On Tue, Oct 13, 2015 at 2:49 PM, Jon Nelson <jnel...@jamponi.net> wrote: > On Tue, Oct 13, 2015 at 1:55 PM, Jonathan Vanasco <jonat...@findmeon.com> > wrote: >> As part of an archiving routine that uses SqlAlchemy, I need to execute some >> pretty specific commands using `\copy` to archive a selection of columns, in >> a special order, into a csv. >> >> Doing some digging, psycopg2 provides an interface to `COPY` -- but that >> doesn't work for my needs. >> I'd rather not use a subprocess to handle run `\copy` in psql, because then >> it's not in the transaction. >> >> I'll be running this to partition 10GB of data into a lot of 10-50MB >> chunks... so I'd like to avoid piping this through sqlalchemy. i'm not >> opposed to pulling this in row-by-row, but I'd really rather avoid it. I >> have almost 250MM rows right now, and the future "nightly" build will be >> doing about 1MM at a time. >> >> Does anyone have a suggestion for a workaround? > > I should be able to provide you with something, as I use this > functionality quite often. However, I must step away from my desk for > the moment and thus there will be a delay.
This is what I've done, and it might not be the best way to do things but it works for me: one way or another, get a connection (either from your session or your active connection, etc..): c = get_your_connection() with c.connection.cursor() as cursor: cursor.copy_expert(sql_statement_here, some_writable_file) # or cursor.copy_from(some_readable_file, tablename, ....) # or cursor.copy_to(some_writable_file, tablename, ....) I didn't place all of the arguments in the calls above, refer to the psycopg2 docs for that. The above is almost pseudo-code. I also take pains to properly quote the table names, etc. whenever that information is generated or passed (and often even when hardcoded). Hope this helps. -- Jon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.