Re: [sqlalchemy] Complicated self join
I thought subquery and alias where equivalent. Thanks Mike! Den fredag 6 mars 2015 kl. 01:29:20 UTC+1 skrev Michael Bayer: Dani Hodovic dani...@gmail.com javascript: wrote: I've been struggling with a query that gets the most recent date as described here: http://stackoverflow.com/a/123481/2966951 I've been able to produce a SQLAlchemy variant, but it seems to be MUCH slower when executed with MySQL. It also looks slightly differentwith parameters around the inner query. http://pastebin.com/NWEsFtAY I’m not sure why you’re using subquery() for the SQLAlchemy version when the original SQL you’re looking for has no subquery (and the subquery will perform *terribly* on MySQL). Just join to “mytable” as an alias() itself. t1 = mytable.alias() t2 = mytable.alias() s.query(t1.userid, t1.date).outerjoin(t2, and_(t1.userid == t2.userid, t1.date t2.date)).filter(t2.userid == None) Please don't point me to scalar subqueries as I've looked at the documentation and this was the best I could come up with. As this query is a part of a larger query I attempted to solve it with text(), but combining ORM code and raw SQL is a pain in the ass. If there is a text solution however, where this query could be joined with another query that would work too. -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
[sqlalchemy] Database session variables with connection pooling
I'm implementing database session variables (in Oracle, DBMS_SESSION.SET_CONTEXT(...)), in order to be able to set (from sqlalchemy) and retrieve (from a database trigger) the application userid and URL path during table audit triggers. The tricky bit is that if I set the user to 'user1', that remains in the session in the database even when a different sqlalchemy thread grabs that same session from the connection pool. I want to prevent the wrong information accidentally still being in the session, so I want to be sure to reset it when appropriate and I'm wondering whether checkout from the Pool is the event you would recommend? @event.listens_for(engine, 'checkout') def receive_checkout(dbapi_connection, connection_record, connection_proxy): If the same database session is recycled from the connection pool, will it have the same *connection_record*? I'd prefer to record the fact that I've set the database session's variables on an object (such as connection_record) so that subsequent requests can detect whether it needs to be reset. Will connection_record correspond to a database session? Thanks in advance for any advice here. Kent -- 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/d/optout.
Re: [sqlalchemy] Database session variables with connection pooling
Kent jkentbo...@gmail.com wrote: I'm implementing database session variables (in Oracle, DBMS_SESSION.SET_CONTEXT(...)), in order to be able to set (from sqlalchemy) and retrieve (from a database trigger) the application userid and URL path during table audit triggers. The tricky bit is that if I set the user to 'user1', that remains in the session in the database even when a different sqlalchemy thread grabs that same session from the connection pool. I want to prevent the wrong information accidentally still being in the session, so I want to be sure to reset it when appropriate and I'm wondering whether checkout from the Pool is the event you would recommend? @event.listens_for(engine, 'checkout') def receive_checkout(dbapi_connection, connection_record, connection_proxy): If the same database session is recycled from the connection pool, will it have the same connection_record? I'd prefer to record the fact that I've set the database session's variables on an object (such as connection_record) so that subsequent requests can detect whether it needs to be reset. Will connection_record correspond to a database session? For this kind of thing you normally reset the state on the “checkin” event. The connection_record does in fact follow around the DBAPI connection, however the .info dictionary is given here as the primary way to track things with a DBAPI connection. .info is available on Connection, the connection record, and the pool wrapper, and it will track the DBAPI connection for its full lifespan, until the connection is closed. So put whatever memoizations you need into the .info dictionary, and then you can pretty much set / reset the state with any of the pool events. Thanks in advance for any advice here. Kent -- 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/d/optout. -- 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/d/optout.
