python - Azure SQL 数据库的 Sqlalchemy 问题
问题描述
我正在尝试通过 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:mypwd@mydb.database.windows.net/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, )
谁能帮我解决这个问题?
解决方案
请尝试使用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 获取结果):
推荐阅读
- c# - 我如何确定从现在到选定日期减去 [DateTime.Now-datetimepicker1] 的月份和年份?
- c# - IBackgroundTask 中的 FullTrustProcessLauncher 不起作用
- json - 为什么 JSON.parse(JSON.stringify(json)) != json?
- prestashop - 按钮“添加到购物车”不起作用 prestashop 1.6
- reactjs - 将别名设置为 Apache2 后,Laravel Mix React 组件不渲染
- matrix - 沿对角栅栏导航
- heroku - 为什么 Passport Google 不在 heroku 中
- python-3.x - 来自现有安装的 aws lambda python 包
- ios - 如何调用多个参数
- c# - 为什么我无法通过 C# 重现 Richter 的 CLR 中的易失性错误