pyodbc based dialect does not raise an integrity error when inserting pk twice

[Nicoretti]

Steps To Reproduce:

Add the tests below to test/test_suit.py and execute the integration tests or just the test itself.
(using the pyodbc connector)

from sqlalchemy.testing.suite import ExceptionTest as _ExceptionTest
from sqlalchemy import text


class ExceptionTest(_ExceptionTest):
  
    def test_integrity_error(self):

        with config.db.connect() as conn:

            trans = conn.begin()
            conn.execute(
                self.tables.manual_pk.insert(), {"id": 1, "data": "d1"}
            )

            assert_raises(
                exc.IntegrityError,
                conn.execute,
                self.tables.manual_pk.insert(),
                {"id": 1, "data": "d1"},
            )

            trans.rollback()

Expected Behavior

✔️ Test passes because an sqlalchemy.exec.IntegrityError is risen.

Actual Behavior

💥 Test fails because no exception is risen.

Related Issues

• test ExceptionTest_exasol_pyodbc::()::test_integrity_error fails #42
• turbodbc based dialect does not raise an integrity when inserting pk twice #119

Regression

With the script sqla_regression.py the scenario can be investigated with
unix-odbc, exasol-odbc and sqla - logging enabled.

Dialect: pyodbc, Autocommit: no

python sqla_regression.py --logging --dialect=pyodbc sqla-insert driver/libexaodbc-uo2214lv1.so

Expected:
💥 sqlalchemy raises and IntegrityError

Actual:
✔️ program finishes "successfully", but sqlalchemy log shows "swallowed" error.

ERROR:sqlalchemy.pool.impl.QueuePool:Error closing cursor: ('HY000', 'The driver did not supply an error!')
Traceback (most recent call last):
  File ".../sqlalchemy-exasol/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1368, in _safe_close_cursor
    cursor.close()
pyodbc.Error: ('HY000', 'The driver did not supply an error!')

sqla_regression.py

import argparse
import logging
import os
import sys
from contextlib import contextmanager
from pathlib import Path
from platform import platform
from subprocess import run
from tempfile import TemporaryDirectory
from textwrap import dedent

from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, text

ODBCINST_INI_TEMPLATE = dedent(
    """
    [ODBC]
    Trace = {trace}
    TraceFile = {trace_file} 

    [EXAODBC]
    #Driver location will be appended in build environment:
    DRIVER={driver}

    """
)


@contextmanager
def environment(env_vars):
    _env = os.environ.copy()
    os.environ.update(env_vars)
    yield os.environ
    os.environ.clear()
    os.environ.update(_env)


@contextmanager
def temporary_odbc_config(config):
    with TemporaryDirectory() as tmp_dir:
        tmp_dir = Path(tmp_dir)
        config_dir = tmp_dir / "odbcconfig"
        config_dir.mkdir(exist_ok=True)
        config_file = config_dir / "odbcinst.ini"
        with open(config_file, "w") as f:
            f.write(config)
        yield config_file


@contextmanager
def odbcconfig(driver, log_file=None):
    with temporary_odbc_config(
            ODBCINST_INI_TEMPLATE.format(
                driver=driver,
                trace="yes" if log_file is not None else "no",
                trace_file=log_file,
            )
    ) as cfg:
        env_vars = {"ODBCSYSINI": f"{cfg.parent.resolve()}"}
        with environment(env_vars) as env:
            yield cfg, env


def sqla_sql_insert(engine):
    table = setup_table(engine)
    with engine.connect() as connection:
        connection.execute(text(f"INSERT INTO {table} VALUES (1, 'd1')"))
        connection.execute(text(f"INSERT INTO {table} VALUES (1, 'd1')"))


def sqla_insert(engine):
    table = setup_table(engine)
    with engine.connect() as connection:
        connection.execute(table.insert(), {"id": 1, "data": "d1"})
        connection.execute(table.insert(), {"id": 1, "data": "d1"})


def sqla_single_insert(engine):
    table = setup_table(engine)
    with engine.connect() as connection:
        connection.execute(table.insert(), {"id": 1, "data": "d1"})


def setup_table(engine):
    engine.execute(text("DROP TABLE IF EXISTS TEST.manual_pk"))
    meta_data = MetaData()
    t = Table(
        "manual_pk",
        meta_data,
        Column("id", Integer, primary_key=True, autoincrement=False),
        Column("data", String(50)),
    )
    meta_data.create_all(engine)
    return meta_data.tables["manual_pk"]


def system_info():
    print("System Information")
    print("-" * 50)
    print("PYTHON VERSION")
    run(["python", "--version"])
    print("-" * 50)
    print("PYTHON ODBC VERSION")
    run(["python", "-m", "pip", "show", "pyodbc"])
    print("PLATFORM")
    print(platform())
    print("EXASOL ODBC DRIVER")
    print("libexasolodbc driver to 7.0.11")


def create_parser():
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "scenario",
        choices=["sqla-insert", "sqla-sql-insert", "sqla-single-insert"],
        help="Test scenario to execute",
    )
    parser.add_argument(
        "driver",
        type=Path,
        help="Path to the exasol odbc driver which shall be used.",
    )
    parser.add_argument(
        "--system-info",
        action="store_true",
        default=False,
        help="Print system information.",
    )
    parser.add_argument(
        "--db-address",
        default="localhost",
        help="DNS name or address of the database server to connect to.",
    )
    parser.add_argument(
        "--db-port", type=int, default=8888, help="Port of the database to connect to."
    )
    parser.add_argument(
        "--logging",
        action="store_true",
        default=False,
        help="Whether or not to enable logging",
    )
    parser.add_argument(
        "--dialect",
        choices=["pyodbc", "turbodbc"],
        default="pyodbc",
        help="Dialect which shall be used for the connection",
    )
    parser.add_argument(
        "--autocommit",
        action="store_true",
        default=False,
        help="Whether or not to store the odbc trace",
    )
    return parser


def main(argv=None):
    parser = create_parser()
    args = parser.parse_args(argv)

    if args.system_info:
        system_info()

    if args.logging:
        log_folder = Path(os.getcwd()) / "logs"
        log_folder.mkdir(exist_ok=True)

        odbc_log = log_folder / "odbc.trace"
        exa_log = log_folder / "exa-odbc.trace"
        sqla_log = log_folder / "sqla.trace"

        logging.basicConfig(filename=f"{sqla_log}", level=logging.DEBUG)
        logging.getLogger("sqlalchemy").setLevel(logging.DEBUG)

    def make_connection_url(args):
        return "".join(
            (
                "exa+{dialect}://sys:exasol@{address}:{port}",
                "/TEST?DRIVER=EXAODBC&CONNECTIONCALL=en_US.UTF-8",
                f"&EXALOGFILE={exa_log}" if args.logging else "",
            )
        ).format(dialect=args.dialect, address=args.db_address, port=args.db_port)

    with odbcconfig(args.driver, f"{odbc_log}" if args.logging else None):
        url = make_connection_url(args)
        engine = create_engine(url, connect_args={"autocommit": args.autocommit})
        scenario = {
            "sqla-insert": sqla_insert,
            "sqla-sql-insert": sqla_sql_insert,
            "sqla-single-insert": sqla_single_insert,
        }[args.scenario]
        scenario(engine)

    sys.exit(0)


if __name__ == "__main__":
    main()

[Nicoretti]

Latest update after discussion with exasols odbc team: This issue should be revisited after projected upgraded exasols latest odbc driver 7.1.x.

See tracking issue:

• Upgrade to latest Exasol 7.1.x ODBC Driver #114

[Nicoretti]

Issue Resolution: Phasing Out ODBC Support

Summary

The long-term goal of this project is to phase out support for ODBC. Additionally, as of today, we have not received any reports from users indicating that this issue is a problem.

Decision

Considering the strategic direction towards phasing out ODBC and the lack of user feedback on this issue, we have decided to close this ticket.

References

• 🔧 Add in code deprecation warnings for [pyodbc, turbodbc] #402
• 🔧 Remove turbodbc support #403
• 🔧 Remove pyodbc support #404