Python SqlAlchemy 相当于使用 FreeTDS 的 pyodbc 连接字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4493614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlAlchemy equivalent of pyodbc connect string using FreeTDS
提问by mwolfe02
The following works:
以下工作：
import pyodbc
pyodbc.connect('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;')
The following fails:
以下失败：
import sqlalchemy
sqlalchemy.create_engine("mssql://myuser:[email protected]:1433/mydb?driver=FreeTDS& odbc_options='TDS_Version=8.0'").connect()
The error message for above is:
上面的错误信息是：
DBAPIError: (Error) ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnectW)') None None
DBAPIError: (Error) ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]无法连接到数据源 (0) (SQLDriverConnectW)') 无 无
Can someone please point me in the right direction? Is there a way I can simply tell sqlalchemy to pass a specific connect string through to pyodbc?
有人可以指出我正确的方向吗？有没有办法可以简单地告诉 sqlalchemy 将特定的连接字符串传递给 pyodbc？
Please Note:I want to keep this DSN-less.
请注意：我想保持此无 DSN。
采纳答案by jmagnusson
The example by @Singletoned would not work for me with SQLAlchemy 0.7.2. From the SQLAlchemy docs for connecting to SQL Server:
@Singletoned 的示例不适用于 SQLAlchemy 0.7.2。从用于连接到 SQL Server的SQLAlchemy 文档：
If you require a connection string that is outside the options presented above, use the odbc_connect keyword to pass in a urlencoded connection string. What gets passed in will be urldecoded and passed directly.
If you require a connection string that is outside the options presented above, use the odbc_connect keyword to pass in a urlencoded connection string. What gets passed in will be urldecoded and passed directly.
So to make it work I used:
所以为了使它工作，我使用了：
import urllib
quoted = urllib.quote_plus('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;')
sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
This should apply to Sybase as well.
这也应该适用于 Sybase。
NOTE: In python 3 the urllib module has been split into parts and renamed. Thus, this line in python 2.7:
注意：在 python 3 中，urllib 模块已被拆分为多个部分并重命名。因此，python 2.7 中的这一行：
quoted = urllib.quote_plus
has to be changed to this line in python3:
在python3中必须改为这一行：
quoted = urllib.parse.quote_plus
回答by mwolfe02
I'm still interested in a way to do this in one line within the sqlalchemy create_enginestatement, but I found the following workaround detailed here:
我仍然对在 sqlalchemycreate_engine语句中的一行中执行此操作的方法感兴趣，但我在此处找到了以下详细解决方法：
import pyodbc, sqlalchemy
def connect():
pyodbc.connect('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;')
sqlalchemy.create_engine('mssql://', creator=connect)
UPDATE: Addresses a concern I raised in my own comment about not being able to pass arguments to the connect string. The following is a general solution if you need to dynamically connect to different databases at runtime. I only pass the database name as a parameter, but additional parameters could easily be used as needed:
更新：解决了我在自己的评论中提出的关于无法将参数传递给连接字符串的问题。如果您需要在运行时动态连接到不同的数据库，以下是一个通用的解决方案。我只将数据库名称作为参数传递，但可以根据需要轻松使用其他参数：
import pyodbc
import os
class Creator:
def __init__(self, db_name='MyDB'):
"""Initialization procedure to receive the database name"""
self.db_name = db_name
def __call__(self):
"""Defines a custom creator to be passed to sqlalchemy.create_engine
http://stackoverflow.com/questions/111234/what-is-a-callable-in-python#111255"""
if os.name == 'posix':
return pyodbc.connect('DRIVER={FreeTDS};'
'Server=my.db.server;'
'Database=%s;'
'UID=myuser;'
'PWD=mypassword;'
'TDS_Version=8.0;'
'Port=1433;' % self.db_name)
elif os.name == 'nt':
# use development environment
return pyodbc.connect('DRIVER={SQL Server};'
'Server=127.0.0.1;'
'Database=%s_Dev;'
'UID=user;'
'PWD=;'
'Trusted_Connection=Yes;'
'Port=1433;' % self.db_name)
def en(db_name):
"""Returns a sql_alchemy engine"""
return sqlalchemy.create_engine('mssql://', creator=Creator(db_name))
回答by skermajo
To pass various parameters to your connect function, it sounds like format string might do what you want:
要将各种参数传递给您的连接函数，听起来格式字符串可能会执行您想要的操作：
def connect(server, dbname, user, pass):
pyodbc.connect('DRIVER={FreeTDS};Server=%s;Database=%s;UID=%s;PWD=%s;TDS_Version=8.0;Port=1433;' % (server, dbname, user, pass))
And you would then call it with something like:
然后你会用类似的东西调用它：
connect('myserver', 'mydatabase', 'myuser', 'mypass')
More info on format strings is here: http://docs.python.org/library/string.html#formatstrings
关于格式字符串的更多信息在这里：http: //docs.python.org/library/string.html#formatstrings
回答by Singletoned
This works:
这有效：
import sqlalchemy
sqlalchemy.create_engine("DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;").connect()
In that format, SQLAlchemy just ignores the connection string and passes it straight on to pyodbc.
在这种格式中，SQLAlchemy 只是忽略连接字符串并将其直接传递给 pyodbc。
Update:
更新：
Sorry, I forgot that the uri has to be url-encoded, therefore, the following works:
对不起，我忘了 uri 必须是 url 编码的，因此，以下工作：
import sqlalchemy
sqlalchemy.create_engine("DRIVER%3D%7BFreeTDS%7D%3BServer%3Dmy.db.server%3BDatabase%3Dmydb%3BUID%3Dmyuser%3BPWD%3Dmypwd%3BTDS_Version%3D8.0%3BPort%3D1433%3B").connect()
回答by grapier
Internally "my.db.server:1433" is passed as part of a connection string like SERVER=my.db.server:1433;.
在内部，“my.db.server:1433”作为连接字符串的一部分传递，如SERVER=my.db.server:1433;.
Unfortunately unixODBC/FreeTDS won't accept a port in the SERVER bit. Instead it wants SERVER=my.db.server;PORT=1433;
不幸的是，unixODBC/FreeTDS 不接受 SERVER 位中的端口。相反它想要SERVER=my.db.server;PORT=1433;
To use the sqlalchemy syntax for a connection string, you must specify the port as a parameter.
要将 sqlalchemy 语法用于连接字符串，您必须将端口指定为参数。
sqlalchemy.create_engine("mssql://myuser:[email protected]:1433/mydb?driver=FreeTDS& odbc_options='TDS_Version=8.0'").connect()
becomes:
变成：
sqlalchemy.create_engine("mssql://myuser:[email protected]/mydb?driver=FreeTDS&port=1433& odbc_options='TDS_Version=8.0'").connect()
