How to get row count of session.exec result

[jd-solanki]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond")
    hero_2 = Hero(name="Spider-Boy")
    hero_3 = Hero(name="Rusty-Man")

    with Session(engine) as session:  #
        session.add(hero_1)  #
        session.add(hero_2)
        session.add(hero_3)

        session.commit()

        row_counts = session.exec(select(Hero)).count() # HERE! How can I get the row counts? In my code I had where as well.
        print(f"row_counts: {row_counts}")


def main():
    create_db_and_tables()
    create_heroes()


if __name__ == "__main__":
    main()

Description

• How can I get the count or row counts of the query?

Operating System

Linux

Operating System Details

Ubuntu 21.10

SQLModel Version

0.0.6

Python Version

3.10.2

Additional Context

None

[mgurg]

Try this way (copied from my test project):

from sqlalchemy import func

session.exec(select([func.count(Users.email)]).where(Users.email == res.email)).one()

[jd-solanki]

hi @mgurg

Thanks for your interest.

I have multiple filters as you have one.

Is using just single column in func.count enough?

[mgurg]

You have to figure out this by yourself, I'm not an expert here. I just found in the past solution for a similar problem for myself.

[byrman]

Ideally, you would would not have to specify a column at all and use COUNT(*) behind the scenes. That does not seem possible yet with 1.4 ORM, see here. Correction: it is possible but generates inefficient SQL (a subquery).

[taranlu-houzz]

Just an update that @mgurg's suggestion of using func.count seems to be broken for version 0.0.14 of sqlmodel (although, the error I am seeing comes from sqlalchemy, so it may actually be due to an update with the sqlalchemy dep instead).

ArgumentError: Column expression, FROM clause, or other columns clause element expected, got [<sqlalchemy.sql.functions.count
  at 0x109974e90; count>]. Did you mean to say select(<sqlalchemy.sql.functions.count at 0x109974e90; count>)?

[jplacht]

Just an update that @mgurg's suggestion of using func.count seems to be broken for version 0.0.14 of sqlmodel

Can confirm; following statement is working:

# simple
session.exec(select(func.count(User.user_id))).one()

# with .where() statement
session.exec(
    select(func.count(User.user_id)).where(
        User.last_action >= datetime.today() - timedelta(days=1)
    )
).one()

[dydek]

Hi, @jplacht your approach works, the only issue with it is that types are incorrect, I know we can ignore it, but I was wondering if there is a better way to use func.count :

count = (await session.exec(select(func.count(ItemModel.id)))).first()

which gives me this error :

Mypy: Argument 1 to "count" has incompatible type "UUID"; expected "ColumnElement[Any] | _HasClauseElement[Any] | SQLCoreOperations[Any] | ExpressionElementRole[Any] | Callable[[], ColumnElement[Any]] | LambdaElement | None" [arg-type]

so I modified it a little to get rid of the mypy errors:

count = (await session.exec(select(func.count()).select_from(ItemModel))).first()

[aim-to-miss]

.all() "to get a list of all the rows right away, instead of an iterable."
I took advantage of this, maybe not great but works:
len(results.all())

[raibann]

from sqlmodel import Session,select,func

count_statement = select(func.count()).select_from(User).where(User.deleted_at == None)
total = db.exec(count_statement).one()

it's work fine in sqlmodel

[jackbravo]

To get rid of the mypy (or pylance/pyright) type issues this is working ok:

from sqlmodel import Session, col, func, select
from .database import engine
from .models import Page

with Session(engine) as session:
    count = session.exec(select(func.count(col(Page.id)))).one()

[YuriiMotov]

Runnable code example:

from typing import Optional

from sqlmodel import Field, Session, SQLModel, col, create_engine, func, select

class Page(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, nullable=False)

def main() -> None:
    engine = create_engine("sqlite:///", echo=True)
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        pages = [Page() for _ in range(30)]
        session.add_all(pages)
        session.commit()

    with Session(engine) as session:
        count = session.exec(select(func.count(col(Page.id)))).one()
        assert count == 30

if __name__ == "__main__":
    main()

[RanaUniverse]

from sqlmodel import Session,select,func

count_statement = select(func.count()).select_from(User).where(User.deleted_at == None)
total = db.exec(count_statement).one()

it's work fine in sqlmodel

if suppose this query returns 1 million row, will not it make it need big processing power of cpu, or perhaps it crash my os.

i means to know which way is less cpu usage to get the row count?

[copdips]

this is what GPT says, generally speaking, in modern DB, it's the same performance:

The performance difference between COUNT(*) and COUNT(primary_key_column) depends on the database system and specific circumstances. Let me clarify:
For many modern database systems (PostgreSQL, SQL Server, MySQL), COUNT(*) and COUNT(primary_key_column) will often have similar performance characteristics when there's a clustered index on the primary key. This is because:
When counting non-NULL values in a specific column, the database can sometimes use that column's index
Most database optimizers are smart enough to recognize that COUNT(*) doesn't actually need to retrieve data, just count rows