CODEWITHSUNDEEP
pythonsqlsqlalchemy
Phil
Phil

Reputation: 14651

SQLAlchemy Select with Exist limiting

I have a table with 4 columns (1 PK) from which I need to select 30 rows. Of these rows, two columns (col. A and B) must exists in another table (8 columns, 1 PK, 2 are A and B).

Second table is large, contains millions of records and it's enough for me to know if even a single row exists containing values of col. A and B of 1st table.

I am using the code below:

query = db.Session.query(db.Table_1).\
        filter(
            exists().where(db.Table_2.col_a == db.Table_1.col_a).\
                     where(db.Table_2.col_b == db.Table_2.col_b)
        ).limit(30).all()

This query gets me the results I desire however I'm afraid it might be a bit slow since it does not imply a limit condition to exists() function nor does it do select 1 but a select *.

exists() does not accept a .limit(1)

How can I put a limit to exists to get it not to look for whole table, hence making this query run faster?

Thank you

Upvotes: 2

Views: 4380

Answers (2)

zzzeek
zzzeek

Reputation: 75137

You can do the "select 1" thing using a more explicit form as it mentioned here, that is,

exists([1]).where(...)

However, while I've been a longtime diehard "select 1" kind of guy, I've since learned that the usage of "1" vs. "*" for performance is now a myth (more / more).

exists() is also a wrapper around select(), so you can get a limit() by constructing the select() first:

s = select([1]).where(
         table1.c.col_a == table2.c.colb
       ).where(
         table1.c.colb == table2.c.colb
       ).limit(30)
s = exists(s)

Upvotes: 8

user5099519
user5099519

Reputation: 

query=select([db.Table_1])
query=query.where(
     and_(
           db.Table_2.col_a == db.Table_1.col_a,
           db.Table_2.col_b == db.Table_2.col_b
          )
     ).limit(30)

result=session.execute(query)

Upvotes: -1

Related Questions