SQLAlchemy, Psycopg2 and Postgresql COPY

Question

It looks like Psycopg has a custom command for executing a COPY:

psycopg2 COPY using cursor.copy_from() freezes with large inputs

Is there a way to access this functionality from with SQLAlchemy?

Answer 1

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

Answer 2

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.