CODEWITHSUNDEEP
sql-serverpython-3.xpandaspyodbc
Chris
Chris

Reputation: 1702

Insert pandas dataframe created within Python into 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.

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.

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)?

Upvotes: 7

Views: 12304

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

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()

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.

Upvotes: 8

Related Questions