well a CREATE TABLE will autocommit but only if it's on an Engine that's not already in a transaction....and when you use a Session it sets up a transaction that stays open until you say commit().
I can see the problem here, one way around is to actually bind the Session to a
connection like this:
conn = engine.connect()
sess = Session(bind=conn)
otherwise, just sending through the "commit" on the DBAPI connection directly
might not be so terrible. If you said,
session.connection().connection.commit() that should have the same effect.
On Aug 23, 2013, at 4:32 PM, Tim Tisdall <tisd...@gmail.com> wrote:
> I knew I'd find a possible solution right after asking... Isn't that always
> the way?
>
> I found that I can do DBSession.execute("COMMIT") to get MySQL to commit the
> temporary table but SQLAlchemy/transaction doesn't seem to pick up on it and
> I don't lose the connection to the threadpool.
>
> This seems kind of a hack, but does anyone have a better solution?
>
>
> On Fri, Aug 23, 2013 at 4:12 PM, Tim Tisdall <tisd...@gmail.com> wrote:
> I'll try to make this succinct...
>
> I'm creating a temporary table and then doing a query with it that takes a
> good length of time. I found that the source tables going into the temporary
> table were being locked after the temporary table was created even though
> they were no longer needed for the second much longer query. Apparently
> "create temporary table" in MySQL doesn't autocommit and so the transaction
> is locking a lot more than needed. So... I tried committing right after
> creating the temporary table, but now I randomly lose the temporary table
> because SQLAlchemy sees the commit as a reason to return the connection back
> to the connection pool and then get back another connection on the next
> query. So, as the temporary table is persistent only on that connection I
> usually lose the table as I usually don't get the very same connection back
> from the pool.
>
> I'd like to be able to tell MySQL to commit after creating the temporary
> table so I can drop the locks used to fill that table, but I want to make
> sure SQLAlchemy doesn't let go of the connection and return it to the pool.
>
> --
> You received this message because you are subscribed to a topic in the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/9dfigjQt1Bw/unsubscribe.
> To unsubscribe from this group and all its topics, 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/groups/opt_out.
>
>
> --
> 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/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
