CODEWITHSUNDEEP
pythonsqlalchemypyodbcazure-synapsemsodbcsql17
Efstathios Chatzikyriakidis
Efstathios Chatzikyriakidis

Reputation: 1471

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

I have installed:

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.

Upvotes: 9

Views: 8687

Answers (3)

Jakhon
Jakhon

Reputation: 61

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

Upvotes: 1

TheHappyEngineer
TheHappyEngineer

Reputation: 55

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.

Upvotes: 4

Gord Thompson
Gord Thompson

Reputation: 123829

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

Upvotes: 10

Related Questions