Python 使用 sqlalchemy 和 pyodbc 连接到 SQL Server 2012
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15750711/
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
Connecting to SQL Server 2012 using sqlalchemy and pyodbc
提问by Brad Campbell
I'm trying to connect to a SQL Server 2012 database using SQLAlchemy (with pyodbc) on Python 3.3 (Windows 7-64-bit). I am able to connect using straight pyodbc but have been unsuccessful at connecting using SQLAlchemy. I have dsn file setup for the database access.
我正在尝试在 Python 3.3（Windows 7-64 位）上使用 SQLAlchemy（带有 pyodbc）连接到 SQL Server 2012 数据库。我可以使用直接 pyodbc 进行连接，但使用 SQLAlchemy 连接失败。我有用于数据库访问的 dsn 文件设置。
I successfully connect using straight pyodbc like this:
我像这样使用直接 pyodbc 成功连接：
con = pyodbc.connect('FILEDSN=c:\users\me\mydbserver.dsn')
For sqlalchemy I have tried:
对于 sqlalchemy 我试过：
import sqlalchemy as sa
engine = sa.create_engine('mssql+pyodbc://c/users/me/mydbserver.dsn/mydbname')
The create_enginemethod doesn't actually set up the connection and succeeds, but
iIf I try something that causes sqlalchemy to actually setup the connection (like engine.table_names()), it takes a while but then returns this error:
该create_engine方法实际上并没有建立连接并成功，但是如果我尝试一些导致 sqlalchemy 实际建立连接的方法（如engine.table_names()），它需要一段时间，然后返回此错误：
DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)') None None
DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)') None None
I'm not sure where thing are going wrong are how to see what connection string is actually being passed to pyodbc by sqlalchemy. I have successfully using the same sqlalchemy classes with SQLite and MySQL.
我不确定哪里出错了，如何查看 sqlalchemy 实际传递给 pyodbc 的连接字符串。我已经成功地在 SQLite 和 MySQL 中使用了相同的 sqlalchemy 类。
Thanks in advance!
提前致谢！
采纳答案by Bryan
The file-based DSN string is being interpreted by SQLAlchemy as server name = c, database name = users.
SQLAlchemy 将基于文件的 DSN 字符串解释为 server name = c, database name = users。
I prefer connecting without using DSNs, it's one less configuration task to deal with during code migrations.
我更喜欢在不使用 DSN 的情况下进行连接，这是在代码迁移期间处理的一项较少的配置任务。
This syntax works using Windows Authentication:
此语法适用于 Windows 身份验证：
engine = sa.create_engine('mssql+pyodbc://server/database')
Or with SQL Authentication:
或者使用 SQL 身份验证：
engine = sa.create_engine('mssql+pyodbc://user:password@server/database')
SQLAlchemy has a thorough explanation of the different connection string options here.
SQLAlchemy在此处对不同的连接字符串选项进行了详尽的解释。
回答by Andrew
I have an update info about the connection to MSSQL Server without using DSNs and using Windows Authentication. In my example I have next options: My local server name is "(localdb)\ProjectsV12". Local server name I see from database properties (I am using Windows 10 / Visual Studio 2015). My db name is "MainTest1"
我有关于不使用 DSN 和使用 Windows 身份验证连接到 MSSQL Server 的更新信息。在我的示例中，我有下一个选项：我的本地服务器名称是“(localdb)\ProjectsV12”。我从数据库属性中看到的本地服务器名称（我使用的是 Windows 10 / Visual Studio 2015）。我的数据库名称是“MainTest1”
engine = create_engine('mssql+pyodbc://(localdb)\ProjectsV12/MainTest1?driver=SQL+Server+Native+Client+11.0', echo=True)
It is needed to specify driver in connection. You may find your client version in:
需要在连接中指定驱动程序。您可以在以下位置找到您的客户端版本：
control panel>Systems and Security>Administrative Tools.>ODBC Data Sources>System DSN tab>Add
控制面板>系统和安全>管理工具。>ODBC数据源>系统DSN选项卡>添加
Look on SQL Native client version from the list.
从列表中查看 SQL Native 客户端版本。
回答by pooja karande
In Python 3 you can use function quote_plusfrom module urllib.parseto create parameters for connection:
在 Python 3 中，您可以使用quote_plus模块中的函数urllib.parse来创建连接参数：
import urllib
params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
"SERVER=dagger;"
"DATABASE=test;"
"UID=user;"
"PWD=password")
engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))
In order to use Windows Authentication, you want to use Trusted_Connectionas parameter:
为了使用 Windows 身份验证，您需要使用Trusted_Connection作为参数：
params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
"SERVER=dagger;"
"DATABASE=test;"
"Trusted_Connection=yes")
In Python 2 you should use function quote_plusfrom library urllibinstead:
在 Python 2 中，您应该使用quote_plus库中的函数urllib：
params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};"
"SERVER=dagger;"
"DATABASE=test;"
"UID=user;"
"PWD=password")
回答by ssword
Just want to add some latest information here: If you are connecting using DSN connections:
只想在这里添加一些最新信息：如果您使用 DSN 连接进行连接：
engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@SOME_DSN")
If you are connecting using Hostname connections:
如果您使用主机名连接进行连接：
engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@HOST_IP:PORT/DATABASENAME?driver=SQL+Server+Native+Client+11.0")
For more details, please refer to the "Official Document"
详情请参阅“官方文档”
回答by Hugo Pitta
I did different and worked like a charm.
我做了不同的工作，就像一个魅力。
First you import the library:
首先导入库：
import pandas as pd
from sqlalchemy import create_engine
import pyodbc
Create a function to create the engine
创建一个函数来创建引擎
def mssql_engine(user = os.getenv('user'), password = os.getenv('password')
,host = os.getenv('SERVER_ADDRESS'),db = os.getenv('DATABASE')):
engine = create_engine(f'mssql+pyodbc://{user}:{password}@{host}/{db}driver=SQL+Server')
return engine
Create a variable with your query
使用您的查询创建一个变量
query = 'SELECT * FROM [Orders]'
Execute the Pandas command to create a Dataframe from a MSSQL Table
执行 Pandas 命令从 MSSQL 表创建数据框
df = pd.read_sql(query, mssql_engine())
