psycopg2: insert multiple rows with one query

by

I built a program that inserts multiple lines to a server that was located in another city.

I found out that using this method was about 10 times faster than executemany. In my case tup is a tuple containing about 2000 rows. It took about 10 seconds when using this method:

args_str=",".join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

and 2 minutes when using this method:

cur.executemany("INSERT INTO table VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)", tup)

More Related Contents:

  1. Parameterized queries with psycopg2 / Python DB-API and PostgreSQL
  2. Passing table name as a parameter in psycopg2
  3. DatabaseError: current transaction is aborted, commands ignored until end of transaction block?
  4. error installing psycopg2, library not found for -lssl
  5. Error: pg_config executable not found when installing psycopg2 on Alpine in Docker
  6. How to set up a PostgreSQL database in Django
  7. psycopg2.OperationalError: FATAL: unsupported frontend protocol 1234.5679: server supports 2.0 to 3.0
  8. Psycopg2 image not found
  9. Error Installing Psycopg2 on MacOS 10.9.5
  10. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  11. Create a Postgres database using python
  12. Python/postgres/psycopg2: getting ID of row just inserted
  13. Escape SQL “LIKE” value for Postgres with psycopg2
  14. How to set connection timeout in SQLAlchemy
  15. Mac + virtualenv + pip + postgresql = Error: pg_config executable not found
  16. “Failed building wheel for psycopg2” – MacOSX using virtualenv and pip
  17. Psycopg2, Postgresql, Python: Fastest way to bulk-insert
  18. Insert Python Dictionary using Psycopg2
  19. Use binary COPY table FROM with psycopg2
  20. How Postgresql COPY TO STDIN With CSV do on conflic do update?
  21. How to write DataFrame to postgres table
  22. CS50: LIKE operator, variable substitution with % expansion
  23. SQLAlchemy create_all() does not create tables
  24. GeoDjango on Windows: “Could not find the GDAL library” / “OSError: [WinError 126] The specified module could not be found”
  25. Can’t install psycopg2 with pip in virtualenv on Mac OS X 10.7
  26. psycopg2 installation error – Library not loaded: libssl.dylib
  27. How to upsert pandas DataFrame to PostgreSQL table?
  28. Case insensitive unique model fields in Django?
  29. python adds “E” to string
  30. How to perform filtering with a Django JSONField?

Leave a Comment