AUTOCOMMIT not available, but required for not-journaled files

[ajcastany]

Hi, I need to do an insert/update operation on a file that doesn't have journaling enabled. So it gives the following error, you can't use transactions without journaling:

sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][AS] SQL7008N REXX variable "VARSS " contains inconsistent data. SQLSTATE=55019 SQLCODE=-7008

on the docs here: https://docs.sqlalchemy.org/en/20/tutorial/dbapi_transactions.html it mentions that the AUTOCOMMIT can be set as the isolation_level on the engine. However, it gives this error:

sqlalchemy.exc.ArgumentError: Invalid value 'AUTOCOMMIT' for isolation_level. Valid isolation levels for ibm_db_sa are RR, REPEATABLE READ, READ STABILITY, CURSOR STABILITY, RS, UNCOMMITTED READ, UR, CS

Is there any way to enable AUTOCOMMIT so i can insert/upsert operations with SqlAlchemy ORM on non-journaled files?

[bchoudhary6415]

Hello @ajcastany
I have created physical file and updated the content of the file also, but not able to reproduce the issue,
If possible can you give me some sample repo, than only I will get to know how you are enabling AUTOCOMMIT to insert/upsert operations with SqlAlchemy ORM on non-journaled files?

[ajcastany]

sure,

smt = f"db2+ibm_db://{user}:{password}@{hostname}:{port}/{database}"
engine = create_engine(smt, echo=True)

sm = sessionmaker(engine), autocommit=True, autoflush=True)

with sm.begin() as session:
    record = Users(**user_dict)
    session.add(record)

[bchoudhary6415]

`# Create the SQLAlchemy engine
engine = create_engine(db_url, echo=True)


# Create a base class for declarative class definitions
Base = declarative_base()


# Define your table schema
class File(Base):
    __tablename__ = 'files'

    id = Column(Integer, primary_key=True)
    filename = Column(String(255))
    content = Column(String(255))


# Create the table if it doesn't exist
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Example data
file_data = {
    'filename': 'example.txt',
    'content': 'This is some example content.'
}

# Insert operation
new_file = File(**file_data)
session.add(new_file)
session.commit()

# Update operation
file_to_update = session.query(File).filter_by(filename='example.txt').first()
file_to_update.content = 'Updated content.'
session.commit()

# Refresh the object within the session
session.refresh(file_to_update)

# Close the session
session.close()

# Example of writing content to a file
filename = file_to_update.filename
content = file_to_update.content

with open(filename, 'w') as file:
    file.write(content)

print("File '{}' has been created/updated with content.".format(filename))

# Drop the 'files' table
Base.metadata.drop_all(engine)
print("Table 'files' has been dropped.")`
````# Create the SQLAlchemy engine
engine = create_engine(db_url, echo=True)


# Create a base class for declarative class definitions
Base = declarative_base()


# Define your table schema
class File(Base):
    __tablename__ = 'files'

    id = Column(Integer, primary_key=True)
    filename = Column(String(255))
    content = Column(String(255))


# Create the table if it doesn't exist
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Example data
file_data = {
    'filename': 'example.txt',
    'content': 'This is some example content.'
}

# Insert operation
new_file = File(**file_data)
session.add(new_file)
session.commit()

# Update operation
file_to_update = session.query(File).filter_by(filename='example.txt').first()
file_to_update.content = 'Updated content.'
session.commit()

# Refresh the object within the session
session.refresh(file_to_update)

# Close the session
session.close()

# Example of writing content to a file
filename = file_to_update.filename
content = file_to_update.content

with open(filename, 'w') as file:
    file.write(content)

print("File '{}' has been created/updated with content.".format(filename))

# Drop the 'files' table
Base.metadata.drop_all(engine)
print("Table 'files' has been dropped.")`

I tried with above code and while giving autocommit=True, it was not updating the file because
The issue here is that the changes to the SQLAlchemy ORM objects (File) are not being persisted to the database because you're using autocommit=True, which auto-commits each individual operation, but you're not actually committing the session after making the updates.
When autocommit=True, SQLAlchemy automatically commits each operation (insert, update, delete) individually, but since you're not explicitly committing the session after the update operation, the changes are not reflected in the database.
To fix this, you need to either remove autocommit=True and explicitly commit the session after making changes, or use SQLAlchemy Core to interact directly with the database
See the below example

`# Create the SQLAlchemy engine
engine = create_engine(db_url, echo=True)

# Create the SQLAlchemy engine with AUTOCOMMIT behavior
engine = create_engine(db_url)
autocommit_engine = engine.execution_options(isolation_level="AUTOCOMMIT")

# Create a metadata object
metadata = MetaData()

# Define the 'files' table schema
files = Table(
    'files',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('filename', String(255)),
    Column('content', String(255))
)

# Create the table if it doesn't exist
metadata.create_all(engine)

# Example data
file_data = {
    'filename': 'example1.txt',
    'content': 'This is some example content.'
}

# Insert operation to create a file
insert_statement = files.insert().values(file_data)
engine.execute(insert_statement)

# Update operation to modify file content
update_statement = files.update().where(files.c.filename == 'example1.txt').values(content='Updated content.')
engine.execute(update_statement)

# Read content from the updated file
select_statement = files.select().where(files.c.filename == 'example1.txt')
result = engine.execute(select_statement)
updated_file_data = result.fetchone()

# Example of writing content to a file
filename = updated_file_data['filename']
content = updated_file_data['content']

with open(filename, 'w') as file:
    file.write(content)

print("File '{}' has been updated with content.".format(filename))

# Drop the 'files' table
metadata.drop_all(engine)
print("Table 'files' has been dropped.")`

Now both the above examples are working.

Thanks

[ajcastany]

the file (db2 term for table) you create has journaling enabled. That code works when it has journaling enabled.

You are declaring an "autocommit_engine" but you don't use it, if you try using it you should get: sqlalchemy.exc.ArgumentError: Invalid value 'AUTOCOMMIT' for isolation_level. Valid isolation levels for ibm_db_sa are CS, RS, UNCOMMITTED READ, CURSOR STABILITY, REPEATABLE READ, RR, READ STABILITY, UR

engine object does not have execute() that is a method of either connect or session objects.

did you copy-paste from chatgpt?

[bchoudhary6415]

Hello @ajcastany
Not really,
I have taken example to create a file and update operations from chatgpt, but for the autocommit option, I have gone through the SQLAlchemy documentation - https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.get_isolation_level

Seems Like I missed execute method thing, I will try to reproduce the issue

Thank You