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?
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?
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...
The
sessionmaker
isn't necessary but if you're in the habit of creating the engine and the session at the same time to useraw_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 tocopy_expert
is also not the only way to it, there is a basiccopy_to
function that you can use with subset of the parameters that you could past to a normalCOPY
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
You can use:
I insert 200000 lines in 5 seconds instead of 4 minutes
If you can get to the engine you have all you need to do this:
Now you can work.
Here are some templates for the COPY statement to use with
cursor.copy_expert()
, a more complete and flexible option thancopy_from()
orcopy_to()
as it is indicated here: http://initd.org/psycopg/docs/cursor.html#cursor.copy_expert.Check out what the options above mean and others that may be of interest to your specific situation https://www.postgresql.org/docs/current/static/sql-copy.html.
IMPORTANT NOTE: The link to the documentation of
cursor.copy_expert()
indicates to use STDOUT to write out to a file and STDIN to copy from a file. But if you look at the syntax on the PostgreSQL manual, you'll notice that you can also specify the file to write to or from directly in the COPY statement. Don't do that, you're likely just wasting your time if you're not running as root (who runs Python as root during development?) Just do what's indicated in the psycopg2's docs and specify STDIN or STDOUT in your statement withcursor.copy_expert()
, it should be fine.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.
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 usingwhich you can use to execute
The cursor will be active in the same transaction as your session currently is. If a
commit
orrollback
happens, any further use of the cursor with throw apsycopg2.InterfaceError
, you would have to create a new one.You don't need to drop down to psycopg2, use raw_connection nor a cursor.
Just execute the sql as usual, you can even use bind parameters with
text()
:You can drop the
execution_options(autocommit=True)
if this PR will be accepted