使用 Python 连接到 Microsoft SQL 服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33725862/
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 Microsoft SQL server using Python
提问by Christopher Ell
I am trying to connect to SQL through python to run some queries on some SQL databases on Microsoft SQL server. From my research online and on this forum the most promising library seems to be pyodbc. So I have made the following code
我正在尝试通过 python 连接到 SQL 以在 Microsoft SQL 服务器上的某些 SQL 数据库上运行一些查询。从我在网上和这个论坛上的研究来看，最有前途的库似乎是 pyodbc。所以我做了以下代码
import pyodbc
conn = pyodbc.connect(init_string="driver={SQLOLEDB}; server=+ServerName+;
database=+MSQLDatabase+; trusted_connection=true")
cursor = conn.cursor()
and get the following error
并得到以下错误
Traceback (most recent call last):
File "C:\Users...\scrap.py", line 3, in <module>
conn = pyodbc.connect(init_string="driver={SQLOLEDB}; server=+ServerName+; database=+MSQLDatabase+; trusted_connection=true")
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
I have looked at the folowing posts and tried changing my driver to {sql server} and have connected using ODBC links before in SAS, which is partially what my above code is based on, so don't think I need to install anything else.
我查看了以下帖子并尝试将我的驱动程序更改为 {sql server} 并且之前在 SAS 中使用 ODBC 链接进行连接，这部分是我上面的代码所基于的，所以不要认为我需要安装任何其他东西。
Pyodbc - "Data source name not found, and no default driver specified"
Pyodbc - “未找到数据源名称，且未指定默认驱动程序”
Thanks
谢谢
采纳答案by ASH
This is how I do it...
我就是这样做的...
import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server_name;"
"Database=db_name;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Table')
for row in cursor:
print('row = %r' % (row,))
Relevant resources:
相关资源：
回答by Parfait
In data source connections between a client and server there are two general types: ODBC which uses a DRIVER and OLEDB which uses a PROVIDER. And in the programming world, it is a regular debateas to which route to go in connecting to data sources.
在客户端和服务器之间的数据源连接中，有两种通用类型：使用 DRIVER 的 ODBC 和使用 PROVIDER 的 OLEDB。在编程世界中，关于连接到数据源的路线是一个经常性的争论。
You are using a provider, SQLOLEDB, but specifying it as a driver. As far as I know, neither the pyodbc nor pypyodbc modules support Window OLEDB connections. However, the adodbapidoes which uses the Microsoft ADO as an underlying component.
您正在使用提供程序，SQLOLEDB但将其指定为驱动程序。据我所知，pyodbc 和 pypyodbc 模块都不支持 Window OLEDB 连接。但是，adodbapi使用 Microsoft ADO 作为底层组件。
Below are both approaches for your connection parameters. Also, I string formatyour variables as your concatenation did not properly break quotes within string. You'll notice I double the curly braces since it is needed in connection string and string.format()also uses it.
以下是连接参数的两种方法。此外，我对您的变量进行字符串格式化，因为您的连接没有正确地断开字符串中的引号。您会注意到我将花括号加倍，因为它在连接字符串中需要并且string.format()也使用它。
# PROVIDER
import adodbapi
conn = adodbapi.connect("PROVIDER=SQLOLEDB;Data Source={0};Database={1}; \
trusted_connection=yes;UID={2};PWD={3};".format(ServerName,MSQLDatabase,username,password))
cursor = conn.cursor()
# DRIVER
import pyodbc
conn = pyodbc.connect("DRIVER={{SQL Server}};SERVER={0}; database={1}; \
trusted_connection=yes;UID={2};PWD={3}".format(ServerName,MSQLDatabase,username,password))
cursor = conn.cursor()
回答by mondieki
An alternative approach would be installingMicrosoft ODBC Driver 13, then replace SQLOLEDBwith ODBC Driver 13 for SQL Server
另一种方法是安装Microsoft ODBC Driver 13，然后替换SQLOLEDB为ODBC Driver 13 for SQL Server
Regards.
问候。
回答by Keith
Minor addition to what has been said before. You likely want to return a dataframe. This would be done as
对之前所说的内容进行了少量补充。您可能想要返回一个数据框。这将做为
import pypyodbc
import pandas as pd
cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=server_name;"
"Database=db_name;"
"uid=User;pwd=password")
df = pd.read_sql_query('select * from table', cnxn)
回答by Franco
I Prefer this way ... it was much easier
我更喜欢这种方式......它更容易
http://www.pymssql.org/en/stable/pymssql_examples.html
http://www.pymssql.org/en/stable/pymssql_examples.html
conn = pymssql.connect("192.168.10.198", "odoo", "secret", "EFACTURA")
cursor = conn.cursor()
cursor.execute('SELECT * FROM usuario')
回答by Alfred Huang
Try using pytds, it works throughout more complexity environment than pyodbcand more easier to setup.
尝试使用 pytds，它可以在更复杂的环境中工作，pyodbc并且更容易设置。
I made it work on Ubuntu 18.04
我让它在 Ubuntu 18.04 上运行
Example code in documentation:
文档中的示例代码：
import pytds
with pytds.connect('server', 'database', 'user', 'password') as conn:
with conn.cursor() as cur:
cur.execute("select 1")
cur.fetchall()
回答by Andrew
Here are some pics for newbies.
这里有一些照片给新手。
回答by LCJ
Following Python code worked for me. To check the ODBC connection, I first created a 4 line C# console application as listed below.
以下 Python 代码对我有用。为了检查 ODBC 连接，我首先创建了一个 4 行 C# 控制台应用程序，如下所示。
Python Code
Python代码
import pandas as pd
import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=RCO_DW;")
df = pd.read_sql_query('select TOP 10 * from dbo.Table WHERE Patient_Key > 1000', cnxn)
df.head()
Calling a Stored Procedure
调用存储过程
dfProcResult = pd.read_sql_query('exec dbo.usp_GetPatientProfile ?', cnxn, params=['MyParam'] )
C# Program to Check ODBC Connection
用于检查 ODBC 连接的 C# 程序
static void Main(string[] args)
{
string connectionString = "Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=RCO_DW;";
OdbcConnection cn = new OdbcConnection(connectionString);
cn.Open();
cn.Close();
}
回答by James
here's the one that works for me:
这是对我有用的一个：
from sqlalchemy import create_engine
import urllib
conn_str = (
r'Driver=ODBC Driver 13 for SQL Server;'
r'Server=DefinitelyNotProd;'
r'Database=PlayPen;'
r'Trusted_Connection=Yes;')
quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
回答by Karl
I found up-to-date resources here: Microsoft | SQL Docs | Python SQL Driver
我在这里找到了最新资源： Microsoft | SQL 文档 | Python SQL 驱动程序
There are these two options explained including all the prerequisites needed and code examples: Python SQL driver - pyodbc(tested & working) Python SQL driver - pymssql
解释了这两个选项，包括所需的所有先决条件和代码示例： Python SQL 驱动程序 - pyodbc（测试和工作） Python SQL 驱动程序 - pymssql
