blaze / blaze/odo

Pandas to Postgres -> My own method is a lot faster than odo (or I made a mistake)

Open
#614 9 comments 21 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
1k
Forks
131
PR merge metrics
No merged PRs in 30d

Description

I have a pandas dataframe of 8 million rows. I found a fast method on stackoverflow to write this data to Postgres: takes less than 2 minutes.
Today I found odo and was hoping it would be faster, but it wasn't.
In fact my guess is it would take 30 minutes or so with odo, I stopped after 7 minutes.

Here's the code for faster writing of dataframe to Postgres, hope it helps you guys:
```
from cStringIO import StringIO

def df_to_database(engine, df, schema, table_name, if_exists='replace', sep='\x01', encoding='utf-8'):
# Create Table
df[:0].to_sql(table_name, engine, if_exists=if_exists, index=False, schema=schema)

# Prepare data
output = StringIO()
df.to_csv(output, sep=sep, header=False, encoding=encoding, index=False)
output.seek(0)

# Insert data
connection = engine.raw_connection()
cursor = connection.cursor()
schema_tablename = '{}.{}'.format(schema, table_name)
cursor.copy_from(output, schema_tablename, sep=sep, null='')
connection.commit()
cursor.close()
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.