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

postgresqlpsycopg2python

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:

Using pyformat binding style for parametric insertion
Using executemany on the list of tuples, and
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.

Best Answer

Yeah, I would vote for COPY, providing you can write a file to the server's hard drive (not the drive the app is running on) as COPY will only read off the server.

Related Solutions

Python – way to run Python on Android

One way is to use Kivy:

Open source Python library for rapid development of applications that make use of innovative user interfaces, such as multi-touch apps.

Kivy runs on Linux, Windows, OS X, Android and iOS. You can run the same [python] code on all supported platforms.

Kivy Showcase app

Python – What’s the canonical way to check for type in Python

To check if o is an instance of str or any subclass of str, use isinstance (this would be the "canonical" way):

if isinstance(o, str):

To check if the type of o is exactly str (exclude subclasses):

if type(o) is str:

The following also works, and can be useful in some cases:

if issubclass(type(o), str):

See Built-in Functions in the Python Library Reference for relevant information.

One more note: in this case, if you're using Python 2, you may actually want to use:

if isinstance(o, basestring):

because this will also catch Unicode strings (unicode is not a subclass of str; both str and unicode are subclasses of basestring). Note that basestring no longer exists in Python 3, where there's a strict separation of strings (str) and binary data (bytes).

Alternatively, isinstance accepts a tuple of classes. This will return True if o is an instance of any subclass of any of (str, unicode):

if isinstance(o, (str, unicode)):

Best Answer

Related Solutions

Python – way to run Python on Android

Python – What’s the canonical way to check for type in Python

Related Topic