SQLAlchemy, Psycopg2 and Postgresql COPY

by

accepted answer is correct but if you want more than just the EoghanM’s comment to go on the following worked for me in COPYing a table out to CSV…

from sqlalchemy import sessionmaker, create_engine

eng = create_engine("postgresql://user:pwd@host:5432/db")
ses = sessionmaker(bind=engine)

dbcopy_f = open('/tmp/some_table_copy.csv','wb')

copy_sql="COPY some_table TO STDOUT WITH CSV HEADER"

fake_conn = eng.raw_connection()
fake_cur = fake_conn.cursor()
fake_cur.copy_expert(copy_sql, dbcopy_f)

The sessionmaker isn’t necessary but if you’re in the habit of creating the engine and the session at the same time to use raw_connection you’ll need separate them (unless there is some way to access the engine through the session object that I don’t know). The sql string provided to copy_expert is also not the only way to it, there is a basic copy_to function that you can use with subset of the parameters that you could past to a normal COPY TO query. Overall performance of the command seems fast for me, copying out a table of ~20000 rows.

http://initd.org/psycopg/docs/cursor.html#cursor.copy_to
http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Engine.raw_connection

More Related Contents:

  1. Setting application_name on Postgres/SQLAlchemy
  2. SQLAlchemy support of Postgres Schemas
  3. uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac
  4. Can’t connect the postgreSQL with psycopg2
  5. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  6. How to set connection timeout in SQLAlchemy
  7. How to reset postgres’ primary key sequence when it falls out of sync?
  8. How to import CSV file data into a PostgreSQL table
  9. The forgotten assignment operator “=” and the commonplace “:=”
  10. Equivalent to unpivot() in PostgreSQL
  11. Passing user id to PostgreSQL triggers
  12. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  13. I forgot the password I entered during postgres installation
  14. Do nullable columns occupy additional space in PostgreSQL?
  15. Error: pg_config executable not found when installing psycopg2 on Alpine in Docker
  16. How to configure PostgreSQL to accept all incoming connections
  17. Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections [duplicate]
  18. Disable PostgreSQL foreign key checks for migrations
  19. Prevent recursive trigger in PostgreSQL
  20. PostgreSQL Error: Relation already exists
  21. Returning from a function with OUT parameter
  22. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  23. SQLAlchemy: engine, connection and session difference
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. Update timestamp when row is updated in PostgreSQL
  26. Postgresql query array of objects in JSONB field
  27. How to add a conditional unique index on PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. “Failed building wheel for psycopg2” – MacOSX using virtualenv and pip
  30. PostgreSQL composite primary key

Leave a Comment