python sqlalchemy - count rows of result if plain sql query used

Question

I'm using Python 3.9 together with sqlalchemy 1.4.18.

engine = create_engine('sqlite:///somedatabase.sqlite', echo=False)
query = """SELECT ...
    FROM ...
    LEFT JOIN ...
    WHERE ..."""
with engine.connect() as con:
    result = con.execute(query)

How do I count the number of rows the variable result contains in the Python code? I don't want to use COUNT() in SQL. With

print(dir(result))

I found there is a rowcount, however it has always the value -1.

The variable result has the type LegacyCursorResult. Where can find full documentation for this class? The (probably) official docs only mention a close() method: https://docs.sqlalchemy.org/en/14/core/connections.html?highlight=legacycursorresult#sqlalchemy.engine.LegacyCursorResult

Thanks a lot, if somebody can help!

Answer 1

With SQLAlchemy 1.4+, you can use Result.all to obtain the results as a list, and then call len:

rows = result.all()
count = len(rows)