sqlmodel
sqlmodel copied to clipboard
How to get row count of session.exec result
First Check
- [X] I added a very descriptive title to this issue.
- [X] I used the GitHub search to find a similar issue and didn't find it.
- [X] I searched the SQLModel documentation, with the integrated search.
- [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
- [X] I already read and followed all the tutorial in the docs and didn't find an answer.
- [X] I already checked if it is not related to SQLModel but to Pydantic.
- [X] I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- [X] 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
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()
hi @mgurg
Thanks for your interest.
I have multiple filters as you have one.
Is using just single column in func.count
enough?
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.
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).
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>)?
Just an update that @mgurg's suggestion of using
func.count
seems to be broken for version0.0.14
ofsqlmodel
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()
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()
.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())
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
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()