PG copy_expert, and sessions

[Gregg Lind]

I've been trying to use pg's "copy_expert" semantics with sessions,
but
something is eluding me.

Observations:

1. It seems like I have to wrap the copy_expert cursor in a
transaction and commit()
in order to make it actually fire (see pgsql_copy below).

2. When I try wrapping it in session.begin() / commit() (as
commented out
below) nothing happens.



The 'real problem' I'm tackling is:

In a rollbackable way:

1. drop and recreate a table
2. fill it with data using 'copy'

If this is possible, how do I get it to all play nice? Should I use
engine
instead of session? If it's not possible, that's sad news.


Ref:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/6219e5e040dbbe2f/fbbdee1d70d7a00b?lnk=gst&q=copy_expert#fbbdee1d70d7a00b

===========================
def data_to_tmp(data,dlm="\t"):
""" given some tuples of data, return a filehandle to
a file filled with data

Args:
data. iterable of stringable fields.
dlm. how fields will be joined in the output.

Return:
ofh. opened handle to NamedTemporaryFile, at pos 0.

Raises:
ValueError, TypeError: fields aren't map(str,x)-able
(various):

>>> data = [(1,2,3)]
>>> fh = data_to_tmp(data,dlm=',')
>>> print fh.read()
1,2,3
<BLANKLINE>
>>> fh = data_to_tmp(data,dlm=' | ')
>>> print fh.read()
1 | 2 | 3
<BLANKLINE>
"""
ofh = NamedTemporaryFile('w+')
for d in data:
print >> ofh, dlm.join(map(str,d))

ofh.flush()
ofh.seek(0)
return ofh



def pgsql_copy(session,tablename,string_buffer):
SQL_COPY_FROM = '''COPY %(tablename)s FROM STDIN USING DELIMITERS
E'\t' ''' \
% (dict(tablename=tablename))
connection = session.bind.connect()
# NOTE: without being wrapped in a transaction / commit, the
cursor.copy_expert
# WILL NOT WORK
transaction = connection.begin()
#session.begin(subtransactions=True)
print ("in pgsql_copy")
#session.begin(subtransactions=True)
try:
# We need the raw psycopg-cursor, hidden deep within SA's
abstractions.
cursor = connection.connection.cursor().cursor
print string_buffer.read()
string_buffer.seek(0)
cursor.copy_expert(SQL_COPY_FROM, string_buffer)
#transaction.commit()
#set_trace()
session.commit()
print 'yay'
except Exception,exc:
session.rollback()
#transaction.rollback()
print exc
raise

return True

=======================================

[Michael Bayer]

first consult PG documentation to confirm that the "copy expert" function is transactional.

Then all you need to do is run it with session execute:

session.execute("COPY ...")
session.commit()

subtransactions=True is not relevant here. The usage of an external Transaction object is also not necessary. if you want to run CREATE TABLE statements using the current transaction, use session.connection() to get the Connection for the current transaction and use that with create(), create_all().

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>