Sqlalchemy issue with Azure SQL Database

[Massi]

I'm trying to connect to an Azure SQL Server via SQLAchemy (1.2.5), but I'm getting the following error regardless of the driver that I use:

Traceback (most recent call last):
  File "C:\Users\user1\Desktop\test.py", line 27, in <module>
    res = engine.connect().execute(q)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2188, in raw_connection
    self.pool.unique_connection, _connection)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "C:\Python27\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 784, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 532, in checkout
    rec = pool._do_get()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 1189, in _do_get
    self._dec_overflow()
  File "C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 1186, in _do_get
    return self._create_connection()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 677, in __connect
    exec_once(self.connection, self)
  File "C:\Python27\lib\site-packages\sqlalchemy\event\attr.py", line 274, in exec_once
    self(*args, **kw)
  File "C:\Python27\lib\site-packages\sqlalchemy\event\attr.py", line 284, in __call__
    fn(*args, **kw)
  File "C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1334, in go
    return once_fn(*arg, **kw)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 183, in first_connect
    dialect.initialize(c)
  File "C:\Python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1931, in initialize
    super(MSDialect, self).initialize(connection)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 283, in initialize
    self.do_rollback(connection.connection)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 457, in do_rollback
    dbapi_connection.rollback()
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No corresponding transaction found. (111214) (SQLEndTran)') (Background on this error at: http://sqlalche.me/e/f405)

This is the script which I use to try and read a table which generates the error:

from sqlalchemy import *
from datetime import datetime
import urllib, sqlalchemy
from urllib import quote_plus as urlquote

q = """
SELECT count(*) FROM [Products]
"""

params = urllib.quote_plus("Driver={ODBC Driver 13 for SQL Server};Server=mydb.database.windows.net,1433;Database=mydb;Uid=myuser;Pwd=mypwd;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;")
engine = sqlalchemy.engine.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
#engine = create_engine("mssql+pyodbc://myuser:my...@mydb.database.windows.net/mydb?charset=utf8&driver=ODBC+Driver+13+for+SQL+Server")
print engine

res = engine.connect().execute(q)

If I run the same query directly via pyodbc, everythin works fine:

import pyodbc
cnxn = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};Server=mydb.database.windows.net,1433;Database=mydb;Uid=myuser;Pwd=mypwd;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;")
cursor = cnxn.cursor()
cursor.execute(q)
row = cursor.fetchone()
if row:
    print row

The output, in this case, is this (the table is empty):

(0, )

Can anyone help me out on this?

[Mike Bayer]

your database does not support transactions. try adding
?autocommit=true to your database URL:

sqlalchemy.engine.create_engine("mssql+pyodbc:///?autocommit=true&odbc_connect=%s"
% params)

let me know if that works

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.