Re: [sqlalchemy] emulating psql's "\copy" function?

[Jon Nelson]

On Tue, Oct 13, 2015 at 2:49 PM, Jon Nelson  wrote:
> 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?

[Jon Nelson]

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.


-- 
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.