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
=======================================
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
