I have:
res = db.engine.execute('select count(id) from sometable')
The returned object is sqlalchemy.engine.result.ResultProxy.
How do I get count value from res?
Res is not accessed by index but I have figured this out as:
count=None
for i in res:
count = res[0]
break
There must be an easier way right? What is it? I didn't discover it yet.
Note: The db is a postgres db.
While the other answers work, SQLAlchemy provides a shortcut for scalar queries as ResultProxy.scalar():
count = db.engine.execute('select count(id) from sometable').scalar()
scalar() fetches the first column of the first row and closes the result set, or returns None if no row is present. There's also Query.scalar(), if using the Query API.
Nonewhat you are asking for called unpacking, ResultProxy is an iterable, so we can do
# there will be single record
record, = db.engine.execute('select count(id) from sometable')
# this record consist of single value
count, = record
The ResultProxy in SQLAlchemy (as documented here http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execute#sqlalchemy.engine.ResultProxy) is an iterable of the columns returned from the database. For a count() query, simply access the first element to get the column, and then another index to get the first element (and only) element of that column.
result = db.engine.execute('select count(id) from sometable')
count = result[0][0]
If you happened to be using the ORM of SQLAlchemy, I would suggest using the Query.count() method on the appropriate model as shown here: http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=count#sqlalchemy.orm.query.Query.count
TypeError: 'ResultProxy' object is not subscriptable with SQLAlchmy 1.3.20Here is how you can get count using sqlAlchemy core in fastapi:
from sqlalchemy.future import select
from sqlalchemy.sql import func
...
query = select(func.count(Clients.id))
results = await db.execute(query)
data = results.scalars().first() # in my case return 2
Clients is a model class represents clients tabledb is session : Session = Depends(get_async_session)
.one() instead of .first() to ensure the query returned exactly one row. Note also that the “first” row of a result set is non-deterministic.If you have to do a count on a subquery, it is done in the following way:
from sqlalchemy import select, func
# Original query
query = select(user_table).where(user_table.c.name == "spongebob")
# Count query
count_query = select([func.count()]).select_from(query.alias("x"))
# Getting count
db.engine.execute(count_query).scalar()
