python - Azure SQL 数据库的 Sqlalchemy 问题

Question

我尝试通过 SQLAchemy (1.2.5) 连接到 Azure SQL Server，但无论使用什么驱动程序，都会收到以下错误:

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)

这是我用来尝试读取生成错误的表的脚本:

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:<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="402d39303724002d3924226e24213421222133256e37292e242f37336e2e2534" rel="noreferrer noopener nofollow">[email protected]</a>/mydb?charset=utf8&driver=ODBC+Driver+13+for+SQL+Server")
print engine

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

如果我直接通过 pyodbc 运行相同的查询，一切正常:

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

在本例中，输出如下(表为空):

(0, )

谁能帮我解决这个问题吗？

Answer 1

请尝试使用 Driver={SQL Server} 而不是 Driver={ODBC Driver 13 for SQL Server}。在我这边使用 python 2.7 工作得很好。

import urllib
import pyodbc
from sqlalchemy import *

q = """
SELECT count(*) FROM test
"""

params = urllib.quote_plus("Driver={SQL Server};Server=tcp:your_server_name.database.windows.net,1433;Database=your_db;Uid=xxxx;Pwd=xxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

print engine

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

print(res.fetchone())

测试结果(我可以从azure sql中获取结果):