Get the number of rows in table using SQLAlchemy

Question

I am using SQLAlchemy in Python, and I want to know how to get the total number of rows in a column. I have variables defined:

engine = sqlalchemy.create_engine(url, ehco=False)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
Session = session()
metadata = sqlalchemy.MetaData(engine)
Base = declarative_base(metadata=metadata)

# A class representing the shape_congress_districts_2012 table
class Congress(Base):
    __tablename__ = 'shape_congress_districts_2012'
    id = geoalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = geoalchemy.Column(sqlalchemy.Unicode)
    geom = geoalchemy.GeometryColumn(geoalchemy.Polygon(2))
    geom_simple = geoalchemy.GeometryColumn(geoalchemy.Polygon(2))
    area = geoalchemy.Column(sqlalchemy.Float)
    state_id = geoalchemy.Column(sqlalchemy.Integer)
    census_year = geoalchemy.Column(sqlalchemy.Date)

geoalchemy.GeometryDDL(Congress.__table__)

I want to determine the total number of rows in the table without having to wait a whole bunch of time querying the database. Currently, I have a bit of code:

rows = session.query(Congress).all()

Then I can access them from list, but this requires me to load everything into memory at once.

Diego Navarro · Accepted Answer · 2012-05-30T19:35:02.397

160

This should work

rows = session.query(Congress).count()

EDIT: Another way related to my first try

from sqlalchemy import func
rows = session.query(func.count(Congress.id)).scalar()

edited May 30 '12 at 19:35

answered May 30 '12 at 19:20

Diego Navarro

9,316
3
26
33

When I type that in, the output I get is `SELECT count(shape_congress_districts_2012.id) AS count_1 FROM shape_congress_districts_2012` Does that mean that I have messed up somewhere else within my code when constructing the interface to the table? – dbmikus May 30 '12 at 19:24
4

@dbmikus: The query might look nasty but is perfectly fine. It is common practice to use `COUNT(primary_key_column)` instead of just `COUNT(*)` since in a database that is not smart enough to use that key's index automatically the query might be faster that way. – ThiefMaster May 30 '12 at 19:37
24

Which one is better for performance? (postgres if it is matters) – Marosinho Feb 14 '18 at 09:44
the primary key will always be the optimal one – Amias Jan 10 '23 at 17:45

score 1 · Answer 2 · answered Feb 13 '23 at 09:52

Using the ORM:

from sqlalchemy import func

with session() as session:
    length = session.scalar(select(func.count()).select_from(table_class))

Or if you want a type-hinted function:

def get_table_size(session: sessionmaker, table_class: type[SQLTable]) -> int:
    """
    Get the number of rows a table has
    
    :param session: An SQLAlchemy session
    :param table_class: A class that inherits from `sqlalchemy.Base` and represents a table 
    :return: integer
    """
    with session() as session:
        return session.scalar(select(func.count()).select_from(table_class))

score 0 · Answer 3 · answered Jan 25 '23 at 22:21

0

The below worked for me:

conn = engine.connect()    

query = """SELECT count(*) FROM [dbo].[YourTable_Name]"""
exe = conn.execute(query) 

row_count = exe.scalar()

answered Jan 25 '23 at 22:21

Grant Shannon

4,709
1
46
36

Get the number of rows in table using SQLAlchemy

3 Answers3

Linked