Psycopg2, Postgresql, Python: Fastest way to bulk-insert
postgresql, psycopg2, python
Solution
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.
Problem
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.