使用PYODBC将 pandas 中的数据获取到SQL Server中 [英] Get data from pandas into a SQL server with PYODBC

问题描述

我试图了解python如何将数据从FTP服务器拉到熊猫，然后将其移到SQL服务器.至少可以说，我的代码非常初级，我正在寻找任何建议或帮助.我试图先从FTP服务器上加载数据，这很好....如果我随后删除此代码，并将其更改为ms sql服务器中的select，就可以了，因此连接字符串可以正常工作，但是可以插入到SQL中服务器似乎引起了问题.

I am trying to understand how python could pull data from an FTP server into pandas then move this into SQL server. My code here is very rudimentary to say the least and I am looking for any advice or help at all. I have tried to load the data from the FTP server first which works fine.... If I then remove this code and change it to a select from ms sql server it is fine so the connection string works, but the insertion into the SQL server seems to be causing problems.

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代码时，它可以完美运行，但是我不知道如何进行下一次跳转，以将其导入Microsoft SQL Server，或者即使没有先保存到文件中也可以实现.

When I remove the ftp code this runs perfectly, but I do not understand how to make the next jump to get this into Microsoft SQL server, or even if it is possible without saving into a file first.

推荐答案

对于写入sql服务器"部分，您可以使用便捷的pandas to_sql方法(因此无需遍历行并执行手动插入).请参阅有关使用pandas与SQL数据库进行交互的文档: http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

For the 'write to sql server' part, you can use the convenient to_sql method of pandas (so no need to iterate over the rows and do the insert manually). See the docs on interacting with SQL databases with pandas: http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

您至少需要熊猫0.14才能正常运行，并且还需要安装sqlalchemy.一个示例，假设df是您从read_table获得的DataFrame:

You will need at least pandas 0.14 to have this working, and you also need sqlalchemy installed. An example, assuming df is the DataFrame you got from 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 的页面.
有关如何为带有pyobdc的sql server的sqlalchemy创建连接引擎的更多信息，可以在这里找到:

See also the documentation page of to_sql.
More info on how to create the connection engine with sqlalchemy for sql server with pyobdc, you can find here:http://docs.sqlalchemy.org/en/rel_1_1/dialects/mssql.html#dialect-mssql-pyodbc-connect

但是，如果您的目标只是将csv数据导入SQL数据库，则也可以考虑直接从SQL执行此操作.参见例如将CSV文件导入SQL Server

But if your goal is to just get the csv data into the SQL database, you could also consider doing this directly from SQL. See eg Import CSV file into SQL Server

这篇关于使用PYODBC将 pandas 中的数据获取到SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！