[sqlalchemy] Re: IN() bug bit hard
On Apr 13, 2007, at 4:17 PM, Ants Aasma wrote: >> really, the work to do here is 5% the patch to the in_() method, and >> 95% making me a really nice unit test suite that will generatively >> test IN for every contingencyincluding the bind param stuff in >> #476 (but simpler code than whats patched there). otherwise its >> giong to sit in the queue for awhile since i still have a lot of >> other more pressing patches i havent yet had the time to test/apply. > > I'll try to create a test suite for it next week, I have already > identified most of the test cases. Should I attach it to #476 or > create a new ticket? lets just go with #476, thanks for the help ! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: weird ORACLE behaviour with limit/offset
On Apr 13, 2007, at 3:43 PM, vkuznet wrote: > > Hi, > I added to my query the limit and offset (using ORACLE). To my > surprise results ARE varying if I'll print my select or not before > executing query. What I mean is the following > > sel = select () > #print sel > sel.execute() > > so, if I will not print my select, I'll get *smaller* number of > results wrt if I'll uncomment my print statement. > As I said this behaviour only appears if I add limit/offset to my > select query using ORACLE. The query is quite complex, but here is > what I see when I enable print statement: this is a bug and is fixed in r2498. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
#476 - forget the visitor stuff; i use the simpler code that is in the text of "02/15/07 09:59:22 changed by svil" comment, for some 2 months now, and all is ok. i've added the patch/code to that ticket as well as to #474/475. On Friday 13 April 2007 23:17:37 Ants Aasma wrote: > On Apr 13, 10:16 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > > OK, first thing i wasnt sure about, is CASE supported on every DB > > that we support. I took a look at our CASE unit test and it > > appears it applies to all DBs...(although i cant verify it passes > > on firebird). > > Works on Firebird 1.5.3. I haven't verified older MySQL and > PostreSQL releases, but I'll try to verify those too, or at least > check changelogs for related changes. > > > really, the work to do here is 5% the patch to the in_() method, > > and 95% making me a really nice unit test suite that will > > generatively test IN for every contingencyincluding the bind > > param stuff in #476 (but simpler code than whats patched there). > > otherwise its giong to sit in the queue for awhile since i still > > have a lot of other more pressing patches i havent yet had the > > time to test/apply. > > I'll try to create a test suite for it next week, I have already > identified most of the test cases. Should I attach it to #476 or > create a new ticket? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 13, 10:16 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > OK, first thing i wasnt sure about, is CASE supported on every DB > that we support. I took a look at our CASE unit test and it appears > it applies to all DBs...(although i cant verify it passes on > firebird). Works on Firebird 1.5.3. I haven't verified older MySQL and PostreSQL releases, but I'll try to verify those too, or at least check changelogs for related changes. > really, the work to do here is 5% the patch to the in_() method, and > 95% making me a really nice unit test suite that will generatively > test IN for every contingencyincluding the bind param stuff in > #476 (but simpler code than whats patched there). otherwise its > giong to sit in the queue for awhile since i still have a lot of > other more pressing patches i havent yet had the time to test/apply. I'll try to create a test suite for it next week, I have already identified most of the test cases. Should I attach it to #476 or create a new ticket? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: [PATCH] Filtered one_to_many relationships (Experimental)
> we arent writing to anything here, this is strictly a read-only > function. if you want to modify the collection, you create a real > relation() separately. the controversy was over should the > relation () and the generative loader be mixed (which I think they > should not, even though its slightly more verbose...surprise). > > > i want to somehow bundle these (read/write aspects) in one thing, > > e.g. called "specialized_relation", and not having to separately > > think about intermediate objects/queries etc. > > yes i am not comfortable with combining them as Gaetan suggested > since i think it conflates two semantic purposes in one attribute, > one purpose being the management of data in the database, the other > being a view. in particular you really cant have your partial > loaded list actually populate the underlying collection since it > will only be partially populated and ruin the semantics of what it > means to be a "relation". Think of the "relation" in your sematics like a class XX. The particular subset (for some obj on some side) - my spec_relation - is like an instance of that XX class. Objects in the subset belong to the superset by definition. Adding to the subset actualy adds to the general thing - assuming validation is done - or forced, e.g. if subsetting is by-template). i think such pattern can be invented and wrapped in something real, e.g. a class, subclassing which will contain and able to declare/create relations, and instantiating the subclass will create temporary relation-subsets by some criteria. let me see if i can do it... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] weird ORACLE behaviour with limit/offset
Hi, I added to my query the limit and offset (using ORACLE). To my surprise results ARE varying if I'll print my select or not before executing query. What I mean is the following sel = select () #print sel sel.execute() so, if I will not print my select, I'll get *smaller* number of results wrt if I'll uncomment my print statement. As I said this behaviour only appears if I add limit/offset to my select query using ORACLE. The query is quite complex, but here is what I see when I enable print statement: SELECT tblk_path FROM (SELECT DISTINCT tblk.path AS tblk_path, ROW_NUMBER() OVER (ORDER BY tblk.path DESC) AS ora_rn FROM processeddataset tprd LEFT OUTER JOIN block tblk ON tblk.dataset = tprd.id LEFT OUTER JOIN procdstier tpds ON tpds.dataset = tprd.id LEFT OUTER JOIN primarydataset tpm ON tprd.primarydataset = tpm.id LEFT OUTER JOIN seblock tseb ON tseb.blockid = tblk.id LEFT OUTER JOIN storageelement tse ON tseb.seid = tse.id LEFT OUTER JOIN procalgo tpal ON tpal.dataset = tprd.id LEFT OUTER JOIN algorithmconfig talc ON tpal.algorithm = talc.id LEFT OUTER JOIN appexecutable tape ON talc.executablename = tape.id LEFT OUTER JOIN appversion tapv ON talc.applicationversion = tapv.id LEFT OUTER JOIN appfamily tapf ON talc.applicationfamily = tapf.id LEFT OUTER JOIN physicsgroup tpg ON tprd.physicsgroup = tpg.id WHERE tpg.physicsgroupname = 0) WHERE ora_rn>0 AND ora_rn<=25 Any ideas??? Thanks, Valentin. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: [PATCH] Filtered one_to_many relationships (Experimental)
On Apr 13, 2007, at 3:10 PM, [EMAIL PROTECTED] wrote: >> >> then: >> >> m = session.query(MyClass).get(1) >> somechildren = m.children[3:5] >> someotherchildren = m.children.filter_by(foo='bar').filter_by >> (x==y).list() >> > hm, that seems like a way... more or less what i've done so far, on a > different level though - i have done it completely separate. > > how to avoid loading the whole thing at any time? Query is a generative object now. the return value hasnt loaded anything until you call list(), select(), __iter__(), or a range function on it. > or there is no > relation() as such? with_parent assumes some relation. yes with_parent assumes the relation, otherwise you have to spell out the join criterion manually. > and how to add new links - manualy? or append to the otherside? then > how to re-sync the list - items can be added on other machines/ > threads...? we arent writing to anything here, this is strictly a read-only function. if you want to modify the collection, you create a real relation() separately. the controversy was over should the relation () and the generative loader be mixed (which I think they should not, even though its slightly more verbose...surprise). > > i want to somehow bundle these (read/write aspects) in one thing, e.g. > called "specialized_relation", and not having to separately think > about intermediate objects/queries etc. yes i am not comfortable with combining them as Gaetan suggested since i think it conflates two semantic purposes in one attribute, one purpose being the management of data in the database, the other being a view. in particular you really cant have your partial loaded list actually populate the underlying collection since it will only be partially populated and ruin the semantics of what it means to be a "relation". --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 13, 2007, at 2:37 PM, Ants Aasma wrote: > > On Apr 13, 6:40 pm, "Rick" <[EMAIL PROTECTED]> wrote: >> Sorry, it looks like is already being discussed. (Serve me right for >> reading in threaded mode.) From my SA-newbie POV, I'd love it if >> col.in_() compiled down to false or 0 (whatever works). > > col.in_() is easy to get working correctly, the problem is > not_(col.in_()). In order to be consistent with regular IN behaviour > this should only return rows with non null values. By example of mysql > (because it has the nicest display, others work the same): > mysql> SELECT null IN (1,2,3), 0 IN (1,2,3), NOT null IN (1,2,3), NOT > 0 IN (1,2,3)\G > *** 1. row *** > null IN (1,2,3): NULL >0 IN (1,2,3): 0 > NOT null IN (1,2,3): NULL >NOT 0 IN (1,2,3): 1 > > To be locally consistent with this behaviour the expression should > return null when col is null, else return false. The case statement > mentioned before is actually the most straightforward encoding of this > behaviour. It is big only due to verbosity of SQL syntax. In Python it > would be "None if col is None else False". OK, first thing i wasnt sure about, is CASE supported on every DB that we support. I took a look at our CASE unit test and it appears it applies to all DBs...(although i cant verify it passes on firebird). while it goes slightly against my desire for SA to remain primarily an extremely thin layer over SQL for something as small as foo.in_() to explode into a CASE like that, i think your support for the feature is much stronger than my antsiness about it so i will support it, considering the alternate behavior is just to throw an error. really, the work to do here is 5% the patch to the in_() method, and 95% making me a really nice unit test suite that will generatively test IN for every contingencyincluding the bind param stuff in #476 (but simpler code than whats patched there). otherwise its giong to sit in the queue for awhile since i still have a lot of other more pressing patches i havent yet had the time to test/apply. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: [PATCH] Filtered one_to_many relationships (Experimental)
On Friday 13 April 2007 21:20:18 Michael Bayer wrote: > On Apr 13, 2007, at 12:51 PM, [EMAIL PROTECTED] wrote: > > haven't thought yet of where/how to hack this... > > i may have to abandon *-to-many-relations() alltogether, as i > > don't want/need them loaded - only filtered at view-time. > > or can i make some super- (or sub-) relation thing > > (propertyLoader?) that does the above? > > I currently do it using a property that returns a Query object, > which works very well. the extra method "with_parent()" i want to > add to Query would make it easier to implement also. > > > class MyClass(object): > def _get_children(self): > return object_session(self).query > (SomeChildClass).with_parent(self) > children = property(_get_children) > > > then: > > m = session.query(MyClass).get(1) > somechildren = m.children[3:5] > someotherchildren = m.children.filter_by(foo='bar').filter_by > (x==y).list() > hm, that seems like a way... more or less what i've done so far, on a different level though - i have done it completely separate. how to avoid loading the whole thing at any time? or there is no relation() as such? with_parent assumes some relation. and how to add new links - manualy? or append to the otherside? then how to re-sync the list - items can be added on other machines/ threads...? i want to somehow bundle these (read/write aspects) in one thing, e.g. called "specialized_relation", and not having to separately think about intermediate objects/queries etc. It's like a more specialized "instance" of current (class-wide) relation() definition. e.g. something that is created off obj.class.some_relation, and it's obj.id (and other subfilters). The whole-relation thing is like a "class", and the subfiltered thing is like an "instance". --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 13, 6:40 pm, "Rick" <[EMAIL PROTECTED]> wrote: > Sorry, it looks like is already being discussed. (Serve me right for > reading in threaded mode.) From my SA-newbie POV, I'd love it if > col.in_() compiled down to false or 0 (whatever works). col.in_() is easy to get working correctly, the problem is not_(col.in_()). In order to be consistent with regular IN behaviour this should only return rows with non null values. By example of mysql (because it has the nicest display, others work the same): mysql> SELECT null IN (1,2,3), 0 IN (1,2,3), NOT null IN (1,2,3), NOT 0 IN (1,2,3)\G *** 1. row *** null IN (1,2,3): NULL 0 IN (1,2,3): 0 NOT null IN (1,2,3): NULL NOT 0 IN (1,2,3): 1 To be locally consistent with this behaviour the expression should return null when col is null, else return false. The case statement mentioned before is actually the most straightforward encoding of this behaviour. It is big only due to verbosity of SQL syntax. In Python it would be "None if col is None else False". --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Multiple inheritance and Backrefs
Figured it out on my own :-)
You need the following for a mapper:
...
properties = { ...
'forks':relation(Fork,
secondary=UtensileTable,
primaryjoin=PlaceSetting.c.id==UtensileTable.c.placeSettingID,
secondaryjoin=UtensileTable.c.id==ForkTable.c.id,
backref=backref("placeSetting",
remote_side=[UtensileTable.c.id]),
)
...
enjoy
-percious
On Apr 13, 11:51 am, "percious" <[EMAIL PROTECTED]> wrote:
> I am trying to do something like this:
>
> PlaceSettingTable = Table("place_setting", metadata,
> Column('id', Integer, primary_key=True)
> )
>
> UtensileTable = Table("utensile", metadata,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(256)),
> Column('placeSettingID', Integer,
> ForeignKey('place_setting.id'))
> )
>
> ForkTable = Table("fork", metadata,
> Column('id', Integer, ForeignKey('utensile.id'),
> primary_key=True),
> Column('numberOfTines', Integer),
> )
> SpoonTable = Table("spoon", metadata,
> Column('id', Integer, ForeignKey('utensile.id'),
> primary_key=True),
> )
> KnifeTable = Table("knife", metadata,
> Column('id', Integer, ForeignKey('utensile.id'),
> primary_key=True),
> )
>
> class PlaceSetting(object):pass
> class Utensile(object):pass
> class Fork(object):pass
> class Knife(object):pass
> class Spoon(object):pass
>
> utensileMapper = mapper(Utensile, UtensileTable)
> forkMapper = mapper(Fork, ForkTable, inherits=utensileMapper)
>
> placeSettingMapper = mapper(PlaceSetting, PlaceSettingTable,
> properties={'forks':relation(Fork,
>
> primaryjoin=PlaceSettingTableTable.c.id==Fork.c.placeSettingID,
>
> backref=backref("placeSetting", remote_side=[UtensileTable.c.id]))
> }
> )
>
> unsuccessfully.
>
> Anyone have any ideas?
>
> -chris
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: count and distinct
you know i was going to say "hey we should add a distinct() function" but then i checked and its already there: select([func.count(table.c.column.distinct())]).execute() this method is actually on _CompareMixin and I should look into getting it into the generated docs somehow. also i think a standalone distinct() would be helpful. On Apr 13, 2007, at 1:34 PM, vkuznet wrote: > > Hi, > I found that if I do > select([func.count(table.c.column)],distinct=True).execute() > > the resulting query is > select distinct count(column) from table > > but it's not what I wanted. If my column has duplicates you got > counting them, rather then count unique names. The proper SQL query > would be > > select count(distinct column) from table > > How I can make it with SQLAlchemy syntax? > > Thanks, > Valentin. > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: [PATCH] Filtered one_to_many relationships (Experimental)
On Apr 13, 2007, at 12:51 PM, [EMAIL PROTECTED] wrote: > haven't thought yet of where/how to hack this... > i may have to abandon *-to-many-relations() alltogether, as i don't > want/need them loaded - only filtered at view-time. > or can i make some super- (or sub-) relation thing (propertyLoader?) > that does the above? I currently do it using a property that returns a Query object, which works very well. the extra method "with_parent()" i want to add to Query would make it easier to implement also. class MyClass(object): def _get_children(self): return object_session(self).query (SomeChildClass).with_parent(self) children = property(_get_children) then: m = session.query(MyClass).get(1) somechildren = m.children[3:5] someotherchildren = m.children.filter_by(foo='bar').filter_by (x==y).list() --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] count and distinct
Hi, I found that if I do select([func.count(table.c.column)],distinct=True).execute() the resulting query is select distinct count(column) from table but it's not what I wanted. If my column has duplicates you got counting them, rather then count unique names. The proper SQL query would be select count(distinct column) from table How I can make it with SQLAlchemy syntax? Thanks, Valentin. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
On Apr 13, 2007, at 11:40 AM, Rick wrote: > > Sorry, it looks like is already being discussed. (Serve me right for > reading in threaded mode.) From my SA-newbie POV, I'd love it if > col.in_() compiled down to false or 0 (whatever works). sure...but I was just saying, i get much more antsy if to create a "false" we have to pull out a big CASE statement..that feels wrong to me. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: [PATCH] Filtered one_to_many relationships (Experimental)
i have some heretical thought about this...
my context is: i have a many2many relation, which is always used as a
one to many for one particular item; i.e. an additionaly filtered
many2many by a runtime argument. e.g. imagine those users <->
addresses, but always used as "addresses for a particular user", and
not used backwards.
so... there is a relation that contains vast number of items, but only
a subset of that is visible/used, at any certain scope of usage.
so i thought that a similar pattern is already there with the mappers:
u have a mapper over whatever local-table, but when used for
selection, it uses another selectable instead (maybe a
subset/superset of the localtable).
Thus said, can i define a relation which has the usual primaryjoin +
whatever full-description, and has some additional runtime filtering
criteria - or replacement primaryjoin - or whole replacement query -
to be used for viewing/reading (once initialised, and fallback to
query-everything when no filter)
haven't thought yet of where/how to hack this...
i may have to abandon *-to-many-relations() alltogether, as i don't
want/need them loaded - only filtered at view-time.
or can i make some super- (or sub-) relation thing (propertyLoader?)
that does the above?
Any opinions?
svilen
On Wednesday 11 April 2007 22:36:43 Michael Bayer wrote:
> On Apr 11, 2007, at 4:50 AM, Gaetan de Menten wrote:
> > looks better, is shorter and is more readable than:
> >
> >Query('Address', instance=someuser, attr_name='addresses')
>
> Ive no problem with the classmethod being present and i dont want
> people using Query's constructor in general.
>
> > And second, I think it could be quite confusing for a user.
> > Imagine that Query(A).from_attr(inst, 'rel') could return
> > instances of B (or whatever class is attached to the relation)...
> > Probably not what you'd expect.
>
> that should raise an exception, if the given instance/relationship
> pair does not match the query's main entity. theres also the
> possibility that we could "add" the entity in using add_entity()
> but lets not go there for now.
>
> generally, any method on Query which generates a SQL clause is
> potentially useful in a generative sense...since any SQL clause is
> always useful as part of a larger SQL clause.
>
> > And by the way, from_attr doesn't sound like a generative method,
> > so if you want it, what about "filter_from_attr", or something
> > similar?
>
> what it really is, is like "with_parent" (to paraphrase an
> SQLElixer term). using the standard User->Address relation, I say
> session.query(Address).with_parent(someuser, relation='addresses').
> additionally, the "addresses" string which marks the name of the
> attribute on User that points to Address should be optional, if it
> can be automatically determined from the existing relationships
> between User and Address. (if there is *any* ambiguity though,
> like two relations on user that point to address, it should raise
> an error). So people can say (as I myself find myself wanting to
> say), session.query(Address).with_parent(someuser).filter_by
> (email='[EMAIL PROTECTED]'). right now I have mentioned the "trick" that
> uses backrefs to get the same effect, this is more convenient since
> it isnt forcing a backref to exist just to generate a certain
> query.
>
>
>
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: threading.currentThread().session = create_session() ??
On Apr 13, 2007, at 10:49 AM, Arun Kumar PG wrote: > Hi All, > > I have a web application where I have multiple DAOs that may be > called during phases within a request. Since I want to make sure > that I get the same session with every DAO I was wondering if I can > add a runtime session attribute to the current thread returned > Python's threading.currentThread() as soon as a request hits the > application. > absolutely. or if youre on python 2.4, threading.local() is easier. > I will be happy if someone can share approach for using > SessionContext in case of a web application so that every request > is associated with the same session/ thats what it does, you can check the docs / read the source or maybe someone will have some more detail about how they use it (its also used in Pylons by default). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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] Multiple inheritance and Backrefs
I am trying to do something like this:
PlaceSettingTable = Table("place_setting", metadata,
Column('id', Integer, primary_key=True)
)
UtensileTable = Table("utensile", metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(256)),
Column('placeSettingID', Integer,
ForeignKey('place_setting.id'))
)
ForkTable = Table("fork", metadata,
Column('id', Integer, ForeignKey('utensile.id'),
primary_key=True),
Column('numberOfTines', Integer),
)
SpoonTable = Table("spoon", metadata,
Column('id', Integer, ForeignKey('utensile.id'),
primary_key=True),
)
KnifeTable = Table("knife", metadata,
Column('id', Integer, ForeignKey('utensile.id'),
primary_key=True),
)
class PlaceSetting(object):pass
class Utensile(object):pass
class Fork(object):pass
class Knife(object):pass
class Spoon(object):pass
utensileMapper = mapper(Utensile, UtensileTable)
forkMapper = mapper(Fork, ForkTable, inherits=utensileMapper)
placeSettingMapper = mapper(PlaceSetting, PlaceSettingTable,
properties={'forks':relation(Fork,
primaryjoin=PlaceSettingTableTable.c.id==Fork.c.placeSettingID,
backref=backref("placeSetting", remote_side=[UtensileTable.c.id]))
}
)
unsuccessfully.
Anyone have any ideas?
-chris
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: Many to many optimization
On Apr 12, 2007, at 11:38 PM, Kaali wrote: > > I actually tried to use query.instances, but it behaved quite oddly. I > didn't debug or even echo the SQL calls yet, but it made accessing > those instances very slow. The actual instances call was quick, but > when accessing the objects from the resulting list it slowed down to > crawl. > make sure you are using the contains_eager() option to indicate the relationships that are eagerly loading. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: SELECT LIKE
Disrupt07 wrote > > @Simon > Thanks. But what is ? Is it SQLAlchemy or pure SQL? > It is a Query object, as described here: http://www.sqlalchemy.org/docs/datamapping.html If you haven't read them yet, I'd recommend working through a tutorial - I found this one really helpful: http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html There's also the 'official' one: http://www.sqlalchemy.org/docs/tutorial.html Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: SELECT LIKE
@Simon Thanks. But what is ? Is it SQLAlchemy or pure SQL? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
Sorry, it looks like is already being discussed. (Serve me right for reading in threaded mode.) From my SA-newbie POV, I'd love it if col.in_() compiled down to false or 0 (whatever works). On Apr 13, 11:35 am, "Rick" <[EMAIL PROTECTED]> wrote: > Wouldn't the semantics of IN seem to imply that the expression "foo IN > ", should always evaluate to false? Clearly, "foo" is not > in the empty set. I can't think of a use case where I'd use IN and > want the expression "anything IN " to evaluate to True. > I'm another user who would use column.in_(), where is > sometimes empty. Alternatively, is there a better way of expressing > "true if an item is in a set, false otherwise"? > > On Apr 12, 11:18 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > On Apr 12, 2007, at 3:32 AM, svilen wrote: > > > > it, but if it's once in a blue moon, u'll get one more disappointed > > > SA user ;0(. > > > right, and then i dont find out until i meet them at Pycon ;) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: IN() bug bit hard
Wouldn't the semantics of IN seem to imply that the expression "foo IN ", should always evaluate to false? Clearly, "foo" is not in the empty set. I can't think of a use case where I'd use IN and want the expression "anything IN " to evaluate to True. I'm another user who would use column.in_(), where is sometimes empty. Alternatively, is there a better way of expressing "true if an item is in a set, false otherwise"? On Apr 12, 11:18 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 12, 2007, at 3:32 AM, svilen wrote: > > > it, but if it's once in a blue moon, u'll get one more disappointed > > SA user ;0(. > > right, and then i dont find out until i meet them at Pycon ;) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: SELECT LIKE
Disrupt07 wrote:
>
> I have a table storing users' info.
> table: userinfo
> columns: name, surname, age, location, ...
>
> I need to query this table using SQLAlchemy's ORM methods (e.g.
> select(), select_by(), get_by()). The query should be like
>SELECT * FROM userinfo WHERE name LIKE 'Ben%' ORDER BY name, age
>
> Which SQLAlchemy method should I use? Can some provide me with an
> example to solve my problem?
>
> Thanks
Columns have a 'like' method, as well as startswith and endswith, so for
your particular example you could use something like this:
.select(userinfo.c.name.startswith('Ben'))
Or:
.select(userinfo.c.name.like('Ben%'))
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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] SELECT LIKE
I have a table storing users' info. table: userinfo columns: name, surname, age, location, ... I need to query this table using SQLAlchemy's ORM methods (e.g. select(), select_by(), get_by()). The query should be like SELECT * FROM userinfo WHERE name LIKE 'Ben%' ORDER BY name, age Which SQLAlchemy method should I use? Can some provide me with an example to solve my problem? Thanks --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Column to default to itself
On Apr 13, 1:47 pm, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> request_table = Table('request', metadata,
> Column('id', Integer, primary_key=True),
> Column('number', Integer, unique=True, nullable=True,
> default=text('(SELECT coalesce(max(number), 0) + 1 FROM
> request)')))
>
> This seems to work well. But is this a good way to do this or can it
> cause complications?
This will start to throw duplicate key errors under heavier load.
There is a window of time between insert and commit when another
insert will get the same duplicate id. It might not be a problem in
your case, but I have had to deal with a similar problem when creating
sequentally hashed database table (for provable temporal ordering and
integrity) which will see very high loads. You'll either occasionally
get duplicate values in the database or you have to serialize all
inserts. The best that can be done, is try to occasionally get holes
and assume that usually transactions succeed and catch rollbacks. That
is a lot harder.
Ants
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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] threading.currentThread().session = create_session() ??
Hi All, I have a web application where I have multiple DAOs that may be called during phases within a request. Since I want to make sure that I get the same session with every DAO I was wondering if I can add a runtime session attribute to the current thread returned Python's threading.currentThread() as soon as a request hits the application. I will be happy if someone can share approach for using SessionContext in case of a web application so that every request is associated with the same session/ thx. - A --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Child to parent - Uni-directional relationships - Is that supported ?
cool thx.
On 4/13/07, svilen <[EMAIL PROTECTED]> wrote:
>
>
>
> > I have a Parent - Child (1:N) relationship between Class and Exam
> > table.
> >
> > Class -> Exam
> > 1 :N
> >
> > Now since a "Class" could have millions of "Exam" I don't want have
> > an attribute on Class called exams. Instead I only want an
> > attribute on "Exam" to the parent "Class".
> >
> > Can we do this in SA ?
> >
> > Will do the below do the job ? Just asking out of curiosity without
> > testing.
> >
> > mapper(Exam, examtable, properties = {class: relation(Class)})
>
> yes, u'll get the other side only if u require backref=something
>
> also do specify relation( uselist=False,) as it mistakes
> sometimes.
>
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: Child to parent - Uni-directional relationships - Is that supported ?
> I have a Parent - Child (1:N) relationship between Class and Exam
> table.
>
> Class -> Exam
> 1 :N
>
> Now since a "Class" could have millions of "Exam" I don't want have
> an attribute on Class called exams. Instead I only want an
> attribute on "Exam" to the parent "Class".
>
> Can we do this in SA ?
>
> Will do the below do the job ? Just asking out of curiosity without
> testing.
>
> mapper(Exam, examtable, properties = {class: relation(Class)})
yes, u'll get the other side only if u require backref=something
also do specify relation( uselist=False,) as it mistakes
sometimes.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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] Child to parent - Uni-directional relationships - Is that supported ?
Hi Guys,
I have a Parent - Child (1:N) relationship between Class and Exam table.
Class -> Exam
1 :N
Now since a "Class" could have millions of "Exam" I don't want have an
attribute on Class called exams. Instead I only want an attribute on "Exam"
to the parent "Class".
Can we do this in SA ?
Will do the below do the job ? Just asking out of curiosity without testing.
mapper(Exam, examtable, properties = {class: relation(Class)})
Thx
- A
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: Column to default to itself
request_table = Table('request', metadata,
Column('id', Integer, primary_key=True),
Column('number', Integer, unique=True, nullable=True,
default=text('(SELECT coalesce(max(number), 0) + 1 FROM
request)')))
This seems to work well. But is this a good way to do this or can it
cause complications?
On Apr 13, 11:23 am, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> Ok, I'd rather handle it on the database level. Is that just a matter
> of creating a function and calling it on insert?
>
> Koen
>
> On Apr 13, 4:47 am, "Ants Aasma" <[EMAIL PROTECTED]> wrote:
>
> > On Apr 13, 2:47 am, Jorge Godoy <[EMAIL PROTECTED]> wrote:
>
> > > IF you insist on doing that at your code, make the column UNIQUE (or a
> > > PK...) and write something like this pseudocode:
>
> > > def save_data():
> > > def insert_data():
> > >try:
> > >unique_column_value = get_max_from_unique_column
> > >Class(unique_column_value + 1, 'other data')
> > >except YourDBExceptionForConstraintViolation:
> > >sleep(random.random())
> > >insert_data()
>
> > > The 'sleep(random.random())' is there to avoid constant clashes and to
> > > be "fair" to all connections that are inserting data on your table.
>
> > To get an uninterrupted number sequence you need to serialize your
> > inserts to that specific entity, for which you basically need locking.
> > The quoted approach is optimistic locking, where you hope that no one
> > tries to insert another row between when you use the
> > get_max_from_unique_column and do the database commit, but are ready
> > to retry if that expectation fails. Another way would be to use
> > pessimistic locking, by doing the get_max_from_unique_column query
> > with lockmode='update'. Then any other thread trying to insert another
> > row while you're busy inserting yours will have to wait.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: Column to default to itself
On Apr 13, 12:23 pm, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> Ok, I'd rather handle it on the database level. Is that just a matter
> of creating a function and calling it on insert?
You need a sequence that has locks and rolls back on rollback.
Simplest way is to use a table: CREATE TABLE sequences (name
varchar(100) not null primary key, value int not null default 0);
and a stored proc to get the next id, in postgresql that would be
CREATE FUNCTION next_id(varchar) RETURNS int AS $$
DECLARE id int;
BEGIN
UPDATE sequences SET value = value + 1 WHERE name = $1;
SELECT value INTO id FROM sequences WHERE name = $1;
RETURN id;
END
$$ LANGUAGE plpgsql;
Then say you have table bar, you initialize the sequence:
INSERT INTO sequences ('bar', 0);
And create the table:
CREATE TABLE bar (bar_id int NOT NULL DEFAULT next_id('bar') PRIMARY
KEY, ...);
or in SA:
Table('bar',
Column('id', Integer, primary_key=True,
default=func.next_id('bar')), ...
)
Ants
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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: query().filter_by(boolean)
Michael Bayer wrote: > please file a ticket for this. done, #535 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Column to default to itself
Ok, I'd rather handle it on the database level. Is that just a matter of creating a function and calling it on insert? Koen On Apr 13, 4:47 am, "Ants Aasma" <[EMAIL PROTECTED]> wrote: > On Apr 13, 2:47 am, Jorge Godoy <[EMAIL PROTECTED]> wrote: > > > IF you insist on doing that at your code, make the column UNIQUE (or a > > PK...) and write something like this pseudocode: > > > def save_data(): > > def insert_data(): > >try: > >unique_column_value = get_max_from_unique_column > >Class(unique_column_value + 1, 'other data') > >except YourDBExceptionForConstraintViolation: > >sleep(random.random()) > >insert_data() > > > The 'sleep(random.random())' is there to avoid constant clashes and to > > be "fair" to all connections that are inserting data on your table. > > To get an uninterrupted number sequence you need to serialize your > inserts to that specific entity, for which you basically need locking. > The quoted approach is optimistic locking, where you hope that no one > tries to insert another row between when you use the > get_max_from_unique_column and do the database commit, but are ready > to retry if that expectation fails. Another way would be to use > pessimistic locking, by doing the get_max_from_unique_column query > with lockmode='update'. Then any other thread trying to insert another > row while you're busy inserting yours will have to wait. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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 -~--~~~~--~~--~--~---
