difference between autobegin and Session.begin

In the following code snippet a commit is executed once the with block is exited.

from sqlalchemy import create_engine, Column, String
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()

class Foo(Base):
    __tablename__ = 'Foo'
    id = Column(String, primary_key=True)

engine = create_engine('sqlite:///temp.db', echo=True)

Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    session.add(Foo(id=1))

As per the docs https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.Session.begin

The Session object features autobegin behavior, so that normally it is not necessary to call the Session.begin() method explicitly

Running without begin, like so does not commit even though the docs mention autobegin is not needed.

with Session(engine) as session:
    session.add(Foo(id=1))

The docs for Session.begin does not mention anything about commits, what am I not understanding with here?

Answer

The SQLAlchemy 2.0 tutorial explains the two approaches for managing transactions: “commit as you go” and “begin once”.

Commit as you go

“Commit as you go” takes advantage of SQLAlchemy’s “autobegin” behaviour which ensures that a transaction is implicitly started whenever we perform an operation that might change the database (and hence might need to be rolled back). In this mode

• we don’t need to explicitly begin a transaction, but
• we do need to explicitly commit changes if we want to keep them.

Core:

with engine.connect() as conn:
    conn.execute(insert(Foo.__table__), {"txt": 'core "commit as you go"'})
    conn.commit()
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00060s] ('core "commit as you go"',)
COMMIT
"""

ORM:

with Session(engine) as sess:
    sess.add(Foo(txt='ORM "commit as you go"'))
    sess.commit()
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00035s] ('ORM "commit as you go"',)
COMMIT
"""

Note that if we forget to .commit() then the changes are automatically rolled back:

with engine.connect() as conn:
    conn.execute(insert(Foo.__table__), {"txt": 'core "commit as you go"'})
    # no commit
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00055s] ('core "commit as you go"',)
ROLLBACK
"""

with Session(engine) as sess:
    sess.add(Foo(txt='ORM "commit as you go"'))
    sess.flush()
    # flushed, but not committed
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00040s] ('ORM "commit as you go"',)
ROLLBACK
"""

Begin once

We also have the option to call .begin() ourselves. In this case we don’t have to remember to explicitly commit the changes; they will automatically be committed when the context manager exits (provided that no errors occurred).

The long-winded way to use “begin once” is to invoke .begin() in its own context manager:

Core:

with engine.connect() as conn:
    with conn.begin():
        conn.execute(insert(Foo.__table__), {"txt": 'core "begin once"'})
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00040s] ('core "begin once"',)
COMMIT
"""

ORM:

with Session(engine) as sess:
    with sess.begin():
        sess.add(Foo(txt='ORM "begin once"'))
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00040s] ('ORM "begin once"',)
COMMIT
"""

However, we can use shortcuts to make the code more compact

with engine.connect() as conn, conn.begin():
    conn.execute(insert(Foo.__table__), {"txt": 'core "begin once"'})
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00040s] ('core "begin once"',)
COMMIT
"""

with Session(engine) as sess, sess.begin():
    sess.add(Foo(txt='ORM "begin once"'))
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00036s] ('ORM "begin once"',)
COMMIT
"""

and with Core we can make the code even shorter by using engine.begin() instead of engine.connect()

with engine.begin() as conn:
    conn.execute(insert(Foo.__table__), {"txt": 'core "begin once"'})
"""
BEGIN (implicit)
INSERT INTO foo (txt) VALUES (?)
[generated in 0.00039s] ('core "begin once"',)
COMMIT
"""