[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.month > myage -=1
> > elif adate.month==self.Birthdate.month:
> > if adate.day > myage -=1
> > return myage
> >
> >
> > # Mapping a Racer with a Race
> > tblRaceParticipant=Table("Race_Participant",
> > Column("id", Integer, primary_key = True),
&g
[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.month Michael Bayer wrote:
> if you are creating mappers within functions on a per-request basis,
> that is a Very Bad Idea. dont create ad-hoc mappers just to create
> queries. you should only have one mapper per class, corresponding to
> the scope of the class itself. i am beginning to consider if the
> "non_primary" keyword argument to mapper can even be deprecated since i
> am hypothesizing that it doesn't provide any unique functionality, and
> only leads to problems.
>
> if you want a particualr query to occur, you should create the query
> you want yourself. without seeing anything that you are doing, if you
> are having issues, things like that would be the biggest reason.
>
>
--~--~-~--~~~---~--~~
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] 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] compiled select
Hi list,
I think I came across a little problem I am still using 0.2.8 (+ flush
patch), so this may have been solved already...
in which case I apologise
When I do
s=tblRace.select(and_(dbRace.c.id==dbRaceLeg.c.id,dbRaceLeg.c.Date>=bindparam("Race_Date")))
lor=conn.execute((s,Race_Date=datetime.date.today())
I get the expected result...
But when I do
s=tblRace.select(and_(dbRace.c.id==dbRaceLeg.c.id,dbRaceLeg.c.Date>=bindparam("Race_Date"))).compile()
lor=conn.execute((s,Race_Date=datetime.date.today())
I get an exception
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py", line 246,
in execute
return Connection.executors[type(object).__mro__[-2]](self, object,
*multiparams, **params)
KeyError:
Is this a known/solved problem or am I doing something wrong?
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: session.flush() closing connection
Hi Michael, Thanks... Indeed it works... As long as I am careful and close the session after every flush (Something that make sense in the little application I am writing) several non concurrent threads can access and use the session. Cool Cheers, François > François Wautier wrote: > > I moved the "connect" to the method handling the data and the problem > > disappeared. > > yeah "connect()" should generally be localized as much as possible. > > > It would be easy enough to make sure all the "flushes" are done in a > > single thread of execution, but can you share a session across multiple > > thread if you ensure that anything dealing with connection (most methods > > of the session save maybe for "save") are executed by a single thread? > > IOW if you modify an object belonging to a session in a different thread, > > will the session notice? > > you sort of can, but you have to be careful, as any load operation also > writes data into the session. Load operations trigger not only when > you run a Query against that session, but also when any relations on > instances trigger a lazy load operation. if two threads each load an > instance with the same primary key identifier, they could compete for > which one actually gets placed in the session and the other one gets > knocked out. also a flush() is modifying the state of the underlying > unit of work which is also not going to well tolerate other instances > being loaded into that session. > > you might want to look into SessionContext which can manage Sessions > per thread, and also can be customized to manage Sessions based on > other criterion, such as for a GUI app on a "per-widget" basis,for > example. > > > --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: session.flush() closing connection
Hi Michael, Thanks for the answers. > also, when you say you "triggered" the problem, can you do that > predictably ? can you send a small test program that does it every > time ? otherwise it sounds like a threading thing (if thats at all > possible). > Very much so. Everytime I run my budding application I trigger the problem. >is there any chance the Connection youre using is being shared >between threads ? thats also not supported. Hmm Interresting question As you can see from the code snippet I sent previously, I am using PyQt. Hence that is multithreaded. I hadn't thought about it, but since the data processing is being run by some dynamically created widget, it is essentially run in a different thread. I moved the "connect" to the method handling the data and the problem disappeared. It would be easy enough to make sure all the "flushes" are done in a single thread of execution, but can you share a session across multiple thread if you ensure that anything dealing with connection (most methods of the session save maybe for "save") are executed by a single thread? IOW if you modify an object belonging to a session in a different thread, will the session notice? Regards, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: session.flush() closing connection
Hi Michael, Nope! Nothing of that sort. I couldn't care less about transaction at this point. All I do is self.dbEng=sqlalchemy.create_engine(myurl,strategy='threadlocal') #debug self.dbEng.echo=True try: self.dbConn=self.dbEng.connect() self.dbSession=sqlalchemy.create_session(bind_to= self.dbConn) except sqlalchemy.exceptions.DBAPIError,msg: QMessageBox.critical(None,"Race Management","Problem with the database: "+str(msg)) sys.exit(-1) After that all I do is create "dbPeople", set the needed properties and issue a "flush" on the one session I have. I did trigger the problem I described at least twice And once more just now). The behaviour was exactly the same in all cases. I installed SQLAlchemy using the standard Gentoo command (emerge), a md5sum on the file used in the install gives fd9898c75d2773d075db89c6a99d31d6 /usr/portage/distfiles/SQLAlchemy-0.2.8.tar.gz Regards, François > hey François - > > are you using the SessionTransaction explicitly ? or connection.begin > () ? Ive tried many combinations, and the only way I can reproduce > the problem is by doing something incorrect: > > c = engine.connect() > s = create_session(bind_to=c) > > tran = c.begin() > session_tran = s.create_transaction() > > s.save(User()) > s.flush() > u = User() > s.save(u) > s.user_name = 'some user' > > tran.commit() > session_tran.commit() > > the reason the above is incorrect is because the "Transaction" and > "SessionTransaction" are not nested properly. > > The fix you have wouldnt be correct since the SessionTransaction is > being closed (if not the underlying connection, which was the > original bug), so it should remove its association from its host > Session. > > On Oct 20, 2006, at 6:59 AM, François Wautier wrote: > > Hi Michael, > > > > It seems I spoke too quickly. > > > > The problem is now when I try to flush a second time with a new > > object. > > Something like this > > > > newguy=dbPeople() > > session.save(newguy) > > newguy.Lastname="Doe" > > newguy.Firstname="John" > > newguy.gender="Ambiguous" > > session.flush() > > newguy=dbPeople() > > session.save(newguy) > > newguy.Lastname="Doe" > > newguy.Firstname="Jane" > > newguy.gender="Sheila" > > session.flush() > > > > The last session flush results in a new record being written to the > > database, > > but an exception is raised, with the error message > > > > This transaction is inactive > > > > If one were to try to add more dbPeople, the records won't be saved > > into the > > database for the session keeps on using the same key value (the > > table uses > > an "auto_increment") > > > > > > I hacked the code a bit and I solved the problem but I am far > > from sure > > that I did the right thing for all cases > > > > In lib/sqlalchemy/orm/session.py around line 67 I changed > > > > for t in self.connections.values(): > > if (t[2]): > > t[0].close() > > self.session.transaction = None > > > > into > > keeptransaction=False > > for t in self.connections.values(): > > if (t[2]): > > t[0].close() > > else: > > keeptransaction=True > > if not keeptransaction=False: > > self.session.transaction = None > > > > I wonder if something like this would not be preferable (but I > > again, I have > > no clue as to what the consequences of my code is) > > > > closeall=False > > for t in self.connections.values(): > > if (t[2]): > > closeall=True > > if closeall: > > for t in self.connections.values(): > > t[0].close() > > self.session.transaction = None > > > > > > Regards, > > 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: session.flush() closing connection
Hi Michael, It seems I spoke too quickly. The problem is now when I try to flush a second time with a new object. Something like this newguy=dbPeople() session.save(newguy) newguy.Lastname="Doe" newguy.Firstname="John" newguy.gender="Ambiguous" session.flush() newguy=dbPeople() session.save(newguy) newguy.Lastname="Doe" newguy.Firstname="Jane" newguy.gender="Sheila" session.flush() The last session flush results in a new record being written to the database, but an exception is raised, with the error message This transaction is inactive If one were to try to add more dbPeople, the records won't be saved into the database for the session keeps on using the same key value (the table uses an "auto_increment") I hacked the code a bit and I solved the problem but I am far from sure that I did the right thing for all cases In lib/sqlalchemy/orm/session.py around line 67 I changed for t in self.connections.values(): if (t[2]): t[0].close() self.session.transaction = None into keeptransaction=False for t in self.connections.values(): if (t[2]): t[0].close() else: keeptransaction=True if not keeptransaction=False: self.session.transaction = None I wonder if something like this would not be preferable (but I again, I have no clue as to what the consequences of my code is) closeall=False for t in self.connections.values(): if (t[2]): closeall=True if closeall: for t in self.connections.values(): t[0].close() self.session.transaction = None Regards, 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 -~--~~~~--~~--~--~---
[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 -~--~~~~--~~--~--~---
[sqlalchemy] session.flush() and closed connection + 1 other questions
Hi,
This is my first posting to this list as I am new to SQLAlchemy , so let me
express
my gratitude to those who develop SQLAlchemy... It's absolutely fabulous... The
ORM in
particular is fantastic!
I am using version 0.2.8 with Python 2.4.3 and MySQL 4.1.21 on an up-to-date
Linux Gentoo box
Session Flush Problem
=
I am having a problem with session.flush(). It seems that every time I issue a
session flush the
DB connection is closed. If I do something like this
eng = create_engine('mysql://test:[EMAIL
PROTECTED]/test',strategy='threadlocal')
conn=eng.connect()
session = create_session(bind_to=conn)
query=session.query(dbPeople) #This step needed for "compiling" the
class/mapper or something like that
query=query.select_by_Lastname
listofpeople=query("Doe")
oneguy=listofpeople[0]
oneguy.Country="Namibia"
session.flush()
listofpeople=query("Smith")
The flush works alright and the database is updated, but the last line result
in an error message:
sqlalchemy.exceptions.InvalidRequestError: This Connection is closed
Is that the normal behaviour? I would have expected the session to query the DB
and return a new list
of dbPeople adding them to its list of "persistent" object. Am I doing
something wrong? Misunderstanding
something?
Is this or something similar possible?
===
Given a dbPeople class, here is what I'd like to do
wherep=dbPeople()
wherep.Lastname="Smith"
wherep.Country="United Kingdom"
listofsmith=session.query(dbPeople).select_by(wherep)
listofsmithbosses=session.query(dbPeople).select_by(Manager=wherep)
Given the right table, classes and mapper definition (omitted here, in the
above example, Manager could be
defined as a "backref") the first query would produce an SQL "WHERE" clause like
"WHERE Lastname="Smith" AND Country="United Kingdom"
The list of dbPeople named "Smith" in the UK
Whilst the second would produce a query like
SELECT FROM People AS Employee, People
AS Manager, ManagerRel
WHERE Employee.id=ManagerRel.Employee AND
Manager.id=ManagerRel.Manager AND
Manager.Lastname="Smith" AND Manager.Country="United Kingdom"
The list of dbPeople whose manager is a "Smith" in the UK
Essentially, the (transient) object would keep track of which properties were
set (including set to None) and use
those to construct the WHERE clause. Probably easier said then done The
case of "Pending" instances should
be quite complex ( and in some case would not make any sense)
Best Regards,
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
-~--~~~~--~~--~--~---
