How to insert data with SQLAlchemy-CodePudding

I have trouble inserting data into a database with SQLAlchemy. I use scoped_session with a context manager (class implementation):

class Session:
    def __init__(self):
        pass

    def __enter__(self):
        engine = create_engine("sqlite:///test.db", echo = True)
        self.connection = engine.connect()
        self.session = scoped_session(\
                sessionmaker(\
                autocommit=True,\
                autoflush=False,\
                bind=engine))
        return self.session

    def __exit__(self, type, value, traceback):
        self.session.close()
        self.connection.close()

My inserting function:

def insert_value(a, b, c)
    with db_session() as db:
        db.add(Value(a = a, b = b, c = c))

The value is not properly added to the database even after the commit but for some reason, it works with a query().filter().update().

The db.add() doesn't produce any log and the documentation of SQLAlchemy is no help.

EDIT: The problem is with db.add() as select, update and delete operations are done through queries (e.g. db.query().all(), db.query().values() or db.query().delete()) and work fine.

EDIT 2: Some precision about how I instance the class and engine

Base = declarative_base()

class Value(Base):
    __tablename__ = "values"

    a = ...
    b = ...
    c = ...

Base.metadata.create_all(create_engine(...))

CodePudding user response：

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html

Based on the above documentation, I guess autocommit has been deprecated. They have a session.begin() method now for auto commiting.

So you can use it this way:

with db_session.begin():
      foo

CodePudding user response：

I fixed the issue.

I think causes of the issue were:

the use of autocommit
I initiated an engine twice, once for the Session and once for the base classes.

engine = create_engine(...) // Initiated once and for all

Base = declarative_base()

class Value(Base):
    __tablename__ = "values"

    a = ...
    b = ...
    c = ...

Base.metadata.create_all(engine)

class DbSession:
    def __init__(self):
        pass

    def __enter__(self):
        self.connection = engine.connect() // Reuse the engine instead of creating a new one
        self.session = scoped_session(\
                sessionmaker(\
                autocommit=False,\
                autoflush=False,\
                bind=engine))
        return self.session

    def __exit__(self, type, value, traceback):
        self.session.commit()
        self.session.close()
        self.connection.close()


def insert_value(a, b, c):
    with DbSession() as db:
        db.add(Value(a = a, b = b, c = c))