[sqlalchemy] Re: Having a proxy attribute along a relationship path
What version of SqlAlchemy are you using, and what error are you seeing? I use the association_proxy for M2M, M2O, O2O all the time. -- 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] Having a proxy attribute along a relationship path
Hi there,
I was wondering if it was possible to have an attribute on a model that is
proxied through a relationship in a many-to-one case. I know for M2M there
is association_proxy, but that does not seem to work.
Example time!
class Show(Base):
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
class Season(Base):
number = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
show_id = sqlalchemy.Column(
sqlalchemy.ForeignKey('show.id'), primary_key=True)
show = sqlalchemy.orm.relationship(Show)
class Episode(Base):
show_id = sqlalchemy.Column(sqlalchemy.Integer, nullable=False)
season_number = sqlalchemy.Column(sqlalchemy.Integer, nullable=False)
__table_args__ = (
sqlalchemy.ForeignKeyConstraint(
['show_id', 'season_number'],
['season.show_id', 'season.number']), {})
season = sqlalchemy.orm.relationship(Season, lazy='joined')
This is a structure almost anyone can recognize: A TV Show has Seasons,
each of those has an episode. Season->Show is many-to-one, Episode->Season
as well. Technically Episode->Show is also many-to-one. I can easily access
the show from an episode by doing episode.season.show, I could also define
a property (or even hybrid_property) to access the show. But I was
wondering if there is a way for SQLAlchemy to "understand" it. That is, the
same way as with the assoc. proxy in M2M.
I realize it cannot be *that* simple because what if I set a show on an
episode? Where does the season-link come in? So I *have* to provide some
information, e.g. the season number to create the intermediary object. But
the normal workflow would be to create Show -> Season -> Episode anyway.
The actual need is thus fairly limited in this example and so this is more
out of curiosity. All query reading and setting needs could be easily
accomplished by briding the attribute as described. And to fetch the show
with the season would also be easy using eager loading.
However, I'd really like to know if it is possible in such a manner that
SQLAlchemy *understands* that it's having a proxy here.
Regards,
Florian
--
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 wrote: > Is it safe, from within either the 'before_execute' or > 'before_cursor_execute' events, to use the same connection to execute a SQL > statement before the current one? I assume there is a good chance the answer > is no, at least for before_cursor_execute. if you’re in before_cursor_execute you have the actual DBAPI connection, and you can use that directly, and that’s totally safe. in before_execute(), you probably can use the Connection there as well but you’d need to be careful because you’re in a re-entrant situation, so your event handler would be called within. You can also use Connection.connection to get at the wrapped DBAPI connection where again it’s fine to use in before_execute as well. > Why? I only want to issue the SQL to update the database's session variables > if needed. Most connection checkout-checkin life cycles will only ever issue > SELECT statements and so don't need the database session updated for > auditing, so I was intending on waiting until I actually know it is needed > (from within before_cursor_execute) before issuing the > DBMS_SESSION.SET_CONTEXT(...). But, once I know that within > before_cursor_execute, can I (recursively) issue an conn.execute() for that > statement safely or will it affect the original execute? if you stick with the DBAPI connection directly then you’re definitely safe. > > > > On Saturday, March 7, 2015 at 6:38:08 PM UTC-5, Michael Bayer wrote: > > > Kent 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+...@googlegroups.com. > > To post to this group, send email to sqlal...@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. -- 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
Is it safe, from within either the 'before_execute' or 'before_cursor_execute' events, to use the same connection to execute a SQL statement before the current one? I assume there is a good chance the answer is no, at least for before_cursor_execute. Why? I only want to issue the SQL to update the database's session variables if needed. Most connection checkout-checkin life cycles will only ever issue SELECT statements and so don't need the database session updated for auditing, so I was intending on waiting until I actually know it is needed (from within before_cursor_execute) before issuing the DBMS_SESSION.SET_CONTEXT(...). But, once I know that within before_cursor_execute, can I (recursively) issue an conn.execute() for that statement safely or will it affect the original execute? On Saturday, March 7, 2015 at 6:38:08 PM UTC-5, Michael Bayer wrote: > > > > Kent > 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+...@googlegroups.com . > > To post to this group, send email to sqlal...@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.
Re: [sqlalchemy] Database session variables with connection pooling
Perfect, thanks much! On Saturday, March 7, 2015 at 6:38:08 PM UTC-5, Michael Bayer wrote: > > > > Kent > 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+...@googlegroups.com . > > To post to this group, send email to sqlal...@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.
