SqlAlchemy Insert Rollback without logged error-CodePudding

Here I am again trying to study SqlAlchemy.

I am executing an Insert procedure, but at the end, SqlAlchemy is rolling back. I couldn't understand why does that happen.

This is the table I´ve created:

NiceSchools = Table(
   'NiceSchools', metadata, 
   Column('id', Integer, primary_key = True, autoincrement = True), 
   Column('name', String (50), index = True, nullable = False), 
   Column('Safety_Score', Float, nullable = False),
   Column('Location', String(50), nullable = False), 
   Column('Start_date', DateTime, default = datetime.today())
)

This is the insert statement:

stmt = NiceSchools.insert().values(id = 1, name = 'Fábio', Safety_Score = 80, Location = 'Saúde')
with engine.connect() as conn:
    conn.execute(stmt)

And this is the echo=true log:

2022-06-21 16:59:23,798 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-06-21 16:59:23,799 INFO sqlalchemy.engine.Engine INSERT INTO "NiceSchools" (id, name, "Safety_Score", "Location", "Start_date") VALUES (?, ?, ?, ?, ?)
2022-06-21 16:59:23,799 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa ibm_db_sa does not support caching 0.00180s] (1, 'Fábio', 80.0, 'Saúde', datetime.datetime(2022, 6, 21, 16, 58, 52, 39739))
2022-06-21 16:59:24,155 INFO sqlalchemy.engine.Engine ROLLBACK

So, what am I doing wrong?

CodePudding user response：

You are using the context manager wrong, or using the wrong context manager.

Engine.connect() requires a manual commit.

with engine.connect() as con:
    con.execute(...)
    con.commit()

Engine.begin() will commit for you on successful exit.

with engine.begin() as con:
    con.execute(...)

See the tutorial: Working with Transactions and the DBAPI