logo标签列表

使用Python连接到Microsoft SQL服务器

pythonsqlwindows
133

我正在尝试通过Python连接到Microsoft SQL Server上的一些SQL数据库以运行一些查询。经过在线和论坛的研究，最有希望的库似乎是pyodbc。因此，我编写了以下代码：

import pyodbc
conn = pyodbc.connect(init_string="driver={SQLOLEDB}; server=+ServerName+; 
database=+MSQLDatabase+; trusted_connection=true")
cursor = conn.cursor()

并获得以下错误

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)')

我查看了以下的帖子，并尝试将我的驱动程序更改为{sql server}，在SAS中以前已经使用过ODBC链接，这部分是我的上面的代码所基于的，因此我认为我不需要安装其他任何内容。

pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

Pyodbc - "Data source name not found, and no default driver specified"

谢谢

3
这里有一些供新手参考的图片。enter image description here - Andrew
对于任何想确定应该使用哪个Python包与SQL Server配合使用的人，如果看到这些答案中的各种包，请注意Microsoft表示：“有几个Python SQL驱动程序可用。但是，Microsoft将其测试工作和信心放在pyodbc驱动程序上。” - jeremysprofile
13个回答
181

这是我如何做到的...

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,))

相关资源：

不要忘记例如 conda install -c anaconda pyodbc / pip install pyodbc。 - ntg
2
在不久的将来，按照这些说明进行操作后，应该现在为pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server.lan,1433;DATABASE=database;UID=user;PWD=pass');（或其非Linux版本）。 https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15 - Orwellophile
83

在之前所说的内容上增加一点。您可能需要返回一个数据框。可以通过以下方式完成：

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)
我有点困惑。如果我在 Linux 服务器上运行 Python 代码（比如 Red Hat 7），我只需要编写“驱动程序= SQL 驱动程序版本名称”而不是服务器上驱动程序的路径？过去我总是在连接 MSSQL 数据库时遇到问题。 - ScipioAfricanus
@ScipioAfricanus 我认为驱动程序是存储在pypyodbc中的东西。 - Keith
44

在客户端和服务器之间的数据源连接中，一般有两种类型：ODBC 使用 DRIVER，OLEDB 使用 PROVIDER。在编程世界中，如何连接数据源是一个普遍的争论话题，可以参见此处。

您正在使用一个 PROVIDER，即 SQLOLEDB，但将其指定为 DRIVER。据我所知，pyodbc 和 pypyodbc 模块都不支持 Window OLEDB 连接。然而，adodbapi 支持使用 Microsoft ADO 作为底层组件的连接方式。

以下是你连接参数的两种方法。此外，我会使用 string format 格式化你的变量，因为你的连接串拼接并没有正确处理字符串内的引号。你会注意到我在花括号上加了一个大括号，因为连接串中需要它，而 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()
谢谢您的解释和代码，我已经让第一个驱动程序运行起来了。尽管我不得不摆脱.format（...）并将变量放在适当的位置。那.format是什么意思？ - Christopher Ell
1
你需要安装adodbapi来使用OLEDB连接。使用字符串格式化方式来传递变量到字符串中，而不是使用+运算符。花括号和数字是占位符，format()会相应地填充它们。你甚至可以使用format()传递列表和元组。你原始的代码没有通过引号来区分字符串和变量，所以+被认为是字符串的一部分。 - Parfait
4
虽然这个回答很好，帮助我解决了问题。但是想要尝试的人需要记住，如果你在同一个连接字符串中设置trusted connection=yes并输入UID/PWD，可能会遇到异常。这是一个二选一的组合，当你使用trusted connection时，即使你明确提到了UID/PWD，也会使用NT/system凭证进行身份验证。 - S4nd33p
21
3
该项目已经被停止：https://github.com/pymssql/pymssql - Pablo EM
5
但是！截至2020年8月，它不再被弃用。你可以看到该存储库再次活跃起来：https://github.com/pymssql/pymssql - deweydb
9

尝试使用pytds，它可以在比pyodbc更复杂的环境中工作，并且设置更加容易。

我在Ubuntu 18.04上使其正常运行。

参考：https://github.com/denisenkom/pytds

文档中的示例代码：

import pytds
with pytds.connect('server', 'database', 'user', 'password') as conn:
    with conn.cursor() as cur:
        cur.execute("select 1")
        cur.fetchall()
3
谢谢。毫不复杂的设置，就能完美运作。 - Shubham Patel
1
我花了很长时间试图让pyodbc和pymssql在M1 MacBook Pro上运行，但仍然失败了。而pytds直接就能正常工作！感谢分享！ - undefined
9

以下Python代码对我有用。为了检查ODBC连接，我首先创建了一个4行的C#控制台应用程序，如下所示。

Python 代码

import pandas as pd
import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=My_DW;")
df = pd.read_sql_query('select TOP 10 * from dbo.Table WHERE Patient_Key > 1000', cnxn)
df.head()

调用存储过程

 dfProcResult = pd.read_sql_query('exec dbo.usp_GetPatientProfile ?', cnxn, params=['MyParam'] )

C#程序检查ODBC连接

    static void Main(string[] args)
    {
        string connectionString = "Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=My_DW;";
        OdbcConnection cn = new OdbcConnection(connectionString);
        cn.Open();
        cn.Close();
    }
3
尝试使用pymssql：pip install pymssql 
import pymssql

try:
    conn = pymssql.connect(server="host_or_ip", user="your_username", password="your_password", database="your_db")
    cursor = conn.cursor()
    cursor.execute ("SELECT @@VERSION")
    row = cursor.fetchone()
    print(f"\n\nSERVER VERSION:\n\n{row[0]}")
    cursor.close()
    conn.close()
except Exception:
    print("\nERROR: Unable to connect to the server.")
    exit(-1)

输出:

SERVER VERSION:

Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64)
        Jul 31 2020 18:47:07
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

可以使用单行代码 sqlcmd 在终端上检查连接。有关语法，请参见语法。

╔═════════╦═════════════════════════════════════════╗
║ Command ║               Description               ║
╠═════════╬═════════════════════════════════════════╣
║   -S    ║ [protocol:]server[instance_name][,port] ║
║   -U    ║ login_id                                ║
║   -p    ║ password                                ║
║   -Q    ║ "cmdline query" (and exit)              ║
╚═════════╩═════════════════════════════════════════╝


sqlcmd -S "host_or_ip"  -U "your_username" -p -Q "SELECT @@VERSION"

输出：

Password:    your_password



--------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) 
        Jul 31 2020 18:47:07 
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)


(1 rows affected)

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total         1  avg   1.00 (1000.00 xacts per sec.)
3

这是适合我的代码：

from sqlalchemy import create_engine
import urllib
import pandas

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))
sqlcmd = """select * from information_schema.tables"""
df = pd.read_sql(sqlcmd, engine)
2
我尝试了以下方法连接SQL Server，并成功了。
使用Windows身份验证连接：
import pyodbc

conn = pyodbc.connect('Driver={SQL Server};Server='+servername+';Trusted_Connection=yes;Database='+databasename+';')
cursor = conn.cursor()
cursor.execute("Select 1 as Data")

为了使用SQL Server身份验证，我使用了以下代码。
import pyodbc

conn = pyodbc.connect('Driver={SQL Server};Server='+servername+  ';UID='+userid+';PWD='+password+';Database='+databasename) 
cursor1 = conn.cursor()
cursor1.execute("SELECT 1 AS DATA")
1
我的版本。希望有帮助。

import pandas.io.sql
import pyodbc
import sys

server = 'example'
db = 'NORTHWND'
db2 = 'example'

#Crear la conexión
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server +
                      ';DATABASE=' + db +
                      ';DATABASE=' + db2 +
                      ';Trusted_Connection=yes')
#Query db
sql = """SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[TitleOfCourtesy]
      ,[BirthDate]
      ,[HireDate]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[HomePhone]
      ,[Extension]
      ,[Photo]
      ,[Notes]
      ,[ReportsTo]
      ,[PhotoPath]
  FROM [NORTHWND].[dbo].[Employees] """
data_frame = pd.read_sql(sql, conn)
data_frame
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接