SQLAlchemy error: An attempt to complete a transaction has failed. No corresponding transaction found

Question

I have installed:

Ubuntu (18.04)
Python (3.6.8)
msodbcsql17 (Microsoft ODBC Driver 17 for SQL Server)
SQLAlchemy (1.3.5)
Pandas (0.24.2)

and I want to create just a proof of concept using SQLAlchemy with an Azure SQL Data Warehouse. However, when I try to run a query on Customer model which is mapped to the customers view table using the code:

import urllib

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

db_username = 'username'
db_password = 'password'
db_database = 'dbname'
db_hostname = 'dbhost'
db_driver = 'ODBC Driver 17 for SQL Server'
db_port = '1433'

db_connectionString = f"DRIVER={{{db_driver}}}; SERVER={{{db_hostname}}}; DATABASE={{{db_database}}}; UID={{{db_username}}}; PWD={{{db_password}}}; PORT={{{db_port}}};"

engine_params = urllib.parse.quote_plus(db_connectionString)

engine = create_engine(f"mssql+pyodbc:///?odbc_connect={engine_params}", echo=True)

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customers'

    id = Column('Customer_ID', Integer, primary_key=True)

Session = sessionmaker(bind=engine)
session = Session()

customers_count = session.query(Customer).count()

session.close()

the following exception is thrown:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No corresponding transaction found. (111214) (SQLEndTran)

Please, keep in mind that I can use the SQLAlchemy's engine with pandas and run native SQL queries such:

data_frame = pandas.read_sql("SELECT COUNT(*) FROM customers", engine)

However, my need is to use the high-level query API of SQLAlchemy:

customers_count = session.query(Customer).count()

Any help would be really appreciated.

Try creating your session with autocommit enabled: `Session = sessionmaker(bind=engine, autocommit=True)` — Gord Thompson, Jul 20 '19 at 15:44
Hi again, A) what is the meaning of this setting? In my application I want only to just get / read data (no update, delete, insert operations). More specifically, I want just to query and get some aggregations from db data. So why there should be a transaction commit? I want to read data without opening transactions and commit automatically. It is important not to commit something that changes the db state, accidentally. I will try it in Monday and let you know. B) Do you believe this is going to fix it? C) The create_engine also has a autocommit setting. What is the difference? Be well. — Efstathios Chatzikyriakidis, Jul 20 '19 at 16:48
Look [here](https://docs.sqlalchemy.org/en/13/core/connections.html#understanding-autocommit) for a general description of SQLAlchemy's approach to autocommit. In your case, the default Session setting (`autocommit=False`) causes SQLAlchemy to send `BEGIN (implicit)` immediately before running the `SELECT count(*) ...` statement to count the number of rows in the table. Specifying `autocommit=True` suppresses the `BEGIN (implicit)` statement, which Azure SQL DW apparently ignores. — Gord Thompson, Jul 20 '19 at 17:42
So, with `autocommit=True` the line `session.query(Customer).count()` will not send the `BEGIN (implicit)` statement. I would like to ask something more: It is possible to use `engine.execute(query).execution_options(autocommit=True)` and also `create_engine(connectionString, connect_args={'autocommit': True))`. What are these for? Should I use `autocommit=True` only in sessionmaker? — Efstathios Chatzikyriakidis, Jul 20 '19 at 19:06
@EfstathiosChatzikyriakidise if the error has solved now, you can post as the answer. — Leon Yue, Jul 30 '19 at 08:43

score 9 · Accepted Answer · answered Oct 06 '21 at 21:36

The SQLAlchemy documentation for mssql+pyodbc://… has just been updated to include the following (for SQLA 1.4/2.0):

Azure SQL Data Warehouse does not support transactions, and that can cause problems with SQLAlchemy's "autobegin" (and implicit commit/rollback) behavior. We can avoid these problems by enabling autocommit at both the pyodbc and engine levels:

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger",
    host="dw.azure.example.com",
    database="mydb",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "autocommit": "True",
    },
)
engine = create_engine(connection_url).execution_options(
    isolation_level="AUTOCOMMIT"
)

score 3 · Answer 2 · answered May 19 '22 at 15:40

To add to @Gord Thompson's answer (apologies as I don't have enough reputation to comment). For SQLAlchemy 1.4.32; if you only have admin rights for a schema and not the entire database, setting the transaction isolation level will throw an error when the library checks if the isolation level is valid against the database. To get around this I modified the code slightly.

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger",
    host="dw.azure.example.com",
    database="mydb",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "autocommit": "True",
    },
)
engine = create_engine(connection_url).execution_options()

This will give you a warning but does work.

Jakhon · Answer 3 · 2022-11-01T20:53:57.273

1

After many trials and errors, this worked for me:

engine = create_engine("mssql+pyodbc://username:password@server_name/database_name?"
                            "driver=ODBC+Driver+17+for+SQL+Server"
                            "&authentication=ActiveDirectoryPassword"
                            "&autocommit=True")

edited Nov 01 '22 at 20:53

answered Nov 01 '22 at 20:51

Jakhon

13
4

Please, avoid code only answers. Especially when answering to old questions with already upvoted answers. What does this do to correct the problem? What new idea does it contain that was not present in previous answer? – chrslg Nov 06 '22 at 14:34
This worked for me, too - just a different syntax. Super helpful! – jeffsdata Nov 11 '22 at 18:11

SQLAlchemy error: An attempt to complete a transaction has failed. No corresponding transaction found

3 Answers3