使用Python连接到Microsoft SQL Server

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.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

pyodbc.Error：（'IM002'，'[IM002] [unixODBC] [Driver Manager]找不到数据源名称，并且没有指定默认驱动程序（0）（SQLDriverConnect）'）

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

Pyodbc - “找不到数据源名称，并且未指定默认驱动程序”

Thanks

谢谢

6 个解决方案

#1

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 sources:

相关来源：

• https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows
• https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows
• http://www.pythonforbeginners.com/code/regular-expression-re-findall
• http://www.pythonforbeginners.com/code/regular-expression-re-findall
• http://blogs.msdn.com/b/cdndevs/archive/2015/03/11/python-and-data-sql-server-as-a-data-source-for-python-applications.aspx
• http://blogs.msdn.com/b/cdndevs/archive/2015/03/11/python-and-data-sql-server-as-a-data-source-for-python-applications.aspx

#2

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 debate as 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 adodbapi does 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 format your 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()

#3

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)

#4

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

#5

An alternative approach would be installing Microsoft ODBC Driver 13, then replace SQLOLEDB with ODBC Driver 13 for SQL Server

另一种方法是安装Microsoft ODBC Driver 13，然后将SQLOLEDB替换为SQL Server的ODBC驱动程序13

Regards.

问候。

#6

Try using pytds, it works throughout more complexity environment than pyodbc and more easier to setup.

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

I made it work on Ubuntu 18.04

我在Ubuntu 18.04上工作了

Ref: https://github.com/denisenkom/pytds

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

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