Re: [sqlalchemy] emulating psql's "\copy" function?
On Tue, Oct 13, 2015 at 2:49 PM, Jon Nelsonwrote: > On Tue, Oct 13, 2015 at 1:55 PM, Jonathan Vanasco > 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.
Re: [sqlalchemy] emulating psql's "\copy" function?
On Tue, Oct 13, 2015 at 1:55 PM, Jonathan Vanascowrote: > 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. -- 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.
[sqlalchemy] emulating psql's "\copy" function?
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? -- 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.