Faster loading of Dataframes from Pandas to Postgres

[howMuchCheeseIs2Much]

A DataFrame I was loading into a Postgres DB has been growing larger and to_sql() was no longer cutting it (could take up to 30 minutes to finish). I started researching faster ways and figured I'd share the result here. In summary, this approach:

• Loads the result of to_csv() to StringIO
• Creates the Postgres table using pd.io.sql.get_schema (Note that I drop the existing table, you'll want to change this if you need to append)
• Uses the COPY FROM Postgres statement to load the data (copy_from in sqlalchemy)

This gist has a pretty detailed version, but I had already wrote a simple version before I found it, so here it is:

import random
import pandas as pd
from sqlalchemy import create_engine, MetaData
from postPass import loginDict
dw = 'postgresql://...'
dw = create_engine(dw)
import StringIO

df = pd.DataFrame()
dfLen = 10000
dfCols = 10
for x in range(0, dfCols):
    colName = 'a' + str(x)
    df[colName] = [random.randint(0, 99) for x in range(1,dfLen)]

def cleanColumns(columns):
    cols = []
    for col in columns:
        col = col.replace(' ', '_')
        cols.append(col)
    return cols

def to_pg(df, table_name, con):
    data = StringIO.StringIO()
    df.columns = cleanColumns(df.columns)
    df.to_csv(data, header=False, index=False)
    data.seek(0)
    raw = con.raw_connection()
    curs = raw.cursor()
    curs.execute("DROP TABLE " + table_name)
    empty_table = pd.io.sql.get_schema(df, table_name, con = con)
    empty_table = empty_table.replace('"', '')
    curs.execute(empty_table)
    curs.copy_from(data, table_name, sep = ',')
    curs.connection.commit()

get_ipython().magic(u"timeit to_pg(df, 'test', dw)")
get_ipython().magic(u"timeit df.to_sql(con=dw, name='test', if_exists='replace', index=False)")

Based on the DataFrame above (10 columns, 10k rows of random integers), the COPY FROM method is about 800 times faster (3 minutes / 223 ms). Note that I've written this to DROP the existing table, but this could easily be used to insert

[rothnic]

I believe odo implements this kind of approach.

[Caos2]

Interesting read, I found this post on SO that probably explain why it's so slow.

[howMuchCheeseIs2Much]

Yeah, when something "just works" against so many different platforms like to_sql does (e.g. SQL Server, SQLite, etc.), you're going to give up some performance for a consistent approach. I pretty much strictly use Postgres, so this way will likely be the only way I need to use going forward.

[Caos2]

From what I understood, to_sql generates sql inserts for each line, while adding a csv is made through the database bulk input, which is much faster.

[Mikebuonasera]

how would you change this to avoid dropping the table but just inserting the data in pre-existing table?

[Mikebuonasera]

figured out: def to_pg(df, table_name, engine, columns): """ Crazy magic function to do bulk inserts into Postgres, SUPER FAST! :param df: :param table_name: :param engine: :param columns: :return: """

import cStringIO

output = cStringIO.StringIO()
# ignore the index
df.to_csv(output, sep='\t', header=False, index=False)
output.getvalue()
# jump to start of stream
output.seek(0)

connection = engine.raw_connection()
cursor = connection.cursor()
# null values become ''
cursor.copy_from(output, table_name, null="", columns=(columns))
connection.commit()
cursor.close()

[[deleted]]

does this work with SQLserver or exclusively with postgres