Clever Geek Handbook

Getting COUNT from sqlalchemy

I have:

res = db.engine.execute('select count(id) from sometable')

The object to return sqlalchemy.engine.result.ResultProxy

.

How do I get the count value from res

?

Res is not accessed by index, but I figured it out as:

count=None
for i in res:
    count = res[0]
    break

There must be an easier way? What is it? I haven't discovered it yet. Note: db is postgres

db.

+3


source to share


3 answers


While the other answers work, SQLAlchemy provides a shortcut to scalar queries as ResultProxy.scalar()

:

count = db.engine.execute('select count(id) from sometable').scalar()


scalar()

retrieves the first column of the first row and closes the result set, or returns None if no rows are present. There's also Query.scalar()

if you're using the Query API.

+5


source


what you are asking for called unboxing ResultProxy

is 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
+3


source


ResultProxy

in SQLAlchemy (as described here http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execute#sqlalchemy.engine.ResultProxy ) is the iterable of the columns returned from the database. For a query, count()

just access the first element to get the column, and then another index to get the first element (and only) of that column.

result = db.engine.execute('select count(id) from sometable')
count = result[0][0]

If you were using the ORM SQLAlchemy I would suggest using the method Query.count()

for the relevant model as shown here: http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=count#sqlalchemy.orm.query.Query .count

+2


source






More articles:


All Articles