pandas 从 Python 连接到 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36021385/
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 from Python to SQL Server
提问by whytheq
I'd like to connect from IPython notebook to a SQL-Server database via integrated security.
我想通过集成安全性从 IPython 笔记本连接到 SQL-Server 数据库。
Is this possible? I'm guessing yes it is.
这可能吗？我猜是的。
How do I format the connection string in the following?
如何格式化以下连接字符串？
import pandas as pd
import pandas.io.sql as psql
sql = "SELECT * FROM WHdb.dbo.vw_smallTable"
cnx = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=WHdb;Data Source=OurServerName"
data = psql.read_sql(sql, cnx)
This just gives an error.
Am I going about the cnxincorrectly?
这只是给出了一个错误。我会cnx错误地处理吗？
回答by user7110627
You need to install the package, pypyodbc
你需要安装包，pypyodbc
!pip install pypyodbc
Then, you can import it as follows:
然后，您可以按如下方式导入它：
import pypyodbc as podbc
You can now create the connection:
您现在可以创建连接：
conn = podbc.connect("Driver={SQL Server};Server=<YourServer>;Database=<YourDatabase>;uid=<YourUserName>;pwd=<YourPassword>"
Finally, you fetch your data as follows:
最后，您按如下方式获取数据：
cursor = conn.cursor()
sql = "SELECT * FROM WHdb.dbo.vw_smallTable"
cursor.execute(sql)
data = cursor.fetchone()
while data:
print(str(data[0]) + ", " + ... + ", " + str(data[n-1]))
data = cursor.fetchone()
conn.close()
Note that n = number of columns in your table.
请注意，n = 表中的列数。
回答by loveR
This is what worked for me well...
这对我来说很有效......
import pyodbc
server = 'myserver'
database = 'mydb'
username = 'myusername'
password = 'mypassword'
#Connection String
connection = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = connection.cursor()
#Sample select query
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
print row[0]
row = cursor.fetchone()
