Python/SQLAlchemy, how to access the results of queries [duplicate]

Question

I am working on writing a simple test to validate that the number of distinct values in an "id" column matches the number of rows for each table. I am expecting to be able to access particular values of an object, but when I run code and try to print the value of my variable, I can see that my object is a sqlalchemy.engine.result.ResultProxy object at..., as opposed to being something human readable. I have been on the SQLAlchemy website for over an hour, and have googled several permutations of my question, but have not found what I am looking for.

My code, with terminal output, is below:

from sqlalchemy import create_engine 
engine = create_engine('postgresql://kyle.pekosh@localhost:5432/testload')
connection = engine.connect()
id_count = connection.execute('SELECT COUNT(DISTINCT(id)) FROM csv.agencies')
id_count
<sqlalchemy.engine.result.ResultProxy object at 0x10357d290>

Answer 1

This is SQLAlchemy's expected behavior. You need to interact with the ResultProxy. As per SQLAlchemy's documentation:

The returned result is an instance of ResultProxy, which references a DBAPI cursor and provides a largely compatible interface with that of the DBAPI cursor. The DBAPI cursor will be closed by the ResultProxy when all of its result rows (if any) are exhausted. A ResultProxy that returns no rows, such as that of an UPDATE statement (without any returned rows), releases cursor resources immediately upon construction.

The ResultProxy API allows you to fetch the data:

results = connection.execute('SELECT COUNT(DISTINCT(id)) FROM csv.agencies')
id_count = results.first()[0]