使用PYODBC将panda中的数据获取到SQL服务器中

我试图了解python如何将数据从FTP服务器拉入panda，然后将其移动到SQL服务器。至少可以说，我在这里的代码非常初级，我正在寻求任何建议或帮助。我试着先从FTP服务器加载数据，这很好。。。。如果我删除这段代码并将其更改为select from ms-sql server，那么连接字符串就可以工作，但插入sql server似乎会造成问题。

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，或者即使不先保存到文件中也是可能的。

对于"写入sql server"部分，您可以使用panda的方便的to_sql方法（因此无需迭代行并手动插入）。请参阅有关使用panda与SQL数据库交互的文档：http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

您至少需要熊猫0.14才能正常工作，还需要安装sqlalchemy。举个例子，假设df是从read_table:获得的DataFrame

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连接引擎的更多信息，您可以在这里找到：http://docs.sqlalchemy.org/en/rel_1_1/dialects/mssql.html#dialect-mssql pyodbc连接

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

使用LocalDB 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()

是的，bcp实用程序似乎是大多数情况下的最佳解决方案。

如果您想继续使用Python，下面的代码应该可以使用。

from sqlalchemy import create_engine
import urllib
import pyodbc
quoted = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=YOURServerName;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'，因为它可以显著减少任务执行时间。

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

编程错误：（'42000'，'[42000][Microsoft][ODBC SQL Server驱动程序][SQL Server]传入请求的参数太多。这个服务器最多支持2100个参数。减少参数并重新发送请求。（8003）（SQLExecDirectW）'）

在这种情况下，请确定数据帧中的列数：df.shape[1]。将支持的最大参数数除以该值，并使用结果的下限作为块大小。

我发现使用bcp实用程序(https://learn.microsoft.com/en-us/sql/tools/bcp-utility)当您拥有大型数据集时效果最佳。我有270万行，以每秒80K行的速度插入。您可以将数据帧存储为csv文件（如果数据没有制表符和utf8编码，请使用制表符作为分隔符）。对于bcp，我使用了"-c"格式，到目前为止，它可以正常工作。

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

"由于Connection表示针对数据库的开放资源，因此我们希望始终将此对象的使用范围限制在特定上下文中，而实现这一点的最佳方法是使用Python上下文管理器形式，也称为with语句"https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html

这个例子就是

from sqlalchemy import create_engine
import urllib
import pyodbc
connection_string = (
    "Driver={SQL Server Native Client 11.0};"
    "Server=myserver;"
    "UID=myuser;"
    "PWD=mypwd;"
    "Database=mydb;"
)
quoted = urllib.parse.quote_plus(connection_string)
engine = create_engine(f'mssql+pyodbc:///?odbc_connect={quoted}')
with engine.connect() as cnn:
    df.to_sql('mytable',con=cnn, if_exists='replace', index=False)

以下是我使用sqlalchemy时使用的方法。请注意最后一部分?driver=SQL+Server'。

import sqlalchemy
import pyodbc
engine = sqlalchemy.create_engine('mssql+pyodbc://MyUser:MyPWD@dataserver.sandbox.myserver/MY_DB?driver=SQL+Server')
dt.to_sql("PatientResultTest", engine,if_exists='append')

SQL表的开头需要一个index列来存储数据帧的索引值。

# using class function   
import pandas as pd
import pyodbc
import sqlalchemy
import urllib
class data_frame_to_sql():    
   def__init__(self,dataFrame,sql_table_name):
           self.dataFrame=dataFrame
           self.sql_table_name=sql_table_name
           def conversion(self):
       
           params = urllib.parse.quote_plus("DRIVER={SQL Server};"
                                            "SERVER=######;"
                                            "DATABASE=####;"
                                            "UID=#####;"
                                            "PWD=###;")
       
       
           try:
               engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))
               return f"Table '{self.sql_table_name}' added sucsessfully in database" ,self.dataFrame.to_sql(self.sql_table_name, engine)
       
           except Exception as e :
               e=str(e).replace(".","")
               print(f"{e} in Database." )
data={"BusinessEntityID":["1","2","3"],"FirstName":["raj","abhi","amir"],"LastName":["kapoor","bachn","khhan"]}
df = pd.DataFrame(data, columns= ['BusinessEntityID','FirstName','LastName'])
ab=data_frame_to_sql(df,"ab").conversion()
print(ab)

没有必要使用sqlamchemy，可以直接创建与pyodbc的连接，将其用于panda，如下所示：

with pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) as newconn:
    df = pd.read_sql(<your sql goes here>,newconn)

相关内容

最新更新

热门标签：