🐞 After calling get_lastrowid through an insert, we see commit in the database.

[rainer-wiener-by]

Summary

We have upgraded to sqlalchemy 1.4.44 and are using sqlalchemy-exasol 4.0.0/4.0.1. And facing a problem with insert if no rowid is given. When this is the case, when we do the insert, it commits immediately after we try the insert. We tried other versions and found that the problem did not exist before 2.3.0. So we checked the difference between 2.2.0 and 2.3.0:

Starting from 2.3.0 code looks like:

        with self.create_cursor() as cursor:
            cursor.execute(sql_stmnt, args)
            result = cursor.fetchone()
            return int(result[0]) - 1

Before 2.3.0 code looks like:

            cursor = self.create_cursor()
            if schema:
                cursor.execute(sql_stmnt, (table, id_col, schema))
            else:
                cursor.execute(sql_stmnt, (table, id_col))
            result = cursor.fetchone()
            cursor.close()
            return int(result[0]) - 1

Reproducing the Issue

After version 2.2.0, any insert that calls get_lastrowid will result in a commit.

set user and password

import getpass
password = getpass.getpass()
user='sys'

install packages

changing to sqlalchemy-exasol==2.2.0 it will work

!pip install sqlalchemy-exasol==4.0.0 sqlalchemy==1.4.44 pyodbc

create database

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
engine_string=f"exa+pyodbc://{user}:{password}@host.docker.internal:8563/?DEFAULTPARAMSIZE=200&INTTYPESINRESULTSIFPOSSIBLE=y&CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC"
engine = create_engine(engine_string)

conn = engine.connect()
trans = conn.begin()
session = Session(bind=conn)
conn.execute("CREATE SCHEMA test_schema")
conn.execute("create table test_table (id DECIMAL(18) identity primary key, name VARCHAR(2000) UTF8)")
conn.execute("commit")

session.close()
conn.close()

insert data into database

from sqlalchemy.orm import Session
from sqlalchemy import insert, create_engine, Table, Column, Integer, String
from sqlalchemy import MetaData

metadata_obj = MetaData()
engine_string=f"exa+pyodbc://{user}:{password}@host.docker.internal:8563/?DEFAULTPARAMSIZE=200&INTTYPESINRESULTSIFPOSSIBLE=y&CONNECTIONLCALL=en_US.UTF-8&autocommit=false&driver=EXAODBC"
engine = create_engine(engine_string)

conn = engine.connect()
trans = conn.begin()

mytable = Table(
    "test_table", metadata_obj,
    Column('id', Integer, primary_key=True),
    Column('name', String(2000)),
    schema='test_schema',
)

result1 = conn.execute("select * from test_schema.TEST_TABLE;").fetchall()
username = "username_with_commit"
insert_statement = insert(mytable).values(name=username)
conn.execute(insert_statement)

# if id is given we are getting no commit, as get_lastrowid is not called
username = "username_without_commit"
id = 555
insert_statement2 = insert(mytable).values(id=id, name=username)
conn.execute(insert_statement2)

trans.rollback()
conn.close()

conn = engine.connect()
result2 = conn.execute("select * from test_schema.TEST_TABLE;").fetchall()
conn.close()


print(f"result1: {result1}")
print(f"result2: {result2}")

Expected Behaviour

Same behaviour as before version 2.3.0, no commit added after using the
get_lastrowid function.

Actual Behaviour

Getting rowid by using get_lastrowid on an insert statement will result in a
commit after getting lastrowid.

Root Cause

We get down in sqlalchemy-exasol to version 2.2.0 is working and version 2.3.0
does not work.

Code issue looks like to be here:

sqlalchemy-exasol/sqlalchemy_exasol/base.py

Line 753 in 09ac067

with self.create_cursor() as cursor:

Starting from 2.3.0 code looks like:

        with self.create_cursor() as cursor:
            cursor.execute(sql_stmnt, args)
            result = cursor.fetchone()
            return int(result[0]) - 1

Before 2.3.0 code looks like:

            cursor = self.create_cursor()
            if schema:
                cursor.execute(sql_stmnt, (table, id_col, schema))
            else:
                cursor.execute(sql_stmnt, (table, id_col))
            result = cursor.fetchone()
            cursor.close()
            return int(result[0]) - 1

It would also be possible to use the given connection:

        result = self.connection.execute(sql_stmnt, args).fetchone()
        return int(result[0]) - 1

[Nicoretti]

Hi @rainer-wiener-by,

thanks for reporting the issue. Additionally huge thanks for the thorough upfront analysis, appreciate it!
I'll wrap my current work and get to it as soon as possible (probably tomorrow).

best
Nico

[Nicoretti]

Amendment

I am pretty sure you already have identified the exact root cause. Looks like the context manager of the cursor always adds an explicit commit.

[Nicoretti]

Update

Regression test and fix have been implemented see PR.
Review and cleanup still needs to be done, merge and new release can be expected at the beginning of next week.

[Nicoretti]

@rainer-wiener-by fix was merged into master, once PR-337 is merged I will publish a new release of sqlaclhemy-exasol to GitHub and PyPi.

[Nicoretti]

@rainer-wiener-by new version 4.4.0 is published on pypi