CODEWITHSUNDEEP
sqlalchemy
Ferdinand van Wyk
Ferdinand van Wyk

Reputation: 1021

Is there a difference between 'fetchone()' and 'LIMIT 1' using SQLAlchemy

When using SQLAlchemy to retrieve a row from a database, you might use

query = "SELECT some_col FROM some_table"

row = session.execute(query).fetchone()

However, you could also do:

query = "SELECT some_col FROM some_table LIMIT 1"

row = session.execute(query).fetchall()

Is there any reason to prefer one over the other, e.g. better performance?

Upvotes: 7

Views: 2114

Answers (1)

BlackJack
BlackJack

Reputation: 4679

The first variant works with all DBMS, the second only with those supporting the LIMIT keyword which is not standard. So if you want to tell the database you want to limit the result to just one row, you better use SQLAlchemy core instead of an SQL statement as string. Something like:

row = session.execute(select([some_table.c.some_col]).limit(1)).fetchone()

If you really query just one row with just one column you might consider scalar() instead of fetchone():

value = session.execute(select([some_table.c.some_col]).limit(1)).scalar()

Upvotes: 2

Related Questions