On Aug 2, 2011, at 9:07 AM, RVince wrote:
> In my controller class, I perform a rather basic, straightforward SQL
> query:
>
> connection = engine.connect()
connection, OK
> trans = connection.begin()
start a transaction, OK
> try:
> c.result = connection.execute("select
> current_disposition_code,count(*) as num from cms_input_file group by
> current_disposition_code;")
> connection.close()
close the connection ? what happened to trans, wheres the rollback or commit ?
what about your c.result that hasn't been iterated yet ? both of these need
an active Connection to proceed correctly. Technically the transaction is
fine with just the connection.close() at the end but its a strange form that
doesn't make the intent clear. The cursor referenced by the ResultProxy
definitely should have exclusive access to its parent Connection for its whole
lifespan, though, closing it out can have one of several negative effects,
depending on the backend and the configuration of the connection pool.
Want to know what would be easy here ?
c.result = engine.execute("select * from my_table")
then you're done. The ResultProxy in this case manages the Connection itself
which will be closed when the ResultProxy closes. However this assumes you
just need one SQL statement in a transaction, it seems that perhaps the begin()
call is because you're doing several things with the one Connection.
Ideally, assuming this is Pylons, you'd have a single Connection declared for
all controllers up in your BaseController, with a trans = connection.begin() at
the top and trans.rollback() at the bottom. That way the mechanics of
providing database context to controllers is handled in application logic and
not business logic. Or you'd have the ORM Session in place and just use
Session.execute() to get a result.
--
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.
