Alex Rivera | Logout

Psycopg2, Postgresql, Python: Fastest way to bulk-insert

Asked 2010-02-16T09:36:50.787
47

I'm looking for the most efficient way to bulk-insert some millions of tuples into a database. I'm using Python, PostgreSQL and psycopg2.

I have created a long list of tulpes that should be inserted to the database, sometimes with modifiers like geometric Simplify.

The naive way to do it would be string-formatting a list of INSERT statements, but there are three other methods I've read about:

  1. Using pyformat binding style for parametric insertion
  2. Using executemany on the list of tuples, and
  3. Using writing the results to a file and using COPY.

It seems that the first way is the most efficient, but I would appreciate your insights and code snippets telling me how to do it right.

python postgresql psycopg2
Edit
Report

1 Answer

7

You could use a new upsert library:

$ pip install upsert

(you may have to pip install decorator first)

conn = psycopg2.connect('dbname=mydatabase')
cur = conn.cursor()
upsert = Upsert(cur, 'mytable')
for (selector, setter) in myrecords:
    upsert.row(selector, setter)

Where selector is a dict object like {'name': 'Chris Smith'} and setter is a dict like { 'age': 28, 'state': 'WI' }

It's almost as fast as writing custom INSERT[/UPDATE] code and running it directly with psycopg2... and it won't blow up if the row already exists.

answered 2012-09-27T01:27:02.230

Your Answer