将在Python中创建的pandas数据框插入SQL Server [英] Insert pandas dataframe created within Python into SQL Server

问题描述

如前所述，我已经在Python中创建了一个数据集合(4万行，5列)，我想将这些数据重新插入到SQL Server表中.

As referenced, I've created a collection of data (40k rows, 5 columns) within Python that I'd like to insert back into a SQL Server table.

通常，在SQL中，我会调用'select * into myTable from dataTable'进行插入，但是位于pandas数据框中的数据显然会使此操作复杂化.

Typically, within SQL I'd make a 'select * into myTable from dataTable' call to do the insert, but the data sitting within a pandas dataframe obviously complicates this.

我不正式反对使用SQLAlchemy(尽管它更愿意避免其他下载和安装)，但是我更喜欢在Python内部进行此操作，并希望使用pyodbc连接到SSMS.

I'm not formally opposed to using SQLAlchemy (though would prefer to avoid another download and install), but would prefer to do this natively within Python, and am connecting to SSMS using pyodbc.

有没有一种简单的方法可以避免循环(即逐行插入)?

Is there a straightforward way to do this that avoids looping (ie, insert row by row)?

推荐答案

如此答案所示，我们可以将通过执行list(df.itertuples(index=False, name=None)，将名为df的DataFrame放入元组列表，这样我们就可以将其传递给executemany，而不必(明确地)遍历每一行.

As shown in this answer we can convert a DataFrame named df into a list of tuples by doing list(df.itertuples(index=False, name=None) so we can pass that to executemany without (explicitly) looping through each row.

crsr = cnxn.cursor()
crsr.fast_executemany = True
crsr.executemany(
    "INSERT INTO #tablename (col1, col2) VALUES (?, ?)",
    list(df.itertuples(index=False, name=None))
)
crsr.commit()

这将是您的本机"，但是如果DataFrame包含pyodbc无法识别的pandas数据类型(它将Python类型作为参数值)，则可能导致错误.使用SQLAlchemy和pandas的to_sql方法可能会更好.

That is as "native" as you'll get, but it can lead to errors if the DataFrame contains pandas data types that are not recognized by pyodbc (which expects Python types as parameter values). You may still be better off using SQLAlchemy and pandas' to_sql method.

这篇关于将在Python中创建的pandas数据框插入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！