使用PYODBC将pandas数据导入SQL服务器

Question

我正在尝试理解如何用Python从FTP服务器获取数据，然后把这些数据放到pandas中，最后再转移到SQL服务器。我的代码现在非常基础，我希望能得到一些建议或帮助。我已经尝试从FTP服务器加载数据，这个过程没有问题……如果我把这段代码去掉，改成从微软SQL服务器选择数据，那也是没问题的，所以连接字符串是有效的，但把数据插入到SQL服务器时似乎出现了问题。

import pyodbc
import pandas
from ftplib import FTP
from StringIO import StringIO
import csv

ftp = FTP ('ftp.xyz.com','user','pass' )
ftp.set_pasv(True)
r = StringIO()
ftp.retrbinary('filname.csv', r.write)

pandas.read_table (r.getvalue(), delimiter=',')


connStr = ('DRIVER={SQL Server Native Client 10.0};SERVER=localhost;DATABASE=TESTFEED;UID=sa;PWD=pass')
conn = pyodbc.connect(connStr)

cursor = conn.cursor()
cursor.execute("INSERT INTO dbo.tblImport(Startdt, Enddt, x,y,z,)" "VALUES                  (x,x,x,x,x,x,x,x,x,x.x,x)")
cursor.close()
conn.commit()
conn.close()
print"Script has successfully run!"

当我去掉FTP的代码后，这段代码运行得很好，但我不太明白如何进一步把数据放入微软SQL服务器，或者说是否有可能在不先保存到文件的情况下做到这一点。

Answer 1

这个在我的Python 3.5.2上有效：

import sqlalchemy as sa
import urllib
import pyodbc

conn= urllib.parse.quote_plus('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
engine = sa.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))

frame.to_sql("myTable", engine, schema='dbo', if_exists='append', index=False, index_label='myField')

Answer 2

我发现使用 bcp 工具（https://learn.microsoft.com/en-us/sql/tools/bcp-utility）在处理大数据集时效果最好。我有 270 万行数据，插入速度达到每秒 8 万行。你可以把你的数据框保存为 csv 文件（如果你的数据中没有制表符，可以用制表符作为分隔符，并使用 utf8 编码）。使用 bcp 时，我用的格式是 "-c"，到目前为止没有遇到任何问题。

Answer 3

是的，bcp 工具在大多数情况下看起来是最好的解决方案。

如果你想在 Python 中操作，下面的代码应该可以用。

from sqlalchemy import create_engine
import urllib
import pyodbc

quoted = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=YOUR\ServerName;DATABASE=YOur_Database")
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

df.to_sql('Table_Name', schema='dbo', con = engine, chunksize=200, method='multi', index=False, if_exists='replace')

不要忽视 method='multi'，因为它可以大大缩短任务执行的时间。

有时候你可能会遇到以下错误。

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]请求的参数太多。服务器最多支持 2100 个参数。请减少参数数量并重新发送请求。 (8003) (SQLExecDirectW)')

在这种情况下，首先要确定你的数据框中有多少列：df.shape[1]。然后把服务器支持的最大参数数量除以这个值，结果向下取整就是你可以使用的每批次的大小。

Answer 4

这是一个使用本地数据库 SQL 实例的 Python3 版本：

from sqlalchemy import create_engine
import urllib
import pyodbc
import pandas as pd

df = pd.read_csv("./data.csv")

quoted = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=(localDb)\ProjectsV14;DATABASE=database")
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

df.to_sql('TargetTable', schema='dbo', con = engine)

result = engine.execute('SELECT COUNT(*) FROM [dbo].[TargetTable]')
result.fetchall()

Answer 5

关于“写入 SQL 服务器”的部分，你可以使用 pandas 提供的方便的 to_sql 方法，这样就不需要逐行插入数据了。你可以查看 pandas 的文档，了解如何与 SQL 数据库进行交互：http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

要使用这个功能，你至少需要 pandas 0.14 版本，并且需要安装 sqlalchemy。假设 df 是你通过 read_table 得到的数据框，这里有个示例：

import sqlalchemy
import pyodbc
engine = sqlalchemy.create_engine("mssql+pyodbc://<username>:<password>@<dsnname>")

# write the DataFrame to a table in the sql database
df.to_sql("table_name", engine)

你还可以查看 to_sql 的文档页面。
关于如何使用 sqlalchemy 创建连接引擎以连接 SQL 服务器和 pyodbc 的更多信息，可以在这里找到：http://docs.sqlalchemy.org/en/rel_1_1/dialects/mssql.html#dialect-mssql-pyodbc-connect

---

不过，如果你的目标只是将 CSV 数据导入 SQL 数据库，你也可以考虑直接通过 SQL 来完成。比如可以参考 将 CSV 文件导入 SQL Server