Add sessionmaker

[hitman-gdg]

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

Session = sessionmaker(engine)

Description

Add an sqlalchemy compatible sessionmaker that generates SqlModel sessions

Wanted Solution

I would like to have a working sessionmaker

Wanted Code

from sqlmodel import sessionmaker

Alternatives

No response

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.6

Additional Context

No response

[Zaffer]

Please add this because now it give error that .exec() does not exist on session if you using sqlalchemy import.

[olzhasar]

I faced the same issue and it looks like SQLAlchemy's sessionmaker works just fine with sqlmodel. The only caveat is that you have to provide sqlmodel.Session class to it:

from sqlmodel import Session
from sqlalchemy.orm import sessionmaker

TestSession = sessionmaker(class_=Session)

[tiangolo]

Why do you want a sessionmaker instead of using the new/future and more modern approach of using a with statement with a Session?

Just for completeness, AFAIK, the sessionmaker exists mainly because there was no with statement at the time.

[gazpachoking]

Why do you want a sessionmaker instead of using the new/future and more modern approach of using a with statement with a Session?

My understanding is that the point of the sessionmaker is so that you don't have to pass the configuration (e.g. the engine) to the Session every time. https://docs.sqlalchemy.org/en/14/orm/session_basics.html#using-a-sessionmaker

[tobiasfeil]

You can achieve the desired functionality like this:

from sqlmodel import Session as SQLModelSession
from contextlib import contextmanager

engine = ...

@contextmanager
def Session():
    session = SQLModelSession(engine)
    try:
        yield session
    finally:
        session.close()

Then, you can use Session as a context manager without passing the engine every time:

with Session() as session:
    ...

[seanhuang514]

I faced a similar issue when I tried to call session.exec,then I got an error

AttributeError: 'Session' object has no attribute 'exec'

I found that if you use Sessionmaker like the below, you will get an instance of sqlalchemy.orm.session.Session instead of sqlmodel.orm.session.Session, and the instance of the SQLAlchemy session doesn't have the method exec, only the SQLModel session has.

from sqlalchemy.orm import sessionmaker
SessionLocal = sessionmaker(autoflush=False , bind=engine)

to fix the issue, just like @olzhasar mentioned, you have to pass Session that is imported from sqlmodel and pass to sessionmaker

from sqlmodel import Session as SQLModelSession
SessionLocal = sessionmaker(class_ = SQLModelSession, autoflush=False , bind=engine)

def get_session():
    with SessionLocal() as session:
        yield session

Session = Annotated[SQLModelSession, Depends(get_session)] 

# endpoint
@router.get("/db-test")
async def db_test(session: Session):
    statement = select(User)
    results = session.exec(statement)
    user = results.first()
    return user