SQLAlchemy Select with Exist limiting

Question

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?

I need n rows from Table_1, which 2 columns exist in a record in Table_2

Thank you

charlax · Accepted Answer · 2018-04-10 14:49:48Z

9

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)

edited Apr 10, 2018 at 14:49

charlax

26.2k19 gold badges64 silver badges71 bronze badges

answered Jan 31, 2013 at 0:29

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phil Over a year ago

Hey, this is excellent! Thank you very much @zzzeek. In fact, this is fantastic! ;-))

charlax Over a year ago

One thing to note: to execute s, you need to generate a select for it: conn.execute(s.select()).

Tunaki · Accepted Answer · 2015-12-04 21:43:44Z

-1

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)

edited Dec 4, 2015 at 21:43

Tunaki

138k46 gold badges370 silver badges443 bronze badges

answered Dec 4, 2015 at 21:43

user5099519

Collectives™ on Stack Overflow

SQLAlchemy Select with Exist limiting

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related