you can share multiple sessions in one transaction, sure, just bind the second one to the connection of the first: sess = Session(bind=my_other_session.connection()). Some notes on binding to transactions at http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites <http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites>. However I’m not sure what the use case for that is. Two sessions with *separate* transactions, sure.
> On Nov 13, 2014, at 12:53 AM, 翁哲 <wengc...@gmail.com> wrote: > > Thanks for your advice. > > What we want is a collection of all the selected even registrations for each > users. Certainly we can manipulate the process in you suggested way, with > adding an extra directory, may be list, to collect all the registrations. > > Besides, I see in Hibernate they have transaction and it can have multiple > sessions. Does SqlAlchemy has a similar sub-session technology, although I > haven't found out yet? If so I think it can solve this problem. > > Thanks. > > > On Thursday, November 13, 2014 1:54:48 AM UTC+11, Michael Bayer wrote: > > I would advise dealing with the EventRegistration objects separately: > > for user, registration in session.query(User, EventRegistration)….. > # process > > that way you don’t need to use contains_eager() and the rows you want are > explicitly present. Collections aren’t really meant for filtered querying > across many rows like that. > > > >> On Nov 12, 2014, at 12:30 AM, 翁哲 <weng...@ <>gmail.com <http://gmail.com/>> >> wrote: >> >> Hi Michael, >> >> Thanks for your promote reply. And I did some experiments on it and seems >> the contains_eager helps me get exactly what I want, however it will lose >> effect whenever I committed the current session. I guess this contains_eager >> is session related. And in my case, I need to commit/rollback within the >> same loop. For example: >> >> for db_user in session.query(User).join(User.br >> <http://user.br/>_event_registrations).filter(EventRegistrations.eventId < >> 10).all(): >> try: >> #here are some update process >> except Exception: >> session.rollback() >> else: >> session.commit() >> >> Any suggestions on this? Thanks! > >> >> >> On Wednesday, November 12, 2014 11:53:23 AM UTC+11, Michael Bayer wrote: >> >>> On Nov 11, 2014, at 5:37 PM, 翁哲 <weng...@gmail.com <>> wrote: >>> >>> Hi all, >>> >>> Recently I found an issue on using the back reference. Here a simple >>> scenario. >>> >>> Table user (with primary key userId) and table event_registration (with two >>> foreign keys, userid referencing the user table and eventId referencing the >>> event table, as primary key). And we define the relationship for this two >>> as fk_user on event_registration and br_event_registrations on user table. >>> >>> user_table = Table("user", metadata, >>> Column('userId', BigInteger, >>> Sequence('user_userId_seq'),primary_key=True), >>> ... >>> ) >>> >>> class User(object): >>> pass >>> >>> mapper(User, user_table, properties={}) >>> >>> event_registration_table = Table("event_registration", metadata, >>> Column('userId', BigInteger, ForeignKey('user.userId'), >>> primary_key=True), >>> Column('eventId', BigInteger, ForeignKey('event.eventId'), >>> primary_key=True), >>> ... >>> ) >>> >>> class EventRegistration(object): >>> pass >>> >>> mapper(EventRegistration, event_registration_table, properties={ >>> 'fk_user': relationship(User, backref='br_event_registrations'), >>> ... >>> }) >>> >>> >>> A common search query about getting the users who have registered a certain >>> or a series of events. For example: >>> for db_user in >>> session.query(User).join(User.br_event_registrations).filter(EventRegistrations.eventId >>> < 10).all(): >>> db_event_registration_list = db_user.br_event_registrations >>> >>> In the db_event_registration_list, I expect to get all the registrations >>> for a certain user and with eventId < 10. However what I actually get are >>> all the registrations related to this user. >>> >>> I have tried using the joinedload and joinedload_all, but it do not solve >>> the problem. >>> >>> I'm wondering if there are some flaws on this database schema or on the >>> fetching process itself. >> >> everything you do with Query as far as join(), filter(), etc. is only about >> the primary rows you’ve asked it to load, in this case, User rows. when >> you deal with some_user.br_event_registrations, that is the collection of >> all registrations associated with this user as defined by the relationship. >> >> If you’d like to affect the actual loading of those registrations at query >> time, you can apply the join() you have to the collection using >> contains_eager(). See >> http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#routing-explicit-joins-statements-into-eagerly-loaded-collection >> >> <http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#routing-explicit-joins-statements-into-eagerly-loaded-collection>. >> The collection for each User object should not be previously loaded >> already. >> >> >> >> >> >>> >>> Thanks in advance! >>> >>> -- >>> 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 <http://googlegroups.com/>. >>> To post to this group, send email to sqlal...@ <>googlegroups.com >>> <http://googlegroups.com/>. >>> Visit this group at http://groups.google.com/group/sqlalchemy >>> <http://groups.google.com/group/sqlalchemy>. >>> For more options, visit https://groups.google.com/d/optout >>> <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+...@googlegroups.com <>. >> To post to this group, send email to sqlal...@googlegroups.com <>. >> Visit this group at http://groups.google.com/group/sqlalchemy >> <http://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <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 > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at http://groups.google.com/group/sqlalchemy > <http://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <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.
