how to transform pandas dataframe for insertion via executemany() statement?

python database pandas executemany
Colin O'Brien picture Colin O'Brien · Apr 29, 2015 · Viewed 18k times · Source

I have a fairly big pandas dataframe - 50 or so headers and a few hundred thousand rows of data - and I'm looking to transfer this data to a database using the ceODBC module. Previously I was using pyodbc and using a simple execute statement in a for loop but this was taking ridiculously long (1000 records per 10 minutes)...

I'm now trying a new module and am trying to introduce executemany() although I'm not quite sure what's meant by sequence of parameters in:

    cursor.executemany("""insert into table.name(a, b, c, d, e, f) 
values(?, ?, ?, ?, ?), sequence_of_parameters)

should it look like a constant list working through each header like

    ['asdas', '1', '2014-12-01', 'true', 'asdasd', 'asdas', '2', 
'2014-12-02', 'true', 'asfasd', 'asdfs', '3', '2014-12-03', 'false', 'asdasd']
  • where this is an example of three rows

or what is the format that's needed?

as another related question, how then can I go about converting a regular pandas dataframe to this format?

Thanks!

Answer

ansen picture ansen · May 12, 2015

You can try this:

cursor.executemany(sql_str, your_dataframe.values.tolist())

Hope it helps.

Related questions

ValueError: Must pass DataFrame with boolean values only

Question In this datafile, the United States is broken up into four regions using the "REGION" column. Create a query that finds the counties that belong to regions 1 or 2, whose name starts with 'Washington', and whose POPESTIMATE2015 was greater than …

Read More
Pandas lookup, mapping one column in a dataframe to another in a different dataframe

I have two pandas dataframes: df1 and df2. df1 has columns X and Y and weeknum. df2 has columns Z, weeknum, and datetime. I want to basically keep df1 and have an extra column in it that is corresponding datetime …

Read More
Add a sequence number to each element in a group using python

I have a dataframe of individuals who each have multiple records. I want to enumerate the record in the sequence for each individual in python. Essentially I would like to create the 'sequence' column in the following table: patient date …

Read More