[sqlalchemy] Re: Replacing a Session Engine
Ok, that sounds logical, but that defeats the purpose of it for me :-) Whenever a Checkout user logs out I clear the session. On a new login I create fresh session that has to re-cache lot of data. The logins depend on a valid postgres user. If I could find a way to replace the engine/connection for a session it would keep the objects around, making login way faster the second time. Any ideas? :-) - Koen On Nov 24, 4:51 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > that is the way to do it as far as switching engines. your example > doesn't work because the state of "p1" is "persistent" as opposed to > "pending", so the example updates a row that isn't there. > > you need to build a copy constructor on Person and make a new, > non-persistent Person object for your second engine. the Session can't > implicitly figure out what your intent is. > > > > Koen Bok wrote: > > > Hey I was wondering if it was possible to replace an engine in a > > session. It does not seem to work, but maybe I'm doing something > > really stupid. > > >http://pastie.org/322501 > > > Kindest regards, > > > Koen Bok - madebysofa.com --~--~-~--~~~---~--~~ 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] Replacing a Session Engine
Hey I was wondering if it was possible to replace an engine in a session. It does not seem to work, but maybe I'm doing something really stupid. http://pastie.org/322501 Kindest regards, Koen Bok - madebysofa.com --~--~-~--~~~---~--~~ 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: Smart Caching Mapper Question
Yep, exactly that. It would speed up my (UI) app immensely. Any ideas how to approach something like that? On May 28, 5:07 pm, [EMAIL PROTECTED] wrote: > some time ago i posted a list of my ideas along > this..http://groups.google.com/group/sqlalchemy/browse_thread/thread/d88696... > > > > > be ware: its all pure theory. > > -1 (horizontal) (eager) loading ONLY of the needed row attributes, > > also hierarhicaly (a.b.c.d) > > -2 (vertical) simultanously loading of columns - e.g. the lazy > > attribites - wholly, or in portions/slices (depending on UI > > visibility or other slice-size) > > -3 skipping creation of objects - only using the data, if time of > > creation gets critical. For example a simple report for a > > name.alias and age of person, the creation of 100,000 Persons can > > be ommitted. To be able to do drill-down, the person.db_id would be > > needed+stored too. > > -4 cacheing of some aggregations/calculations in special > > columns/tables, so they're not re-invented everytime > > -5 translate the whole report - calculations, aggregations, > > grouping etc. into sql and use the result as is (with same thing > > about db_id's) > > except the #4/aggregation which is pretty automated now, i dont have > yet implementation of the rest. > i think u're talking about #2 ? > > ciao > svilen > > > Hey All, > > > I have a conceptual question. > > > You have two ways to get relations; lazy and nonlazy. Nonlazy works > > great for saving queries but can get pretty slow with complicated > > joins. So I was wondering if there was a third way; pre fetching > > all the data for relations and let the mapper get the relation data > > from a cache instead of doing another query. > > > It's kinda hard to explain, so I wrote an example script at: > >http://paste.pocoo.org/show/55145/ > > > I guess this should be possible by writing some MapperExtension? > > Did anyone do anything like this, or maybe has some pointers? > > > Thanks! > > > Koen --~--~-~--~~~---~--~~ 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] Smart Caching Mapper Question
Hey All, I have a conceptual question. You have two ways to get relations; lazy and nonlazy. Nonlazy works great for saving queries but can get pretty slow with complicated joins. So I was wondering if there was a third way; pre fetching all the data for relations and let the mapper get the relation data from a cache instead of doing another query. It's kinda hard to explain, so I wrote an example script at: http://paste.pocoo.org/show/55145/ I guess this should be possible by writing some MapperExtension? Did anyone do anything like this, or maybe has some pointers? Thanks! Koen --~--~-~--~~~---~--~~ 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: problems with py2app
Hey Iain,
If you build apps with py2app it tries to figure out which modules to
include automatically. If these modules are nested in some weird way
it sometimes chokes. A solution is to import that module by hand in
your main script (your-app-name.py) or telling py2app it needs to add
the module in the setup dict (see manual).
Good luck!
Koen - madebysofa.com
On Apr 26, 7:53 am, iain duncan <[EMAIL PROTECTED]> wrote:
> Hi folks, I seem to be having a problem with sqlalchemy and py2app, but
> I am very new to OS X and py2app, so I could be doing something stupid.
> When we try to build the app we get this:
>
> ImportError: No module named logging
> Traceback (most recent call last):
> File
> "/Users/bear/iain/booking-wx/dist/booking_main.app/Contents/Resources/__boo
> t__.py",
> line 137, in
> _run('booking_main.py')
> File
> "/Users/bear/iain/booking-wx/dist/booking_main.app/Contents/Resources/__boo
> t__.py",
> line 134, in _run
> execfile(path, globals(), globals())
> File
> "/Users/bear/iain/booking-wx/dist/booking_main.app/Contents/Resources/booki
> ng_main.py",
> line 10, in
> from model_extern import *
> File "model_extern.pyc", line 15, in
> File "sqlalchemy/__init__.pyc", line 29, in
> File "sqlalchemy/engine/__init__.pyc", line 54, in
> File "sqlalchemy/engine/base.pyc", line 16, in
> File "sqlalchemy/logging.pyc", line 35, in
> ImportError: No module named logging
> 2008-04-25 22:43:27.066 booking_main[457] booking_main Error
> 2008-04-25 22:43:27.067 booking_main[457] booking_main Error
> An unexpected error has occurred during execution of the main script
>
> I'm not import logging, and the only other libraries being used are
> wxPython and formencode. wx is working ok.
>
> Any tips or even stories of success/failure would be much appreciated!
> Thanks
> Iain
--~--~-~--~~~---~--~~
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: Get Mapper for Table
Got it, thanks!
On Apr 16, 4:26 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Apr 16, 2008, at 10:13 AM, Koen Bok wrote:
>
>
>
> > Hey all,
>
> > Before 0.4.5 I used this code to get the mapped class for a table. It
> > worked fine, but mapper_registry was made private in 0.4.5 and the
> > private function does not behave the same way for some reason. But as
> > this code is pretty nasty anyway I was wondering if there was a better
> > way to do this. If not, can anyone hint me how to make this work
> > again?
>
> from sqlalchemy.orm import mapper as _mapper
>
> my_table_registry = {}
> def mapper(cls, tbl, **kwargs):
> my_table_registry[tbl] = cls
> return _mapper(cls, tbl, **kwargs)
--~--~-~--~~~---~--~~
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] Get Mapper for Table
Hey all, Before 0.4.5 I used this code to get the mapped class for a table. It worked fine, but mapper_registry was made private in 0.4.5 and the private function does not behave the same way for some reason. But as this code is pretty nasty anyway I was wondering if there was a better way to do this. If not, can anyone hint me how to make this work again? If you are wondering, I need this for asynchronous updates in postgres. The modification table holds the table name for a modification and inserted by a trigger. Koen classTableCache = dict() def classForTable(tableName): """Returns the mapped class for a particular table name.""" if classTableCache == dict(): for table in metadata.table_iterator(reverse=False): for mapper in mapperlib.mapper_registry.values(): if table == mapper.base_mapper.mapped_table: # This is an extremely ugly hack that only works when all patent mapper names # are longer than inherited mapper names if classTableCache.has_key(table.name): if len(str(mapper.class_)) < len(str(classTableCache[table.name])): classTableCache[table.name] = mapper.class_ else: classTableCache[table.name] = mapper.class_ return classTableCache[tableName] --~--~-~--~~~---~--~~ 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: Mapper Issue Upgrading to 0.4.5
For anyone who needs this too, there is a small typo in the above
example. It should be:
mapper(PGUser, pg_user, properties={
'groups': relation(PGGroup, viewonly=True,
primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
foreign_keys=[pg_group.c.grolist],
_local_remote_pairs=[(pg_user.c.usesysid,
pg_group.c.grolist)])})
On Apr 14, 11:26 pm, Koen Bok <[EMAIL PROTECTED]> wrote:
> Wow that is fast!
>
> Thanks man ;-)
>
> On Apr 14, 5:51 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> > On Apr 14, 2008, at 7:44 AM, Koen Bok wrote:
>
> > > Hey All,
>
> > > I was upgrading our app from 0.4.1 to 0.4.5 and this code broke:
>
> > > pg_user = Table('pg_user', metadata,
> > > Column('usesysid', Integer, primary_key=True),
> > > Column('usename', Unicode(), unique=True))
>
> > > pg_group = Table('pg_group', metadata,
> > > Column('grosysid', Integer, primary_key=True),
> > > Column('groname', Unicode(), unique=True),
> > > Column('grolist', PGArray(unicode)))
>
> > > mapper(PGUser, pg_user, properties={
> > > 'groups': relation(PGGroup, viewonly=True,
> > > primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
> > > foreign_keys=[pg_group.c.grolist])})
>
> > use the trunk and say:
>
> > mapper(PGUser, pg_user, properties={
> > 'groups': relation(PGGroup, viewonly=True,
> > primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
> > foreign_keys=[pg_group.c.grolist],
> > _local_remote_pairs=[pg_user.c.usesysid, pg_group.c.grolist]
> > )})
>
> > this doesn't yet handle the full blown "functions/casts in primaryjoin
> > conditions" ticket but its part of the way there.
--~--~-~--~~~---~--~~
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: Mapper Issue Upgrading to 0.4.5
Wow that is fast!
Thanks man ;-)
On Apr 14, 5:51 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Apr 14, 2008, at 7:44 AM, Koen Bok wrote:
>
>
>
>
>
> > Hey All,
>
> > I was upgrading our app from 0.4.1 to 0.4.5 and this code broke:
>
> > pg_user = Table('pg_user', metadata,
> > Column('usesysid', Integer, primary_key=True),
> > Column('usename', Unicode(), unique=True))
>
> > pg_group = Table('pg_group', metadata,
> > Column('grosysid', Integer, primary_key=True),
> > Column('groname', Unicode(), unique=True),
> > Column('grolist', PGArray(unicode)))
>
> > mapper(PGUser, pg_user, properties={
> > 'groups': relation(PGGroup, viewonly=True,
> > primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
> > foreign_keys=[pg_group.c.grolist])})
>
> use the trunk and say:
>
> mapper(PGUser, pg_user, properties={
> 'groups': relation(PGGroup, viewonly=True,
> primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
> foreign_keys=[pg_group.c.grolist],
> _local_remote_pairs=[pg_user.c.usesysid, pg_group.c.grolist]
> )})
>
> this doesn't yet handle the full blown "functions/casts in primaryjoin
> conditions" ticket but its part of the way there.
--~--~-~--~~~---~--~~
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] Mapper Issue Upgrading to 0.4.5
Hey All,
I was upgrading our app from 0.4.1 to 0.4.5 and this code broke:
pg_user = Table('pg_user', metadata,
Column('usesysid', Integer, primary_key=True),
Column('usename', Unicode(), unique=True))
pg_group = Table('pg_group', metadata,
Column('grosysid', Integer, primary_key=True),
Column('groname', Unicode(), unique=True),
Column('grolist', PGArray(unicode)))
mapper(PGUser, pg_user, properties={
'groups': relation(PGGroup, viewonly=True,
primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
foreign_keys=[pg_group.c.grolist])})
"Specify the foreign_keys argument to indicate which columns on the
relation are foreign." % (self.primaryjoin, self))
ArgumentError: Could not determine relation direction for primaryjoin
condition 'pg_user.usesysid = any(pg_group.grolist)', on relation
PGUser.groups (PGGroup). Specify the foreign_keys argument to indicate
which columns on the relation are foreign.
I found this post(1) so I tried upgrading to SVN, but I still got the
error. Does it have to do anything with the any() function and the
PGArray column type?
Koen
(1)
http://groups.google.com/group/sqlalchemy/browse_thread/thread/f8e5b5fa07ed9ab9/749beae25624c2f8?lnk=gst&q=foreign_keys#749beae25624c2f8
--~--~-~--~~~---~--~~
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: downtime
Hey Mike, We (Sofa) just sponsored a year 512 hosting. Check the paypal. And keep up the good work! Koen Bok - madebysofa.com On Mar 24, 8:39 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > another theory is that I had some vhost config that wasn't synced up > on both DNS servers that I use. not sure if that could have created > some confusing situation on the apache side if something changed DNS- > wise. > > On Mar 24, 2008, at 2:39 PM, Michael Bayer wrote: > > > > > Well, I dont think anything exotic caused this downtime. Looks pretty > > much like Django user traffic was suddenly pointed at the website via > > Michael Trier's blog and some child processes got hung. So we > > basically got Django'ed :) > > > The site is just a 256 meg slicehost running four hosts on only four > > child apache procs running, so if our traffic is starting to hit the > > next level i might need to up it to 512 --~--~-~--~~~---~--~~ 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: foreign key support in SQLite
Hey Manilo, My feeling is that this is out of the scope of the SQLAlchemy project. It should support the official workings for each database package, not extend it. It is pretty easy to implement this yourself and use SQLAlchemy on top of that (I use a lot of triggers in my app). There may be something to say for pythonizing triggers so they become database agnostic, but I am not even sure if that is possible given all the different triggers different databases can have. What do you mean by 'unstable internals' in SQLAlchemy? Kindest regards, Koen Bok On Jan 28, 12:03 pm, Manlio Perillo <[EMAIL PROTECTED]> wrote: > Hi. > > In this wiki page:http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers > > there is a recipe on how to add basic foreign key support to SQLite > using triggers. > > Is it possible to implement this recipe in SQLAlchemy? > > Some time ago I have tried to implement it, by adding support for > triggers in SQLAlchemy (so that they can be automatically > created/dropped) but I have abandoned the project because the internals > of SQLAlchemy are unstable. > > Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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: Polymorphic from multiple columns
A composite type would be perfect for me. > Michael Bayer wrote: > > you cant do it right now. but its something we could support. its > > unclear to me if we should just go for "composite types" as the way to > > do it, or just use a callable. using a composite is likely cleaner > > and would integrate with the "save" process better (otherwise, the > > callable needs to generate the "discriminator" value at save time as > > well). > > > On Dec 3, 2007, at 11:09 AM, Koen Bok wrote: > > >> I'd like to make a polymorphic mapper based on two columns. Is that > >> possible? > > >> See example code here:http://paste.pocoo.org/show/13799/ > > >> Thanks, Koen --~--~-~--~~~---~--~~ 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] Polymorphic from multiple columns
I'd like to make a polymorphic mapper based on two columns. Is that possible? See example code here: http://paste.pocoo.org/show/13799/ Thanks, Koen --~--~-~--~~~---~--~~ 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: Expire Objects from other Thread
Cool, thanks! I got it working pretty wel I think. I replaced the iterator code with a Session.get. Check out the code here: http://paste.pocoo.org/show/13434/ The only thing I am still wondering if Session.get also returns objects in Session.new? Otherwise this won't fix the other bug. I think it does as my tests pass fine. You just earned yourself another sushi meal ;-) Koen On Dec 1, 8:40 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Dec 1, 2007, at 9:33 AM, Koen Bok wrote: > > > > > This is quite hackish, but just an experiment for me. > > > We use listen/notify in our app. A seperate thread is listening for > > notifications from updated rows in the table. If one is received I'd > > like to expire an object in the Session cache so it gets refetched the > > next time it is accessed. I have it working, but I get a lot of these: > > > AttributeError: 'ReceiveRequest' object has no attribute > > '_instance_key' > > > I guess that's because the background thread is marking objects as > > expired while the main thread is performing actions on them. > > _instance_key doesn't get removed by the expire operation, so thats > something else. i notice youre iterating through the whole session > which will return objects from "new" as well, so you might want to > iterate just through session.identity_map.values() there. also i > dont understand the point of that little iterate through > session.registry.registry but the main idea seems OK. --~--~-~--~~~---~--~~ 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] Expire Objects from other Thread
This is quite hackish, but just an experiment for me. We use listen/notify in our app. A seperate thread is listening for notifications from updated rows in the table. If one is received I'd like to expire an object in the Session cache so it gets refetched the next time it is accessed. I have it working, but I get a lot of these: AttributeError: 'ReceiveRequest' object has no attribute '_instance_key' I guess that's because the background thread is marking objects as expired while the main thread is performing actions on them. Is there a way to make this work? Or is it not a good idea at all? Some example code of my implementation: http://paste.pocoo.org/show/13402/ --~--~-~--~~~---~--~~ 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] Polymorphic Mapper with Custom Column Type
Hi, I am trying to store numeric values for an object inverted in the database. For that I created a custom TypeDecorator. It work's fine. But I only want to apply it to one inherited object type for that object. See example code at: http://paste.pocoo.org/show/12411/ Thanks, Koen --~--~-~--~~~---~--~~ 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: Merge Questions
Aight, thanks for the explanation! Koen On Nov 20, 5:02 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Nov 20, 2007, at 6:19 AM, Koen Bok wrote: > > > > > I have some questions about pickling/merging objects. > > > I have written example code to demonstrate:http://pastie.caboo.se/120146 > > > Kindest regards, > > OK ive rewritten that error message in r3809 since it made no sense. > now it will say: > > Could not update instance '[EMAIL PROTECTED]', identity key ( '__main__.User'>, (1,), None); a different instance with the same > identity key already exists in this session. > > the error is that you need to use the return value of merge: > > user1 = Session.merge(user1, dont_load=True) > > as for the "dirty" list, i think we might need to put a more friendly > "dirty" accessor on there (or a note in the docs)...the merge process > sets attributes in the normal way so that things like backrefs fire > off, but a side effect is that its flipping on the "modified" flag on > every object. the "modified" flag is just a cue to add the object to > the flush process, but if nothing actually changed on it then it wont > be updated. > > A more expensive check, i.e. comparing the attribute values on the > instances, would reveal that they arent "dirty" after all, and this is > the check that happens during flush. > > we cant *really* preserve the modified flag here from merged to > already-present, since if A1.foo == "ed", and A2.foo == "jack", both > have no "modified" flag, but then you merge A2 on top of A1, A1 *is* > actually modified. --~--~-~--~~~---~--~~ 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] Merge Questions
I have some questions about pickling/merging objects. I have written example code to demonstrate: http://pastie.caboo.se/120146 Kindest regards, Koen Bok --~--~-~--~~~---~--~~ 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: SQLAlchemy 0.4.1 released
Thanks Mike. Cool stuff!
On Nov 19, 1:26 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> Hello alchemers -
>
> This is an awesome release. I'm excited about this one. With our new
> shiny clean 0.4 codebase, internals are starting to look a lot more
> intelligent, and new things are becoming possible. Call counts are
> going down like a rock. Intents and behaviors are clarifying and
> sharpeningplus Super Mario Galaxy arrives tomorrow so its time for
> a break.
>
> Some highlights of this release:
>
> - you might notice that some eager load operations are suddenly a lot
> faster, particularly on MySQL. This is because we've improved the
> queries that are issued when you use eager loading with LIMIT and/or
> OFFSET; whereas we previously would wrap the LIMITed query in a
> subquery, join back to the mapped table, and then add the eager
> criterion outer joined against the mapped table, a trick we've been
> doing since 0.1.0, we now outer join the eager criterion directly
> against the subquery, and the main mapper pulls rows straight from the
> subquery columns. Improved SQL expression functionality has allowed
> this to be possible. What it means is, an eager load with LIMIT/
> OFFSET uses one less JOIN in all cases. This is an example of SA's
> very rich expression constructs paying off - since a query that is
> much more efficient on the database side trumps the hundred or so
> method calls spent compiling the query anyday.
>
> - session.refresh() and session.expire() can now operate on individual
> instance attributes. Just say session.expire(myobject, ['items',
> 'description', 'name']), and all three of those attributes, whether
> they're just columns or relations to other objects, will go blank
> until you next access them on the instance, at which point they are
> refreshed from the DB. Column attributes will be grouped together in
> a single select() statement and related tables will be lazy loaded
> individually right now. Also, the internal mechanisms used by
> deferred() columns, refresh/expire operations, and polymorphically
> deferred columns have all been merged into one system, which means
> less internal complexity and more consistent behavior.
>
> - the API of the session has been "hardened". This means its going to
> check more closely that operations make sense (and it also no longer
> raises some errors that did not make sense in certain circumstances).
> The biggest gotcha we've observed so far from people using trunk is
> that session.save() is used *only* for entities that have not been
> saved to the database yet. If you put an already-stored instance in
> save(), you'll get an error. This has always been the contract, it
> just hasn't complained previously. If you want to put things in the
> session without caring if they've already been saved or not, use
> session.save_or_update(myinstance). We've also fixed things regarding
> entities that have been de-pickled and placed back into the session -
> some annoying errors that used to occur have been fixed.
>
> - still in the session category, the merge() method gets a
> "dont_load=True" argument. Everyone using caches like memcached can
> now place copies of their cached objects back in the session using
> "myinstance = merge(mycachedinstance, dont_load=True)", and the
> instance will be fully copied as though it were loaded from the
> database, *without* a load operation proceeding; it will trust that
> you want that instance state in the session.
>
> - query.options() are way more intelligent. Suppose you have a large
> bidirectional chain of relations. If you say something like
> query.options(eagerload('orders.items.keywords.items.orders')), it
> will accurately target the 'orders' relation at the end of that chain
> and nothing else. On a similar topic, self-referential eagerloads can
> be set up on the fly, such as
> query.options(eagerload_all('children.children.children')) without
> needing to set the "join_depth" flag on relation().
>
> - method call overhead continues to be cut down. Many expensive calls
> in statement compilation, clauseelement construction, and statement
> execution have been whacked away completely and replaced with simpler
> and more direct behaviors, and results are more accurate and correct.
> This continues along from all that we've done in 0.4 and at this point
> most call counts should be half of what they were in 0.3. I invite
> everyone to take a tour around expression.py, compiler.py, and
> critique; we've had a huge amount of housecleaning in these modules
> (and others), and further suggestions/ideas/flames are entirely
> welcome (though not too early in the morning) on sqlalchemy-devel.
>
> - in the fringe category, you can now define methods like __hash__(),
> __nonzero__(), and __eq__() on your mapped instances and the ORM won't
> get con
[sqlalchemy] Re: Temporary Disable Session.mapper with Scoping
Ok, I could do that. On Nov 15, 4:09 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Nov 15, 2007, at 9:06 AM, Koen Bok wrote: > > > > > Hi, > > > I use scoped_session in our project for auto object saving which is > > great. But when I initialize a new database I need to flush some > > objects with another engine. I could not find a nice way to do that so > > I created a new session with it's own engine and merged the objects > > into that one and cleared the from my Session object. That worked, but > > all their relations are lost and they have a parent child relation. > > How to do this? > > > Sample code uploaded as test_scope.py > > it would be easier if you just didnt use autosave, id really love to > remove that feature entirely. --~--~-~--~~~---~--~~ 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] Temporary Disable Session.mapper with Scoping
Hi, I use scoped_session in our project for auto object saving which is great. But when I initialize a new database I need to flush some objects with another engine. I could not find a nice way to do that so I created a new session with it's own engine and merged the objects into that one and cleared the from my Session object. That worked, but all their relations are lost and they have a parent child relation. How to do this? Sample code uploaded as test_scope.py Koen --~--~-~--~~~---~--~~ 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: Object Init Bug?
Yep. That was it. Stupid me...
On Nov 13, 4:10 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Nov 13, 2007, at 8:25 AM, Koen Bok wrote:
>
> > Hi, I have this strange bug or problem...
>
> > I have a basic class like this mapped to a table with the same name.
> > Metatype is a relation to the metatype object.
>
> > class MetaData(DBObject):
>
> >def __init__(self, metatype, value, item):
>
> >self.metatype = metatype
> >self.item = item
> >self.value = value
>
> > class MetaType(DBObject):
>
> >def __init__(self, name, parent=None, offset=None):
>
> >self.parent = parent
> >self.offset = offset
> >self.name = name
>
> > Now when I create a MetaData object like this I get the following
> > error:
>
> >>>> nameMetaType = MetaType('name')
> >>>> product = Product(Decimal('20.00'), Decimal('15.00'))
> >>>> Session.commit()
>
> >>>> MetaData(nameMetaType, 'Koen', product)
>
> > Traceback (most recent call last):
> > method testMetabug in test_connection.py at line 16
> >MetaData(nameMetaType, 'Koen', product)
> > method init in attributes.py at line 1025
> >oldinit(instance, *args, **kwargs)
> > TypeError: __init__() takes at most 3 arguments (4 given)
>
> what happens if you change the name "MetaData" to something else ?
> your error below seems to indicate that your MetaData class/object is
> getting treated like a SQLAlchemy "MetaData" 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?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Object Init Bug?
Hi, I have this strange bug or problem...
I have a basic class like this mapped to a table with the same name.
Metatype is a relation to the metatype object.
class MetaData(DBObject):
def __init__(self, metatype, value, item):
self.metatype = metatype
self.item = item
self.value = value
class MetaType(DBObject):
def __init__(self, name, parent=None, offset=None):
self.parent = parent
self.offset = offset
self.name = name
Now when I create a MetaData object like this I get the following
error:
>>> nameMetaType = MetaType('name')
>>> product = Product(Decimal('20.00'), Decimal('15.00'))
>>> Session.commit()
>>> MetaData(nameMetaType, 'Koen', product)
Traceback (most recent call last):
method testMetabug in test_connection.py at line 16
MetaData(nameMetaType, 'Koen', product)
method init in attributes.py at line 1025
oldinit(instance, *args, **kwargs)
TypeError: __init__() takes at most 3 arguments (4 given)
That seems odd to me, as it clearly takes 4 arguments: self, metatype,
value, item. But I tried it anyway with 3 arguments to see what it
did:
>>> MetaData(nameMetaType, 'Koen')
Traceback (most recent call last):
method testMetabug in test_connection.py at line 16
MetaData(nameMetaType, 'Koen')
method init in attributes.py at line 1025
oldinit(instance, *args, **kwargs)
method __init__ in schema.py at line 1070
self.reflect()
method reflect in schema.py at line 1193
available = util.OrderedSet(bind.engine.table_names(schema,
AttributeError: 'MetaType' object has no attribute 'engine'
Extra info: The item is a polymorphic mapper to product, employee
etc... I use scoped_session and created my mappers with
Session.mapper.
If you need more info let me know...
Thanks,
Koen
--~--~-~--~~~---~--~~
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] Code Organisation
Hi there, We have a pretty large project by now and we run into import loops. So I decided to restructure the code, and I hoped some people with more experience can comment on this. The basic problem is this: We have the database object code, mappers and tables neatly organized in one module (db). The controller code imports this module to get access to these objects. All fine. But we have another object called Connection which is a singleton class that actually manages the connection to our database. It is basically a wrapper for create_engine and contextual_session. But next to that it keeps info about the current login state like the employee, location etc. The mapped database objects need this info on their turn to add the current user to a new object etc. So the Connection object depends on the Mapped Database Objects, but the Mapped Database Object depend on the Connection object too. Anyone got a good tip to solve this? Or designed something similar? Thanks, Koen --~--~-~--~~~---~--~~ 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] Load Lazy Relation in Separate Thread
Hi, I am doing some optimizations on our app, and one thing I was wondering is if I can have a lazy relation loading in a separate thread. The way I'd like to use it is like this. I have a list with products, and these products show a graph with stock levels. To draw this graph, the object fetches it's relation stock. The stock object is built like this: SELECT amount - (SELECT sum(qty) FROM orders WHERE id_product=stock.id_product) as available FROM stock WHERE id_product=1234; This is because we need to correct stock levels with the already placed orders to get a good idea of availability. This query takes fairly long (depending on the orders) so I'd like to draw a gray bar initially, start a thread to fetch the stock object and draw it once we have that result. Is this possible? If so, can anyone give me a hint how to start? Thanks, Koen --~--~-~--~~~---~--~~ 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: reviving migrate
I have no info on this, but I have seen the current owner on this and the migrate group. It would be awesome if migrate would work again. Koen On Sep 9, 2:53 pm, Jan Dittberner <[EMAIL PROTECTED]> wrote: > I read the discussion regarding the status of the migrate tool and > would like to help with this project. > > I built the Debian package for migrate (http://packages.qa.debian.org/ > m/migrate.html) which is now available in Debian testing and I use > migrate with the SA version contained in etch (0.3.1). Maybe I could > find some time to look at the existing code and create a version that > works with current SA. Is someone here with a better insight on what > is broken in migrate now, to get me started? > > Regards > Jan Dittberner --~--~-~--~~~---~--~~ 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: Get Table/Column Sequence Names
Thanks for the tip! I solved it this way now and it works pretty well. if True in [c.primary_key for c in tableName.c]: sequenceName = '%s_id_seq' % tableName Koen On Sep 9, 12:52 am, "Orest Kozyar" <[EMAIL PROTECTED]> wrote: > I don't know if SQLAlchemy has an easy way of determining this metadata, but > you can likely query the system tables directly. pg_attrdef stores this > information in the adbin/adsrc columns. You can parse the adsrc column to > get the sequence name. You can filter your query easily by table and/or > column. > Seehttp://www.postgresql.org/docs/8.2/interactive/catalog-pg-attrdef.htmlfor > more info. > > Hopefully someone else who knows SQLAlchemy better will know whether it's > possible to easily extract this info via metadata. > > Orest> -Original Message- > > From: sqlalchemy@googlegroups.com > > [mailto:[EMAIL PROTECTED] On Behalf Of Koen Bok > > Sent: Saturday, September 08, 2007 6:17 PM > > To: sqlalchemy > > Subject: [sqlalchemy] Get Table/Column Sequence Names > > > For sqlalchemy auto generated sequences (postgres) is there a > > nice way to find out: > > > - If a table/column has any > > - If so, what are the names > > > Thanks! Koen --~--~-~--~~~---~--~~ 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] Get Table/Column Sequence Names
For sqlalchemy auto generated sequences (postgres) is there a nice way to find out: - If a table/column has any - If so, what are the names Thanks! Koen --~--~-~--~~~---~--~~ 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: max/coalesce bug in 0.4?
Tried that, but it just places the select statement within the insert
statement without brackets:
2007-09-06 18:00:57,603 INFO sqlalchemy.engine.base.Engine.0x..90
INSERT INTO request (id, metanumberstate) VALUES (%(id)s, SELECT
coalesce(max(metanumber.id), %(coalesce)s)
FROM metanumber)
2007-09-06 18:00:57,604 INFO sqlalchemy.engine.base.Engine.0x..90
{'coalesce': 0, 'id': 1L}
2007-09-06 18:00:57,609 INFO sqlalchemy.engine.base.Engine.0x..90
ROLLBACK
If I put the brackets in by hand, it works fine...
On Sep 6, 5:53 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Sep 6, 2007, at 10:28 AM, Koen Bok wrote:
>
>
>
> > Aight, that would be cool. So this still has to be implemented then,
> > right?
>
> embedded select works eh ? try making your default that:
>
> default=select([func.max(metanumber.id)])
--~--~-~--~~~---~--~~
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: max/coalesce bug in 0.4?
Aight, that would be cool. So this still has to be implemented then,
right?
To be honest, I am not really up to date on the theory behind this
change but I have enough trust to assume it's a good thing :-)
I was playing around and found out that the faulty SQL it generated is
like this:
Test=# INSERT INTO request (id, metanumberstate) VALUES (1,
coalesce(max(metanumber.id), 0));
ERROR: missing FROM-clause entry for table "metanumber" at character
67
But when I do it with a subquery (by hand) it does work.
Test=# INSERT INTO request (id, metanumberstate) VALUES (1, (SELECT
max(metanumber.id) FROM metanumber));
Query OK, 1 rows affected (0.00 sec)
Anyway, I made a test script, you might find it useful. It's test.py
under files
Cheers, Koen
I was just creating a test script for this.
On Sep 6, 4:14 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> all the default SQL functions are being executed "inline" here. so
> it doesnt like aggregates like "max" being placed into defaults like
> that.
>
> the best I can do for you here, other than rolling back the entire
> "inilne" default thing, would look like this:
>
> Column('foo', Integer, ColumnDefault(func.coalesce(func.max
> (metanumber_table.c.id), 0), inline=False))
>
> so that it gets the hint to "pre-execute" that default.
>
> On Sep 6, 2007, at 8:44 AM, Koen Bok wrote:
>
>
>
> > Hi there, I am upgrading my app to 0.4 and while it's going pretty
> > well, I encountered something strange.
>
> > I have the following code:
>
> > request_table = Table('request', metadata,
> >Column('id', Integer, primary_key=True),
> >...
> >Column('metanumberstate', Integer, nullable=False,
> >default=func.coalesce(func.max(metanumber_table.c.id), 0)),
> >...
> >)
>
> > And this is bound to the Request object. But when I init a new
> > instance and commit it I get an error (see below). But this always
> > worked (0.3.x) so I have no clue what I am doing wrong. Anyone has a
> > hint?
>
> > ProgrammingError: (ProgrammingError) missing FROM-clause entry for
> > table "metanumber"
> > LINE 1: ...uest), NULL, 5, 1, 2, 3, 2, NULL, 7,
> > coalesce(max(metanumber...
> > ^
> > 'INSERT INTO request (id, number, id_parent, id_item, id_employee,
> > id_terminal, id_location, srcstocktype, dststocktype, metadatastate,
> > metanumberstate, metataxstate, quantity, discount, over ride_price,
> > allocation, date, is_business, has_tax, is_quote) VALUES (%(id)s,
> > (SELECT coalesce(max(number), 0) + 1 FROM request), %(id_parent)s, %
> > (id_item)s, %(id_employee)s, %(id_terminal)s, %(id_location)s, %
> > (srcstocktype)s, %(dststocktype)s, %(metadatastate)s,
> > coalesce(max(metanumber.id), %(coalesce)s), coalesce(max
> > (metatax.id), %
> > (coalesce_1)s), %(quantity)s, %(discount)s, %(override_price)s, %
> > (allocation)s, now(), %(is_business)s, %(has_tax)s, %
> > (is_quote)s)' {'id_item': 5L, 'is_business': False, 'srcstocktype': 2,
> > 'is_quote': False, 'coalesce_1': 0, 'id_terminal': 2L, 'id_parent':
> > None, 'id_employee': 1L, 'allocation': 0, 'coalesce': 0,
> > 'id_location': 3L, 'has_tax': True, 'override_price': None,
> > 'dststocktype': None, 'discount': None, 'quantity': 1, 'id': 1L,
> > 'metadatastate': 7L}
--~--~-~--~~~---~--~~
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] max/coalesce bug in 0.4?
Hi there, I am upgrading my app to 0.4 and while it's going pretty
well, I encountered something strange.
I have the following code:
request_table = Table('request', metadata,
Column('id', Integer, primary_key=True),
...
Column('metanumberstate', Integer, nullable=False,
default=func.coalesce(func.max(metanumber_table.c.id), 0)),
...
)
And this is bound to the Request object. But when I init a new
instance and commit it I get an error (see below). But this always
worked (0.3.x) so I have no clue what I am doing wrong. Anyone has a
hint?
ProgrammingError: (ProgrammingError) missing FROM-clause entry for
table "metanumber"
LINE 1: ...uest), NULL, 5, 1, 2, 3, 2, NULL, 7,
coalesce(max(metanumber...
^
'INSERT INTO request (id, number, id_parent, id_item, id_employee,
id_terminal, id_location, srcstocktype, dststocktype, metadatastate,
metanumberstate, metataxstate, quantity, discount, over ride_price,
allocation, date, is_business, has_tax, is_quote) VALUES (%(id)s,
(SELECT coalesce(max(number), 0) + 1 FROM request), %(id_parent)s, %
(id_item)s, %(id_employee)s, %(id_terminal)s, %(id_location)s, %
(srcstocktype)s, %(dststocktype)s, %(metadatastate)s,
coalesce(max(metanumber.id), %(coalesce)s), coalesce(max(metatax.id), %
(coalesce_1)s), %(quantity)s, %(discount)s, %(override_price)s, %
(allocation)s, now(), %(is_business)s, %(has_tax)s, %
(is_quote)s)' {'id_item': 5L, 'is_business': False, 'srcstocktype': 2,
'is_quote': False, 'coalesce_1': 0, 'id_terminal': 2L, 'id_parent':
None, 'id_employee': 1L, 'allocation': 0, 'coalesce': 0,
'id_location': 3L, 'has_tax': True, 'override_price': None,
'dststocktype': None, 'discount': None, 'quantity': 1, 'id': 1L,
'metadatastate': 7L}
--~--~-~--~~~---~--~~
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] Connection Timeout
My app saves bookmarks to different Postgres servers, and tries to connect at login. But if the server from the bookmark is not reachable or down, it only times out after like 2 minutes. So I could either check if the host is available before connecting (telnetlib connection to the Postgres port) or put the testconnection in a thread and kill it if there is no connection after x seconds. Or is there a nice way to solve this from withing SQLAlchemy? Ciao Koen --~--~-~--~~~---~--~~ 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: Generate new id
Dear Gennady, I'm afraid flushing is your only option, as you need the next number from the database sequence. What I do is flushing some objects in their init methods already. Kindest regards, Koen Bok On Aug 19, 7:22 am, Gennady <[EMAIL PROTECTED]> wrote: > Hello, > > I'm a new sqlalchemy user. I have a question about generate id. > > I have a class with __init__ method, and after some initialization > __init__ call events. Event handlers must know about id of new object. > > But if I use Sequence I don't know about new id in __init__ method > before I flush changes to the database. And I can have two or more > processes that connect to database. If I generate id in python may be > a conflict on save objects in two processes. > > What is right way to resolve this problem? How to generate id and use > transactions? > > Thank you. > > Gennady. --~--~-~--~~~---~--~~ 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] Serialize ClauseLists
For our app we'd like the user to construct and save search queries
that we execute on the database. My first thought was to construct
something like:
predicate = and_(or_(item.c.id>5, item.c.id<3),
item.c.name.like('aap')))
And save this to a pickle column in the database. But it gives:
raise TypeError, "can't pickle %s objects" % base.__name__
TypeError: can't pickle module objects
I could write wrappers around or_ and and_ and the binaryexpressions
etc but I have the feeling there must be a better approach. Anyone got
a hint?
--~--~-~--~~~---~--~~
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: Cumulative Select
And again you made my day...
On Jul 24, 7:17 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> I had to put one little "trick" in here to make the subquery -
> work, which is something i should look into; otherwise it
> went straight in.
>
> from sqlalchemy import *
>
> transaction = table('transaction',
> column('id'),
> column('id_product'),
> column('price'),
> column('quantity')
> )
>
> f1 = transaction.alias('f1')
> f2 = transaction.alias('f2')
>
> subquery = select([func.coalesce(func.sum(f2.c.quantity))], and_
> (f2.c.id>f1.c.id, f2.c.id_product==bindparam('f2item')), scalar=True)
>
> s = select([f1.c.id, subquery.label('foo') - bindparam('offset').label
> ('offset')], and_(
> f1.c.id_product==bindparam('f1item'),
> bindparam('something') <= subquery
> ) , order_by=[desc(f1.c.id)], limit = 1
> )
>
> print s
>
> I think in 0.4 im going to deprecate "scalar=True" and instead have
> you sayselect(...).scalar()
>
> On Jul 24, 2007, at 9:38 AM, Koen Bok wrote:
>
>
>
> > I need to do a cumulative select on postgres, and I got it to work
> > with some extreme SQL query, but I was wondering if it could be done
> > more easily without having to drop to SQL but with SQLAlchemy
> > statements.
>
> > Let's say I have a table with stock transactions like this:
>
> > Transaction
> >id
> >id_product
> >price
> >quantity
>
> > And it is filled like this:
>
> > 1 1 12 10
> > 2 1 13 5
> > 3 1 12 3
> > 4 1 11 6
> > 5 1 10 5
>
> > Now at moment X my stock is 13 and I want to know the costs for each
> > product in my stock. So I add a cumulative column to select on and
> > expect to get the last three rows back as their cumulative total is <=
> > as my stock:
>
> >CUM
> > 1 1 12 10 29
> > 2 1 13 5 19
> > 3 1 12 3 14 this
> > 4 1 11 6 11 this
> > 5 1 10 5 5 and this...
>
> > Extra info:
>
> > This is the query I currently use to get the transaction ID and offset
> > back:
>
> > SELECT
> >f1.id,
> >(
> >SELECT
> >coalesce(sum(quantity), 0)
> >FROM transaction f2
> >WHERE f2.id>=f1.id
> >AND f2.id_item = %s
> >) - %s as offset
> > FROM
> >transaction f1
> > AND f1.id_item = %s
> > AND %s <= (
> >SELECT
> >coalesce(sum(quantity), 0)
> >FROM transaction f2
> >WHERE f2.id>=f1.id
> >AND f2.id_item = %s
> >)
> > ORDER BY f1.id DESC LIMIT 1
--~--~-~--~~~---~--~~
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] Cumulative Select
I need to do a cumulative select on postgres, and I got it to work with some extreme SQL query, but I was wondering if it could be done more easily without having to drop to SQL but with SQLAlchemy statements. Let's say I have a table with stock transactions like this: Transaction id id_product price quantity And it is filled like this: 1 1 12 10 2 1 13 5 3 1 12 3 4 1 11 6 5 1 10 5 Now at moment X my stock is 13 and I want to know the costs for each product in my stock. So I add a cumulative column to select on and expect to get the last three rows back as their cumulative total is <= as my stock: CUM 1 1 12 10 29 2 1 13 5 19 3 1 12 3 14 this 4 1 11 6 11 this 5 1 10 5 5 and this... Extra info: This is the query I currently use to get the transaction ID and offset back: SELECT f1.id, ( SELECT coalesce(sum(quantity), 0) FROM transaction f2 WHERE f2.id>=f1.id AND f2.id_item = %s ) - %s as offset FROM transaction f1 AND f1.id_item = %s AND %s <= ( SELECT coalesce(sum(quantity), 0) FROM transaction f2 WHERE f2.id>=f1.id AND f2.id_item = %s ) ORDER BY f1.id DESC LIMIT 1 --~--~-~--~~~---~--~~ 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: Many to many to same table
Thanks Michael, thats just too cool!
On Jul 20, 10:35 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> yup, just spell it out
>
> item_mapper = mapper(Item, item_table, properties=dict(
> collections=relation(Item, secondary=collection_table ,lazy=True,
> primaryjoin=item_table.c.id==collection_table.c.id_coll,
> secondaryjoin=item_table.c.id==collection_table.c.id_item,
> backref='items')))
>
> ...
>
> # do a more reasonable collection setup than the original test
>
> (koen, dirk) = session.query(Item).order_by(desc
> (item_table.c.name)).all()
>
> koen.collections.append(dirk)
> assert dirk in koen.collections
> assert koen in dirk.items
> session.flush()
> session.clear()
>
> (koen, dirk) = session.query(Item).order_by(desc
> (item_table.c.name)).all()
> assert dirk in koen.collections
> assert koen in dirk.items
>
> On Jul 20, 2007, at 4:00 PM, Koen Bok wrote:
>
> > from sqlalchemy import *
>
> > metadata = BoundMetaData('sqlite://')
>
> > class Item(object):
> >def __init__(self, name):
> >self.name = name
> >def __repr__(self):
> >return '<%s:%s>' % (self.__class__.__name__, self.name)
>
> > item_table = Table('item', metadata,
> >Column('id', Integer, primary_key=True),
> >Column('name', String(50)))
>
> > collection_table = Table('collection', metadata,
> >Column('id_coll', Integer, ForeignKey("item.id"), nullable=False),
> >Column('id_item', Integer, ForeignKey("item.id"), nullable=False))
>
> > item_mapper = mapper(Item, item_table, properties=dict(
> >collections=relation(Item, secondary=collection_table ,lazy=True,
> > backref='items')))
>
> > metadata.create_all()
>
> > session = create_session()
>
> > session.save(Item('Koen Bok'))
> > session.save(Item('Dirk Stoop'))
>
> > session.flush()
>
> > # And now we should be able to do something like this:
>
> > items = session.query(Item).select()
>
> > for item in items:
> >for i in items:
> >item.items.append(i)
> >item.collections.append(i)
--~--~-~--~~~---~--~~
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] Many to many to same table
Hello, I am trying to do something odd, but I was wondering if this
could be done with SQLAlchemy. See the script for details:
from sqlalchemy import *
metadata = BoundMetaData('sqlite://')
class Item(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return '<%s:%s>' % (self.__class__.__name__, self.name)
item_table = Table('item', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)))
collection_table = Table('collection', metadata,
Column('id_coll', Integer, ForeignKey("item.id"), nullable=False),
Column('id_item', Integer, ForeignKey("item.id"), nullable=False))
item_mapper = mapper(Item, item_table, properties=dict(
collections=relation(Item, secondary=collection_table ,lazy=True,
backref='items')))
metadata.create_all()
session = create_session()
session.save(Item('Koen Bok'))
session.save(Item('Dirk Stoop'))
session.flush()
# And now we should be able to do something like this:
items = session.query(Item).select()
for item in items:
for i in items:
item.items.append(i)
item.collections.append(i)
--~--~-~--~~~---~--~~
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: No update in cascade
And you were right. It turned out to be a stupid idea anyway. Let that be a lesson for the next programmer who tries to be lazy ;-) On Jul 7, 12:32 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Jul 6, 2007, at 5:50 PM, Koen Bok wrote: > > > > > I have a strange situation. If someone _really_ needs to know why I > > could explain. > > > When an object gets deleted by SQLAlchemy, it's related objects are > > getting updated setting their foreign keys referring to the deleted > > items to NULL. But what if I don't want that to happen? Viewonly is > > not an option here, as I need to be able to make changes. > > it wants to maintain referential integrity. if you werent using > SQLite, you'd get an error on any other database if it were not set > to NULL. that the foreign key value can store a value which doesnt > exist otherwise seems to imply youre setting it manually, in which > case "viewonly" could still work for you. we dont have an option > right now for it to populate foreign keys but to not clear them out > (also have gone this far without anyone *truly* needing it). --~--~-~--~~~---~--~~ 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] No update in cascade
I have a strange situation. If someone _really_ needs to know why I could explain. When an object gets deleted by SQLAlchemy, it's related objects are getting updated setting their foreign keys referring to the deleted items to NULL. But what if I don't want that to happen? Viewonly is not an option here, as I need to be able to make changes. Example Moment x: Creating item Moment x+1: Creating note Moment x+2: Deleting item But I want it still to be: --~--~-~--~~~---~--~~ 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: GRANT weirdness.
Yep that's it.
If anyone has the same just do this:
engine.execute('BEGIN;GRANT SELECT ON TABLE checkout_info TO
testuser;COMMIT;')
Ciao
Koen
On Apr 29, 4:51 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Apr 29, 2007, at 9:28 AM, Koen Bok wrote:
>
>
>
> > I want to grant permissions through sqlalchemy, but for some reason if
> > I let an engine execute the sql it does not work:
>
> > Code:
>
> > from sqlalchemy import *
>
> > engine = create_engine('postgres://localhost/Test')
> > engine.echo = True
>
> > engine.execute('CREATE USER testuser;')
> > engine.execute('GRANT SELECT ON TABLE testtable TO testuser;')
>
> my only guess is that the GRANT needs a "COMMIT" issued afterwards
> (which is a postgres weirdness in itself, that schema changes are
> part of transactions). SA's autocommit logic is based on the
> statement text containing INSERT, UPDATE, ALTER etc. but i dont think
> GRANT is in there. so use an explicit connection/transaction for now.
--~--~-~--~~~---~--~~
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: GRANT weirdness.
And ofcourse the first result should be this in the above mail:
> Test=# \z testtable
>Access privileges for database "Test"
> Schema | Name | Type | Access privileges
> +---+---+
> public | testtable | table | {koen=arwdxt/koen}
> (1 row)
--~--~-~--~~~---~--~~
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] GRANT weirdness.
I want to grant permissions through sqlalchemy, but for some reason if
I let an engine execute the sql it does not work:
Code:
from sqlalchemy import *
engine = create_engine('postgres://localhost/Test')
engine.echo = True
engine.execute('CREATE USER testuser;')
engine.execute('GRANT SELECT ON TABLE testtable TO testuser;')
Result:
Test=# \z testtable
Access privileges for database "Test"
Schema | Name | Type | Access privileges
+---+---+
public | checkout_info | table | {koen=arwdxt/koen}
(1 row)
And when I do the exact same sql in psql
Test=# CREATE USER testuser;
CREATE ROLE
Test=# GRANT SELECT ON TABLE testtable TO testuser;
GRANT
Test=# \z testtable
Access privileges for database "Test"
Schema | Name | Type | Access privileges
+---+---+
public | Test | table | {koen=arwdxt/koen,testuser=r/koen}
(1 row)
I never had any problems doing any other sql stuff like creating
triggers etc with this. Anyone got a hint?
Thanks,
Koen
--~--~-~--~~~---~--~~
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: Get URL from engine
That would be very nice. You have my vote. Koen On Apr 29, 5:13 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 28, 2007, at 10:34 AM, Koen Bok wrote: > > > > > Is there a nice way to get the URL from a given engine? > > not at the moment...the url goes into a create function and gets > thrown away. have been meaning to just connect "url" to "engine" > when the engine is created so its available as engine.url. --~--~-~--~~~---~--~~ 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] Get URL from engine
Is there a nice way to get the URL from a given engine? Thanks, Koen --~--~-~--~~~---~--~~ 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] Catching username password errors
I am trying to catch username password errors to show in the
interface. But somehow I cannot catch the exeption. I wrap everything
in a try except, but the exeption is still throwed and my app stops
functioning. What am I doing wrong?
Koen
loginInfo = (
self.username,
self.password,
store.server.address,
store.server.port,
store.tableName.replace(' ', '\ '))
engineURL = 'postgres://%s:[EMAIL PROTECTED]:%s/%s' % loginInfo
"""
try:
engine = create_engine(engineURL)
try:
c = engine.connect()
c.close()
finally:
d.dispose()
except:
self.showError(UserPasswordError)
return False
else:
self.showError(None)
"""
# Make the actual connection here, we should improve this
try:
engine.connect(engineURL)
result = engine.execute(text('SELECT NOW()'))
except:
engine.dispose()
self.showError(UserPasswordError)
return False
--~--~-~--~~~---~--~~
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] Postgres user management and SQLAlchemy
I wondered if it was possible to manage users inside postgres with the
help of SQLAlchemy. But I guess Postgres users are special objects and
not just rows in a table. I tried to do this, but it did not work.
from sqlalchemy import *
metadata = BoundMetaData('postgres://127.0.0.1/template1')
pg_authid = Table('pg_authid', metadata, autoload=True)
pg_auth_members = Table('pg_auth_members', metadata, autoload=True)
For some reason, I get a key error back. Would this be possible at
all?
Koen
--~--~-~--~~~---~--~~
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: Find out which objects were deleted
Yep, and I want to know them after the flush :-) On Apr 15, 7:11 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > session has a "deleted" accessor with all items marked as deleted. > (its cleared out on flush()). > > On Apr 15, 2007, at 11:20 AM, Koen Bok wrote: > > > > > Is there a nice way to find out if an object has been deleted? > > Ofcourse I could set a deleted = False on every mapped object and > > subclass sqlalchemy's session object to set it to true when a delete() > > is called on an object. But isn't there a less complicated way? > > > Koen --~--~-~--~~~---~--~~ 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] Find out which objects were deleted
Is there a nice way to find out if an object has been deleted? Ofcourse I could set a deleted = False on every mapped object and subclass sqlalchemy's session object to set it to true when a delete() is called on an object. But isn't there a less complicated way? Koen --~--~-~--~~~---~--~~ 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
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] Column to default to itself
I need to have a uninterrupted number sequence in my table for
invoices. I was trying to do it like this, but I can't get it to work.
Can anyone give me a hint?
request_table = Table('request', metadata,
Column('id', Integer, primary_key=True),
Column('number', Integer, unique=True, nullable=True))
request_table.c.number.default =
default=func.coalesce(func.max(request_table.c.number), 0).op('+')(1)
--~--~-~--~~~---~--~~
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] About threadlocal strategy
I'm trying to wrap my head around the threadlocal plugin to decide if we need it for our app. We are building a GUI point of sale system. Until now I have done everything in sessions, basically in one global shared session. I have read all of the documentation about the threadlocal strategy but I still cannot decide if we should use it. Basically we hoped that someone could compare the threadlocal strategy to the default strategy and some examples in what kind of apps you would use it. Thanks, Koen @ Sofa --~--~-~--~~~---~--~~ 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: Putting Session.flush in a seperate thread
> the elegant way would be to not worry about the thread scope of a > connection and just use connection pooling normally. pull out a > connection when needed, close it when complete (which is really just > a return to the connection pool). keep the scope of those two > operations local to a single thread. This is exactly what I'd like to do. But I have no clue to assign a new connection to a session in a new thread. This is what I'm doing. class FlushThread(Thread): def __init__(self, session): super(FlushThread, self).__init__() self.session = session self.start() def run(self): print self.session #engine.connect() #self.session.bind_to(engine) self.session.flush() --~--~-~--~~~---~--~~ 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] Putting Session.flush in a seperate thread
We are building a GUI app, and we were thinking about wrapping session.flush() in a thread with a timer that detects a timeout. That way we would have better performace and we can generate warnings if the connection goes down. Do you guys think this is smart, or are there complications? I tried to build this already but I cannot figure out how to assign the shared connection to a thread. I always get 'No connection defined'. --~--~-~--~~~---~--~~ 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: sqlalchemy.org down?
I posted a PDF of the documentation here in case someone needs it. Koen On Mar 29, 11:53 am, "Jonathan Ballet" <[EMAIL PROTECTED]> wrote: > On 29 mar, 11:24, "Arnar Birgisson" <[EMAIL PROTECTED]> wrote: > > > I can't reachwww.sqlalchemy.org, anyone else having problems? > > > Arnar > > Same here :( > > - Jonathan --~--~-~--~~~---~--~~ 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: Changing lazy property for primary mapper on the fly
Ok, thanks! On Mar 26, 4:26 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > no. do a clear_mappers() and build your mappers again if you need to > change the base configuration. > > On Mar 25, 2007, at 3:35 PM, Koen Bok wrote: > > > > > I get this, but that's only on a particular query object. That makes > > sense, but is there no way to 'globally' set this in the mapper? > > > Koen > > > On Mar 25, 4:51 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > >>http://www.sqlalchemy.org/docs/ > >> datamapping.html#datamapping_selectrelations_options > > >> On Mar 25, 2007, at 6:50 AM, Koen Bok wrote: > > >>> Is it possible to change the lazy property of a primary mapper of an > >>> object's relation on the fly so from then on it wil change the eager > >>> loading of that relation. > > >>> I want to use this for a small debug window in my app where I can > >>> change these on the fly to test which settings are optimal for the > >>> connection. > > >>> Koen --~--~-~--~~~---~--~~ 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: Mapper with custom outerjoin and sum functions
Thanks! That did the trick!
On Mar 26, 4:33 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Mar 25, 2007, at 6:17 PM, Koen Bok wrote:
>
> > stock_request = select(
> >[stock_request_join] + \
> >[stock_table.c.quantity.op('-')
> > (func.sum(request_table.c.quantity)).label('unordered')] + \
> >[stock_table.c.quantity.op('-')
> > (func.sum(request_table.c.allocation)).label('unallocated')],
> >group_by=[c for c in stock_request_join.c],
> >correlate=False).alias('stock_request')
>
> > This generates the right SQL. But I can't get it to work with the
> > original column names, then I get name ambigious errors.
>
> dont know what a name ambiguous error is (usually thats thrown by
> postgres directly, meaning the SQL *is* wrong).
>
> > When I do it
> > with the joined names, the mapper tries to insert a request too when I
> > want to create stock which makes kind of sense.
>
> > When I remove [stock_request_join] and insert [c for c in
> > stock_request_join.c] the JOIN syntax is being removed and it does a
> > regular select against request and stock.
>
> > Basically I want to end up with a regular Stock object with the
> > aggregrate columns request_table.c.quantity and
> > request_table.c.allocation. I have it working with a regular select,
> > but if there is no request in te table linked to a stock it does not
> > return that stock when doing a session.query(Stock).select(). Left
> > outer joining request would do the trick.
>
> select([stock_table, request_table.c.quantity,
> request_table.c.allocation], from_obj=[stock_table.outerjoin
> (request_table)]) ?
--~--~-~--~~~---~--~~
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] Mapper with custom outerjoin and sum functions
Hello again!
I need a mapper that does an outer join on another table.
stock_request_join = sql.outerjoin(request_table, stock_table, and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype))
stock_request = select(
[stock_request_join] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.quantity)).label('unordered')] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.allocation)).label('unallocated')],
group_by=[c for c in stock_request_join.c],
correlate=False).alias('stock_request')
This generates the right SQL. But I can't get it to work with the
original column names, then I get name ambigious errors. When I do it
with the joined names, the mapper tries to insert a request too when I
want to create stock which makes kind of sense.
When I remove [stock_request_join] and insert [c for c in
stock_request_join.c] the JOIN syntax is being removed and it does a
regular select against request and stock.
Basically I want to end up with a regular Stock object with the
aggregrate columns request_table.c.quantity and
request_table.c.allocation. I have it working with a regular select,
but if there is no request in te table linked to a stock it does not
return that stock when doing a session.query(Stock).select(). Left
outer joining request would do the trick.
Thanks in advance!
Koen
--~--~-~--~~~---~--~~
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: Changing lazy property for primary mapper on the fly
I get this, but that's only on a particular query object. That makes sense, but is there no way to 'globally' set this in the mapper? Koen On Mar 25, 4:51 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > http://www.sqlalchemy.org/docs/ > datamapping.html#datamapping_selectrelations_options > > On Mar 25, 2007, at 6:50 AM, Koen Bok wrote: > > > > > Is it possible to change the lazy property of a primary mapper of an > > object's relation on the fly so from then on it wil change the eager > > loading of that relation. > > > I want to use this for a small debug window in my app where I can > > change these on the fly to test which settings are optimal for the > > connection. > > > Koen --~--~-~--~~~---~--~~ 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] Changing lazy property for primary mapper on the fly
Is it possible to change the lazy property of a primary mapper of an object's relation on the fly so from then on it wil change the eager loading of that relation. I want to use this for a small debug window in my app where I can change these on the fly to test which settings are optimal for the connection. Koen --~--~-~--~~~---~--~~ 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] Unwanted Cascaded Deletes
I have a mapper like this:
mapper(Request, request_table, properties={
[SOME MORE STUFF]
'stock': relation(Stock, lazy=True, uselist=False,
primaryjoin=and_(
request_table.c.id_item==stock_request.c.id_product,
request_table.c.id_location==stock_request.c.id_location,
request_table.c.id_stocktype==stock_request.c.id_stocktype),
foreign_keys=[request_table.c.id_item,
request_table.c.id_location,
request_table.c.id_stocktype])})
Whenever I remove a request object that has a stock object associated,
it deletes the stock object too. When I change the foreign_keys to
stock_request.c.id_product, stock_request.c.id_location,
stock_request.c.id_stocktype it tries to update the related object
with NULL values.
But I want it to not do anyithing at all! I played with the cascade
rules, but they all either delete or update.
Thanks!
Koen
--~--~-~--~~~---~--~~
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: Eager loading self referential mapper
Yeah, I am sure the tree will never be deeper than 3 or 4 levels. Koen On Mar 23, 5:24 pm, Andreas Jung <[EMAIL PROTECTED]> wrote: > --On 23. März 2007 16:14:26 +0000 Koen Bok <[EMAIL PROTECTED]> wrote: > > > > > Could anyone please explain me a little why self referantial mappers > > cannot be eager-loading. Is this not yet integrated in SQLAlchemy or > > theoratically impossible or impractical? > > Do you really want to load a possibly *huge* tree using eager loading? > > -aj > > application_pgp-signature_part > 1KDownload --~--~-~--~~~---~--~~ 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] Eager loading self referential mapper
Could anyone please explain me a little why self referantial mappers cannot be eager-loading. Is this not yet integrated in SQLAlchemy or theoratically impossible or impractical? Ciao Koen --~--~-~--~~~---~--~~ 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: Using mapper with custom select creates unneeded subquery
It worked! Thanks a bunch!
On Mar 22, 5:06 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> clearly "correlate=False" has to be more prominently mentioned, not
> just in the FAQ but in the main docs, there should be a section
> specifically on subqueries and their mechanics.
>
> On Mar 22, 2007, at 6:53 AM, King Simon-NFHD78 wrote:
>
>
>
> > This caught me out a couple of weeks ago, and I've seen a couple of
> > other similar questions as well. You need to add 'correlate=False' to
> > the nested select.
>
> > I wonder if this should be added to the FAQ?
>
> > Hope that helps,
>
> > Simon
>
> > -----Original Message-
> > From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
> > On Behalf Of Koen Bok
> > Sent: 22 March 2007 10:47
> > To: sqlalchemy
> > Subject: [sqlalchemy] Re: Using mapper with custom select creates
> > unneeded subquery
>
> > Let me post some sample code with that:
>
> > mapper(Request, request_table, properties={
> >'children' : relation(
> >Request,
>
> > primaryjoin=request_table.c.id_parent==request_table.c.id,
> >backref=backref("parent",
> > remote_side=[request_table.c.id])),
> >'i': relation(Item,
> > primaryjoin=item_table.c.id==request_table.c.id_item,
> > backref='requests', lazy=True),
> >[SOME MORE STUFF]
> >'stock': relation(Stock, primaryjoin=and_(
>
> > request_table.c.id_item==stock_table.c.id_product,
>
> > request_table.c.id_location==stock_table.c.id_location,
>
> > request_table.c.id_stocktype==stock_table.c.id_stocktype),
> >foreign_keys=[stock_table.c.id_product,
> > stock_table.c.id_location,
> > stock_table.c.id_stocktype])})
>
> > stock_request = select(
> >[c for c in stock_table.c] + \
> >[stock_table.c.quantity.op('-')
> > (func.sum(request_table.c.quantity)).label('unordered')] + \
> >[stock_table.c.quantity.op('-')
> > (func.sum(request_table.c.allocation)).label('unallocated')],
> >and_(
> >request_table.c.id_item==stock_table.c.id_product,
> >request_table.c.id_location==stock_table.c.id_location,
>
> > request_table.c.id_stocktype==stock_table.c.id_stocktype),
> >group_by=[c for c in stock_table.c]).alias('stock_request')
>
> > mapper(Stock, stock_request, properties={
> >'product': relation(Item,
> > primaryjoin=item_table.c.id==stock_table.c.id_product,
> > backref='_stock'),
> >'location': relation(Item,
> > primaryjoin=item_table.c.id==stock_table.c.id_location),
> >'stocktype': relation(StockType)})
>
> > If you need more, just let me know!
>
> > Koen
>
> > On Mar 22, 11:42 am, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> >> Thanks for the reply! If the performance is about equal, that's fine!
>
> >> But I think I might have found a bug.
>
> >> When I make a selection it generates the following (faulty) SQL
> >> query:
>
> >> SELECT
> >> stock_request.id_stocktype AS stock_request_id_stocktype,
> >> stock_request.unordered AS stock_request_unordered,
> >> stock_request.id_location AS stock_request_id_location,
> >> stock_request.id_product AS stock_request_id_product,
> >> stock_request.unallocated AS stock_request_unallocated,
> >> stock_request.quantity AS stock_request_quantity,
> >> stock_request.id AS stock_request_id FROM
> >> (
> >> SELECT
> >> stock.id AS id,
> >> stock.id_stocktype AS id_stocktype,
> >> stock.id_product AS id_product,
> >> stock.id_location AS id_location,
> >> stock.quantity AS quantity,
> >> (stock.quantity - sum(request.quantity)) AS
> >> unordered,
> >> (stock.quantity - sum(request.allocation)) AS
> > unallocated
> >> FROM request
> >> WHERE
> >> request.id_item = stock.id_product
> >> AND
> >> request.id_location = stock.id_location
> >> AND
> >> request.id_stocktype = stock.id_stocktype
> >> GROUP BY
> >> stock.id
[sqlalchemy] Re: Using mapper with custom select creates unneeded subquery
Let me post some sample code with that:
mapper(Request, request_table, properties={
'children' : relation(
Request,
primaryjoin=request_table.c.id_parent==request_table.c.id,
backref=backref("parent",
remote_side=[request_table.c.id])),
'i': relation(Item,
primaryjoin=item_table.c.id==request_table.c.id_item,
backref='requests', lazy=True),
[SOME MORE STUFF]
'stock': relation(Stock, primaryjoin=and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
foreign_keys=[stock_table.c.id_product,
stock_table.c.id_location,
stock_table.c.id_stocktype])})
stock_request = select(
[c for c in stock_table.c] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.quantity)).label('unordered')] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.allocation)).label('unallocated')],
and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
group_by=[c for c in stock_table.c]).alias('stock_request')
mapper(Stock, stock_request, properties={
'product': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_product,
backref='_stock'),
'location': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_location),
'stocktype': relation(StockType)})
If you need more, just let me know!
Koen
On Mar 22, 11:42 am, "Koen Bok" <[EMAIL PROTECTED]> wrote:
> Thanks for the reply! If the performance is about equal, that's fine!
>
> But I think I might have found a bug.
>
> When I make a selection it generates the following (faulty) SQL query:
>
> SELECT
> stock_request.id_stocktype AS stock_request_id_stocktype,
> stock_request.unordered AS stock_request_unordered,
> stock_request.id_location AS stock_request_id_location,
> stock_request.id_product AS stock_request_id_product,
> stock_request.unallocated AS stock_request_unallocated,
> stock_request.quantity AS stock_request_quantity,
> stock_request.id AS stock_request_id
> FROM
> (
> SELECT
> stock.id AS id,
> stock.id_stocktype AS id_stocktype,
> stock.id_product AS id_product,
> stock.id_location AS id_location,
> stock.quantity AS quantity,
> (stock.quantity - sum(request.quantity)) AS unordered,
> (stock.quantity - sum(request.allocation)) AS unallocated
> FROM request
> WHERE
> request.id_item = stock.id_product
> AND
> request.id_location = stock.id_location
> AND
> request.id_stocktype = stock.id_stocktype
> GROUP BY
> stock.id,
> stock.id_stocktype,
> stock.id_product,
> stock.id_location,
> stock.quantity,
> stock.quantity
> ) AS stock_request, stock
> WHERE
> stock.id_product = 5
> AND
> stock.id_location = 7
> AND
> stock.id_stocktype = 1
> ORDER BY
> stock_request.id
> LIMIT 1
>
> The FROM in the subquery should be: FROM request, stock
>
> The strange thing is that whenever I print the subquery's sql, it has
> stock in the FROM and tehrefore is correct.
>
> Or am I not understanding it right?
>
> Koen
>
> On Mar 22, 2:58 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> > when you pass a selectable to the mapper, the mapper considers that
> > selectable to be encapsulated, in the same way as a table is. the
> > Query cannot add any extra criterion to that selectable directly
> > since it would modify the results and corrupt the meaning, if not the
> > actual syntax, of the selectable itself. therefore the mapper is
> > always going to select * from (your selectable) - its the only way to
> > guarantee the correct results.
>
> > the queries it generates, i.e. select * from (select * from ...))
> > will be optimized by the database's optimizer in most cases and
> > should not add any overhead to your application.
>
> > On Mar 21,
[sqlalchemy] Re: Using mapper with custom select creates unneeded subquery
Thanks for the reply! If the performance is about equal, that's fine!
But I think I might have found a bug.
When I make a selection it generates the following (faulty) SQL query:
SELECT
stock_request.id_stocktype AS stock_request_id_stocktype,
stock_request.unordered AS stock_request_unordered,
stock_request.id_location AS stock_request_id_location,
stock_request.id_product AS stock_request_id_product,
stock_request.unallocated AS stock_request_unallocated,
stock_request.quantity AS stock_request_quantity,
stock_request.id AS stock_request_id
FROM
(
SELECT
stock.id AS id,
stock.id_stocktype AS id_stocktype,
stock.id_product AS id_product,
stock.id_location AS id_location,
stock.quantity AS quantity,
(stock.quantity - sum(request.quantity)) AS unordered,
(stock.quantity - sum(request.allocation)) AS unallocated
FROM request
WHERE
request.id_item = stock.id_product
AND
request.id_location = stock.id_location
AND
request.id_stocktype = stock.id_stocktype
GROUP BY
stock.id,
stock.id_stocktype,
stock.id_product,
stock.id_location,
stock.quantity,
stock.quantity
) AS stock_request, stock
WHERE
stock.id_product = 5
AND
stock.id_location = 7
AND
stock.id_stocktype = 1
ORDER BY
stock_request.id
LIMIT 1
The FROM in the subquery should be: FROM request, stock
The strange thing is that whenever I print the subquery's sql, it has
stock in the FROM and tehrefore is correct.
Or am I not understanding it right?
Koen
On Mar 22, 2:58 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> when you pass a selectable to the mapper, the mapper considers that
> selectable to be encapsulated, in the same way as a table is. the
> Query cannot add any extra criterion to that selectable directly
> since it would modify the results and corrupt the meaning, if not the
> actual syntax, of the selectable itself. therefore the mapper is
> always going to select * from (your selectable) - its the only way to
> guarantee the correct results.
>
> the queries it generates, i.e. select * from (select * from ...))
> will be optimized by the database's optimizer in most cases and
> should not add any overhead to your application.
>
> On Mar 21, 2007, at 8:08 PM, Koen Bok wrote:
>
>
>
> > My mapper looks like this:
>
> > stock_unreserved = select(
> >[stock_table] + \
> >[stock_table.c.quantity.op('-')
> > (func.sum(request_table.c.quantity)).label('unordered')] + \
> >[stock_table.c.quantity.op('-')
> > (func.sum(request_table.c.allocation)).label('unallocated')],
> >and_(
> >request_table.c.id_item==stock_table.c.id_product,
> >request_table.c.id_location==stock_table.c.id_location,
> >request_table.c.id_stocktype==stock_table.c.id_stocktype),
> >group_by=[c for c in stock_table.c]).alias('stock_unreserved')
>
> > mapper(Stock, stock_unreserved, properties={
> >'product': relation(Item,
> > primaryjoin=item_table.c.id==stock_table.c.id_product,
> > backref='_stock'),
> >'location': relation(Item,
> > primaryjoin=item_table.c.id==stock_table.c.id_location),
> >'stocktype': relation(StockType)})
>
> > Whenever I try to select an object through the mapper I would think it
> > would use the SQL from stock_unreserved which is:
>
> > SELECT
> >stock.id,
> >stock.id_stocktype,
> >stock.id_product,
> >stock.id_location,
> >stock.quantity, (stock.quantity - sum(request.quantity)) AS
> > unordered,
> >(stock.quantity - sum(request.allocation)) AS unallocated
> > FROM stock, request
> > WHERE request.id_item = stock.id_product
> > AND request.id_location = stock.id_location
> > AND request.id_stocktype = stock.id_stocktype
> > GROUP BY stock.id, stock.id_stocktype, stock.id_product,
> > stock.id_location, stock.quantity
>
> > Selecting all objects by a plain select() on the mapper works great!
> > But when I make a selection it does a subquery on all the results eg:
>
> > SELECT * FROM (SELECT * FROM stock_unreserved) WHERE selection
> > criteria
>
> > But I want it to append it to the other selection criteria without
> > doing a subselect eg:
>
> > SELECT * FROM stock_unreserved WERE ... AND ... + extra selection
&g
[sqlalchemy] Using mapper with custom select creates unneeded subquery
My mapper looks like this:
stock_unreserved = select(
[stock_table] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.quantity)).label('unordered')] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.allocation)).label('unallocated')],
and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
group_by=[c for c in stock_table.c]).alias('stock_unreserved')
mapper(Stock, stock_unreserved, properties={
'product': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_product,
backref='_stock'),
'location': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_location),
'stocktype': relation(StockType)})
Whenever I try to select an object through the mapper I would think it
would use the SQL from stock_unreserved which is:
SELECT
stock.id,
stock.id_stocktype,
stock.id_product,
stock.id_location,
stock.quantity, (stock.quantity - sum(request.quantity)) AS
unordered,
(stock.quantity - sum(request.allocation)) AS unallocated
FROM stock, request
WHERE request.id_item = stock.id_product
AND request.id_location = stock.id_location
AND request.id_stocktype = stock.id_stocktype
GROUP BY stock.id, stock.id_stocktype, stock.id_product,
stock.id_location, stock.quantity
Selecting all objects by a plain select() on the mapper works great!
But when I make a selection it does a subquery on all the results eg:
SELECT * FROM (SELECT * FROM stock_unreserved) WHERE selection
criteria
But I want it to append it to the other selection criteria without
doing a subselect eg:
SELECT * FROM stock_unreserved WERE ... AND ... + extra selection
criteria
Is this possible at all?
Many thanks!
Koen
--~--~-~--~~~---~--~~
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] Complicated Mapper with Count Function
Dear all.
I have to make a complicated mapper, and I need a little help.
We have a list of products. These products have stock, each individual
stock item has an entry in the stockproduct table. So to get the total
stock we need to count the items in the stock database. We can filter
them by a particular stock.
So I made a function to create a mapper to do just that. But there are
two problems:
- It's not working well, because if the count function equals 0 (no
stock) the product does not appear in the results.
- I have the feeling this can be better optimized, but I can't see it
(maybe put it in a join or subquery?)
The function
def productStockMapper(stockList):
or_list = or_()
for stock in stockList:
or_list.append(stockproduct_table.c.stock_id==stock.id)
s = select([product_table,
func.count(stockproduct_table.c.id).label('stock')],
and_(
stockproduct_table.c.product_id==product_table.c.id,
or_list),
group_by=[c for c in product_table.c]).alias('count_select')
return mapper(Product, s, non_primary=True)
The tables:
product_table = Table('products', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False)
Column('code', Unicode(255), unique=True, nullable=False))
stockproduct_table = Table('stockproducts', metadata,
Column('id', Integer, primary_key=True),
Column('stock_id', Integer, ForeignKey("stocks.id"), nullable=False),
Column('product_id', Integer, ForeignKey("products.id"),
nullable=False))
stock_table = Table('stocks', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False))
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
