SQLAlchemy, Psycopg2 and Postgresql COPY
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
If your engine is configured with a psycopg2 connection string (which is the default, so either "postgresql://..."
or "postgresql+psycopg2://..."
), you can create a psycopg2 cursor from an SQL Alchemy session using
cursor = session.connection().connection.cursor()
which you can use to execute
cursor.copy_from(...)
The cursor will be active in the same transaction as your session currently is. If a commit
or rollback
happens, any further use of the cursor with throw a psycopg2.InterfaceError
, you would have to create a new one.