[sqlalchemy] Re: Thread issue?
Thanks for your reply, OK, i had the impression you were switching the mapper inside of a relation() somehow, but it seems all youre doing is sticking a mapper on a property (i dont quite understand how youd use it ? ) I want to use it like this fixRace(dbPeople, myrace) listofgreeks=session.query(dbPeople.inRace).select_by(Nationality=Greece) listofresident=session.query(dbPeople.inRace).select_by(Country=Germany) So I need to have the inRace mapper an attribute of the class itself. I'll stick to what I have right now, it seems to work fine. Cheers, François if i understand properly, id just do it this way: class dbPeople(object): def fixRace(self, race): self._race_id = race.id def _in_race(self): return object_session(self).query(dbRace).select( and_(tblRaceParticipant.c.Race_id==self._race_id,tblPeople.c.id==tblRacePar ticipant.c.Racer_id)) inRace = property(_in_race) François Wautier wrote: Hi Michael, Thanks for your reply. First an apology, my program is now working It was a silly mistake... Second, I agree that what I am doing is not the most elegant thing I've ever done... .to put it mildly... Yet, in most cases, the fixRace function will only be run once at startup . In all but one case you only deal with one race. So that's not as bad as it sounds... Still the application that input data into the database does need to deal with multiple races. In that case I only keep one secondary mapper attached to the class and only create a new one when needed (i.e. when the race changes) (I probably need to delete the old mapper if present) At the bottom you will see the actual definition/mapping I use. I guess that I may be able to map the various attributes of dbPeople and dbRace to attributes of dbRaceParticipant and deal with that object when needed, but I still see no elegant way of fixing the race, either I create a secondary mapper (same as now essentially) or I need to pass the race as an argument to all my queries... which is exactly what I am trying to avoid. Cheers, François Here is an excerpt of my definitions draft in progress =+%% tblPeople=Table(People, Column(id, Integer, primary_key = True), Column(Nickname,Unicode(32),nullable=False,index=True), Column(Firstname,Unicode(32),nullable=False), Column(Lastname,Unicode(32),nullable=False,index=True), Column(Email,VARCHAR(64),index=True), Column(Birthdate,Date), Column(Gender,Enum([Male,Female]),nullable=False), Column(Nationality,Country,nullable=False), Column(Address,Unicode(256)), Column(Zip,Unicode(32)), Column(Country,Country), Column(Tel,String(16)), Column(Tel_Ext,String(4)), Column(Mobile,String(16)), Column(Picture_id,Integer,ForeignKey(ADM_Files.id), nullable=True), Column(Tag,String(32)), Column(Active,Boolean,default=True)) tbbPeopleidx=Index('OnlyOne', tblPeople.c.Nickname,tblPeople.c.Firstname, tblPeople.c.Lastname, unique=True) class dbPeople(object): def __str__(self): return self.Fullname() def Fullname(self): return unicode(self.Firstname)+u +unicode(self.Lastname) def age(self,adate=None): Compute the age of a person. If adate is set the age is computed at the given date if adate is None: adate=datetime.date.today() myage=adate.year-self.Birthdate.year if adate.monthself.Birthdate.month: myage -=1 elif adate.month==self.Birthdate.month: if adate.dayself.Birthdate.day: myage -=1 return myage # Mapping a Racer with a Race tblRaceParticipant=Table(Race_Participant, Column(id, Integer, primary_key = True), Column(Race_id,Integer,ForeignKey(Race.id), nullable=False), Column(Racer_id,Integer,ForeignKey('People.id'), nullable=False), Column(Team_id,Integer,ForeignKey('Team.id'), nullable=True), Column(Weight,DECIMAL(4,1), nullable=True), Column(Age,Integer, nullable=True), Column(Height,Integer, nullable=True), #Column(Categories,String, nullable=True), Column(isActive,Boolean, nullable=False,default=True), Column(Retired,TIMESTAMP, nullable=True), Column(Comment,Unicode)) tbbParticipantidx=Index('OnlyOne', tblRaceParticipant.c.Race_id,tblRaceParticipant.c.Racer_id, unique=True) class dbRaceParticipant(object): def __str__(self): return str(self.Racer)+u during +str(self.Race) #Defining races tblRace=Table(Race, Column(id, Integer, primary_key = True), Column(Name,Unicode(64),nullable=False), Column(Type,Enum([Team,Individual]),nullable=False), Column(Vehicle,Enum([None,One,Multiple,Individual]),nullable=Fa lse,default=One
[sqlalchemy] Thread issue?
Hi List, I have a table of people, one of Races and a RaceParticipant table that relate people to races and add a few extra info (e.g. Team, Weight, ...) All this is working fine and thanks to the power of the ORM, I can do things like race.Racers people.Races and get the right list. Now I am trying to fix the race and try to retrieve only the people participating to the race. What I have done is a create a function fixRace. It takes a race as parameter and what it does is create a secondary mapper that is based on a query where the join is set properly. The mapper is then put in the inRace attribute of the people object. My intention being to do things like session.query(people.inRace).select_by(Nationality=Sweden) I did a test using the shell and it seemed to work... but in my Qt application... It does not... my questions are Event though my session is not flushed and only one thread at a time will access the session, could this be a threading problem? Is there a better way to do what I am trying to do? Cheers, François --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Thread issue?
Hi Michael, Thanks for your reply. First an apology, my program is now working It was a silly mistake... Second, I agree that what I am doing is not the most elegant thing I've ever done... .to put it mildly... Yet, in most cases, the fixRace function will only be run once at startup . In all but one case you only deal with one race. So that's not as bad as it sounds... Still the application that input data into the database does need to deal with multiple races. In that case I only keep one secondary mapper attached to the class and only create a new one when needed (i.e. when the race changes) (I probably need to delete the old mapper if present) At the bottom you will see the actual definition/mapping I use. I guess that I may be able to map the various attributes of dbPeople and dbRace to attributes of dbRaceParticipant and deal with that object when needed, but I still see no elegant way of fixing the race, either I create a secondary mapper (same as now essentially) or I need to pass the race as an argument to all my queries... which is exactly what I am trying to avoid. Cheers, François Here is an excerpt of my definitions draft in progress =+%% tblPeople=Table(People, Column(id, Integer, primary_key = True), Column(Nickname,Unicode(32),nullable=False,index=True), Column(Firstname,Unicode(32),nullable=False), Column(Lastname,Unicode(32),nullable=False,index=True), Column(Email,VARCHAR(64),index=True), Column(Birthdate,Date), Column(Gender,Enum([Male,Female]),nullable=False), Column(Nationality,Country,nullable=False), Column(Address,Unicode(256)), Column(Zip,Unicode(32)), Column(Country,Country), Column(Tel,String(16)), Column(Tel_Ext,String(4)), Column(Mobile,String(16)), Column(Picture_id,Integer,ForeignKey(ADM_Files.id), nullable=True), Column(Tag,String(32)), Column(Active,Boolean,default=True)) tbbPeopleidx=Index('OnlyOne', tblPeople.c.Nickname,tblPeople.c.Firstname, tblPeople.c.Lastname, unique=True) class dbPeople(object): def __str__(self): return self.Fullname() def Fullname(self): return unicode(self.Firstname)+u +unicode(self.Lastname) def age(self,adate=None): Compute the age of a person. If adate is set the age is computed at the given date if adate is None: adate=datetime.date.today() myage=adate.year-self.Birthdate.year if adate.monthself.Birthdate.month: myage -=1 elif adate.month==self.Birthdate.month: if adate.dayself.Birthdate.day: myage -=1 return myage # Mapping a Racer with a Race tblRaceParticipant=Table(Race_Participant, Column(id, Integer, primary_key = True), Column(Race_id,Integer,ForeignKey(Race.id), nullable=False), Column(Racer_id,Integer,ForeignKey('People.id'), nullable=False), Column(Team_id,Integer,ForeignKey('Team.id'), nullable=True), Column(Weight,DECIMAL(4,1), nullable=True), Column(Age,Integer, nullable=True), Column(Height,Integer, nullable=True), #Column(Categories,String, nullable=True), Column(isActive,Boolean, nullable=False,default=True), Column(Retired,TIMESTAMP, nullable=True), Column(Comment,Unicode)) tbbParticipantidx=Index('OnlyOne', tblRaceParticipant.c.Race_id,tblRaceParticipant.c.Racer_id, unique=True) class dbRaceParticipant(object): def __str__(self): return str(self.Racer)+u during +str(self.Race) #Defining races tblRace=Table(Race, Column(id, Integer, primary_key = True), Column(Name,Unicode(64),nullable=False), Column(Type,Enum([Team,Individual]),nullable=False), Column(Vehicle,Enum([None,One,Multiple,Individual]),nullable=False,default=One), Column(Organiser_id,Integer,ForeignKey('People.id'),nullable=True), Column(Description,Unicode), Column(Logo_id,Integer,ForeignKey(ADM_Files.id), nullable=True), Column(Standing,Unicode(32))) class dbRace(object): def __str__(self): return self.Name def getRegistrationRecord(self,people): There must be a session here for rec in self.Registration: if rec.Racer==people: return rec # dbRaceParticipant.mapper=mapper(dbRaceParticipant, tblRaceParticipant, properties = { 'Races' : relation(dbRace, backref=backref('Registration')), 'Racer' : relation(dbPeople, backref=backref('Registration')), Team:relation(dbTeam )}) # Organiser with back reference dbRace.mapper=mapper(dbRace, tblRace, properties = { 'Organiser' : relation(dbPeople, backref=backref('Organise')), Racers:relation(dbPeople,secondary=tblRaceParticipant, primaryjoin=tblRace.c.id==tblRaceParticipant.c.Race_id, secondaryjoin=tblPeople.c.id==tblRaceParticipant.c.Racer_id),
[sqlalchemy] Re: session.flush() closing connection
Thanks for the patch... it works... so far And sorry for the double post... my original email was held for a very long time on some google host Cheers, François thats a bug. its because the flush() is closing the connection you passed to your session. heres a patch that fixes it, which i will try to commit later today but i want to work up some test cases: Index: lib/sqlalchemy/orm/session.py === --- lib/sqlalchemy/orm/session.py (revision 1852) +++ lib/sqlalchemy/orm/session.py (working copy) @@ -37,7 +37,7 @@ e = connectable.engine c = connectable.contextual_connect() if not self.connections.has_key(e): -self.connections[e] = (c, c.begin()) +self.connections[e] = (c, c.begin(), c is not connectable) return self.connections[e][0] def commit(self): if self.parent is not None: @@ -58,7 +58,8 @@ if self.parent is not None: return for t in self.connections.values(): -t[0].close() +if (t[2]): +t[0].close() self.session.transaction = None class Session(object): --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---