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_engineeng = 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_tohttp://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.

It doesn't look like it.

You may have to just use psycopg2 to expose this functionality and forego the ORM capabilities. I guess I don't really see the benefit of ORM in such an operation anyway since it's a straight bulk insert and dealing with individual objects a la an ORM would not really make a whole lot of sense.