7

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

Chris
  • 1,401
  • 4
  • 17
  • 28

1 Answers1

8

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.

ADataGMan
  • 449
  • 1
  • 7
  • 23
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • thanks...reading between the lines, sounds like you'd assert SQLAlchemy is an all ways preferable option? – Chris Nov 06 '18 at 22:42
  • 1
    *"SQLAlchemy is an all ways preferable option"* - I'm always leery of absolutes. In many cases I'll advocate for the [KISS principle](https://en.wikipedia.org/wiki/KISS_principle), but in this case you're working with pandas and they have essentially "outsourced" their database access layer to SQLAlchemy, so that's how you can get the benefits of pandas' built-in database interoperability and not have to re-invent the wheel yourself. – Gord Thompson Nov 07 '18 at 00:45
  • I try to keep it simple myself, partly why I'd wanted to avoid an additional download if possible...actually ran into issues downloading SQLAlchemy last night (firewall here can be a real bear). – Chris Nov 07 '18 at 17:40