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

Question

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?

Answer 1

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()