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.

Yorgos

Hi! I have fix it. It was for version 17. – Yorgos

Try creating your session with autocommit enabled: Session = sessionmaker(bind=engine, autocommit=True) – Balkanize

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. – Yorgos

Look here 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. – Balkanize

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? – Yorgos

@EfstathiosChatzikyriakidise if the error has solved now, you can post as the answer. – Stenger

Answer 1

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"
)

Balkanize

Answer 2

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.

Cutter

Answer 3

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")

Eriha

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? – Sideward

This worked for me, too - just a different syntax. Super helpful! – Sniperscope