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().
On Jun 19, 2010, at 2:53 PM, Gregg Lind wrote:
>
>
> 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.
>
--
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.
