[sqlalchemy] multiple columns in correlated subquery?
In Oracle, I would like to perform a correlated subquery where
multiple columns are specified in the set clause and those columns are
selected by the subquery. For example:
UPDATE table1 a
SET (a.column1, a.column2) = (SELECT b.column1, b.column2 FROM table2
b WHERE a.id=b.id)
WHERE a.group=:group AND exists(select 1 where a.id=b.id);
Is this possible using sqlalchemy core? I have tried a few variations,
but nothing seems to do quite what I'm looking for. In the meantime
I've been doing the equivalent of:
UPDATE table1 a SET
a.column1 = (SELECT a.column1 FROM table2 b WHERE a.id=b.id),
a.column2 = (SELECT a.column2 FROM table2 b WHERE a.id=b.id)
WHERE a.group=:group AND exists(select 1 where a.id=b.id);
With something like this:
upd = table1.update()
upd = upd.where(table1.group == group)
upd = upd.where(exists(select([1], table1.c.id == table2.c.id)))
for col in table2.columns:
query = select([col])
query = query.where(table2.c.id == table1.c.id)
upd = upd.values({col.name: query})
I am interested in doing this as it would produce far fewer sub-
queries, and hopefully this would show a benefit in performance.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Overriding reflected columns in SqlSoup?
Hi Neil
I managed to make queries on those tables by creating a new table
definition and getting a class mapping from that explicitly, for
example:
from sqlalchemy.ext.sqlsoup import SqlSoup
from sqlalchemy import *
engine = create_engine('sqlite:///:memory:')
metadata = MetaData(bind=engine)
retailer_table = Table('retailer', metadata, Column('retailer_id',
primary_key=True), autoload=True)
db = SqlSoup(metadata)
Retailer = db.map(retailer_table)
Retailer.first()
MappedRetailer(retailer_id=33199, ...)
So then the Retailer class returns MappedRetailer instances even
though the underlying schema defines no primary key. That part seemed
fine, but I then had to work with relations on those tables that I was
mapping explicitly and I found it easier at that point just to skip
SqlSoup and define the table metadata and mapping myself.
I hope that helps.
Stephen Emslie
On Sun, Apr 26, 2009 at 11:48 PM, NeilK neilku...@gmail.com wrote:
Hi Stephen, did you find a way to access those tables without a
primary key using SqlSoup?
Thanks,
-neil
On Apr 25, 3:42 am, Stephen Emslie stephenems...@gmail.com wrote:
I am using SqlSoup to do a little maintenance on a database whose
schema I have no control over. Unfortunately some tables are without a
primary key, and thus SqlSoup complains when accessing them:
sqlalchemy.ext.sqlsoup.PKNotFoundError: table 'category' does not have
a primary key defined
When reflecting tables directly with sqlalchemy, using Table(name,
meta, autoload=True), one can override the reflected columns to
compensate for the lack of a primary key. Is this possible in SqlSoup?
Stephen Emslie
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Overriding reflected columns in SqlSoup?
I am using SqlSoup to do a little maintenance on a database whose schema I have no control over. Unfortunately some tables are without a primary key, and thus SqlSoup complains when accessing them: sqlalchemy.ext.sqlsoup.PKNotFoundError: table 'category' does not have a primary key defined When reflecting tables directly with sqlalchemy, using Table(name, meta, autoload=True), one can override the reflected columns to compensate for the lack of a primary key. Is this possible in SqlSoup? Stephen Emslie --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to map columns into a tuple using SQLAlchemy?
You could try changing your _limit tuple to a property on the class that returns the tuple you want. For example: class Result(object): def get_limit(self): return (self.upper, self.lower, self.nominal) _limit = property(get_limit) Is this what you were looking for? Stephen Emslie On Wed, Mar 11, 2009 at 1:01 AM, batraone r...@kaos.stanford.edu wrote: Hi, I'm just starting to use SQLAlchemy and hit a roadblock. I have a class Result which contains a tuple called _limits. _limits = (Upper value, lower value, nominal) I would like to map a the table's columns directly into this tuple. From the documentation, all I can see is columns mapping directly to python attributes (I.e. Result.upper_value, Result.lower_value,..). Is there a way to map the three columns directly into the tuple? I do not want to modify the Result class and therefore cannot create it as composite column type. I'm hoping there is a syntax that states map these 3 columns into this tuple via the mapper. Thanks, Raj --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: getting the number of records in a result set from select
Well, I would have expected ResultProxy.rowcount to do just that (return the number of rows in the last executed statement) but I just get 0 from it. Perhaps someone could explain how to use it correctly. Stephen Emslie On Thu, Mar 12, 2009 at 5:20 PM, jeff jeffre...@gmail.com wrote: hi. this question should be easy. i've searched around though and haven't found the answer. all i want to do is know the number of records in a result set i get using an execute statement with a simple select. so if i do: s=select([raw_table],and_(raw_table.c.name==m ['name'],raw_table.c.as_of=i['first_time'])) rec_list=conn.execute(s) is there a simple way to get back the number of records that exist in rec_list? thanks, i'm sure it's simple and i missed something. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy Migrate
It is always good to see some activity on this front. sqlalchemy-migrate seems to be a good idea that needs more activity. Perhaps try contributing to that project before branching. Any comment from the sqlalchemy-migrate developers? Stephen On Fri, Mar 6, 2009 at 3:13 PM, J. Cliff Dyer j...@sdf.lonestar.org wrote: On Thu, 2009-03-05 at 01:03 -0800, jarrod.ches...@gmail.com wrote: Hi All I'm writing a metadata based schema migration tool. As SQLAlchemy doesn't support much schema modification. I will implement a complete set of schema migration functions one way or another for several of the SQLAlchemy supported databases. My question is, Where should these function reside? Options are : 1) Branch SQLAlchemy and make the modifications 2) Branch SQLAlchemy Migrate and make the modifications 3) Write the code and not distribute it cause no one cares. 4) ? Suggestions ? PS, Thanks Michael Bayer, SQLAlchemy is great. I suggest adding the functionality to SQLAlchemy-migrate. Cheers, Cliff --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Cascading delete
Hi Bob Looks like you're doing some fun thinking :) Steve On Thu, Aug 21, 2008 at 11:04 AM, Bob Farrell [EMAIL PROTECTED] wrote: Hi there, I spoke with zzzeek_ on IRC yesterday re: some code I'd written for an introspective cascading delete function. We were previously using the ORM to do this for us but, due to the way it works, it was taking several minutes to delete large amounts of second-generation orphans. The code I've written recursively gets all the tables involved, analyses the relationships and issues as few delete statements as possible (I hope). I've attached the code, or there's a link to it here: http://paste.pocoo.org/show/82878/ With parent-child relationships my tests are showing this working much, much faster than the ORM (by a factor of at least 100x). I haven't had a chance to set up more complicated tables to fully test the recursive aspect of it but it has worked with what I've given it so far. I'm really enjoying working with SQLAlchemy, you guys have done a really good job. If you think there's room for something like this in SA then it's all yours. :-) Cheers, -- -- Bob Farrell pH, an Experian Company www.phgroup.com Office Line: 020 7598 0310 Fax: 020 7598 0311 -- --~--~-~--~~~---~--~~ 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: path based options and contains_eager
That was quick! contains_eager with aliases is now giving me exactly what I wanted, which is an eager-loaded subset of the tree at each level. That is going to make a really big difference to my project. Thank you! I'm still seeing a cycle develop with the unaliased options, but I think that might actually be the correct behaviour here. Stephen Emslie On Nov 18, 2007 4:20 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 16, 2007, at 12:29 PM, stephen emslie wrote: I'm a bit confused now, so please tell me if I've got something fundamentally wrong here, otherwise are these bugs? I've got these both fixed with some test coverage in rev 3791...thanks for spotting these ! --~--~-~--~~~---~--~~ 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] path based options and contains_eager
I noticed that the path_based_options branch has been merged, which
looks great as a way to eager-load deep relationships in queries built
with from statements (ie: query.from_statement). I noticed some
strange behavior and I want to check that I've got the right idea
before I open any tickets:
Both of them come from using contains_eager with a path. After setting
up a mapping similar to the AdjacencyTree example in trunk I'm joining
to the same table twice with aliases like this (This doesn't really
show why I'm doing this in the first place, but it should illustrate
the problem):
ali = trees.alias()
ali2 = trees.alias()
statement = trees.outerjoin(ali, trees.c.node_id==ali.c.parent_node_id)
statement = statement.outerjoin(ali2, ali.c.node_id==ali2.c.parent_node_id)
statement =
statement.select(use_labels=True).where(trees.c.parent_node_id==None)
query = create_session().query(TreeNode).from_statement(statement)
So I have a query created from the statement, which should be the
table joined to itself twice. Now if I set two contains_eager options
using the new path behaviour:
root = query.options(contains_eager('children'),
contains_eager('children.children')).one()
(Pdb) root.children
{u'rootnode': rootnode (1,None, 139201708)
}
(Pdb) root.children['rootnode'].children
{u'rootnode': rootnode (1,None, 139201708)
}
(Pdb) root.children['rootnode'].children['rootnode'].children
{u'rootnode': rootnode (1,None, 139201708)
}
This looks like root thinks that it is it's own child. It might be
that I need to use an alias with the contains_eager option, but if I
do:
root = query.options(contains_eager('children', alias=ali),
contains_eager('children.children', alias=ali2)).one()
Traceback (most recent call last):
File basic_tree.py, line 88, in module
root = query.options(contains_eager('children', alias=ali),
contains_eager('children.children', alias=ali2)).one()
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/query.py,
line 641, in one
ret = list(self[0:2])
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/query.py,
line 655, in __iter__
return self._execute_and_instances(context)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/query.py,
line 660, in _execute_and_instances
return iter(self.instances(result, querycontext=querycontext))
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/query.py,
line 721, in instances
self.select_mapper._instance(context, row, result)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/mapper.py,
line 1448, in _instance
self.populate_instance(context, instance, row, **flags)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/mapper.py,
line 1514, in populate_instance
(newpop, existingpop, post_proc) =
selectcontext.exec_with_path(self, prop.key,
prop.create_row_processor, selectcontext, self, row)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/query.py,
line 1223, in exec_with_path
return func(*args, **kwargs)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/interfaces.py,
line 487, in create_row_processor
return
self._get_context_strategy(selectcontext).create_row_processor(selectcontext,
mapper, row)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/strategies.py,
line 593, in create_row_processor
row_decorator = self._create_row_decorator(selectcontext, row,
selectcontext.path)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/strategies.py,
line 580, in _create_row_decorator
decorated_row = decorator(row)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/strategies.py,
line 709, in decorate
for c in prop.target.columns:
NameError: free variable 'prop' referenced before assignment in enclosing scope
So without aliases there is a cycle, but with aliases there's this
exception. Just for kicks I tried using the name of the alias instead
of the alias object:
root = query.options(contains_eager('children', alias=ali.name),
contains_eager('children.children', alias=ali2.name)).one()
Traceback (most recent call last):
File basic_tree.py, line 88, in module
root = query.options(contains_eager('children', alias=ali.name),
contains_eager('children.children', alias=ali2.name)).one()
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/query.py,
line 267, in options
opt.process_query(q)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/interfaces.py,
line 561, in process_query
self.process_query_property(query, paths)
File /home/stephen/src/sqlalchemy/lib/sqlalchemy/orm/strategies.py,
line 704, in process_query_property
(mapper, propname) = paths[-1]
ValueError: too many values to unpack
and I get a different exception.
I'm a bit confused now, so please tell me if I've got something
fundamentally wrong here, otherwise are these bugs?
Thanks
Stephen Emslie
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email
[sqlalchemy] Re: adjacency list: filtering relations eagerly
Hi. I've been quiet on this for a while. I'm getting by without this
behavior, though it would be nice to get a bit of clarity.
Just to refresh, I've got an implementation very similar to the
adjacency list example. Michael has helped me to eager load a subset
of a node's children with a call to from_statement and contains_eager,
however I got ambitious and wanted to get the same behavior for
grandchildren, etc. and I discovered that dotted relations like
contains_eager('children.children', alias=alias) don't seem to work
too well with self-referential mappers
(the alias of the last contains_eager statement to be issued ends up
as the only 'children' relation in the result)
Any idea whether this is a bug or expected behavior? I could try to
rustle up a test script if it would help.
Stephen Emslie
On 9/11/07, stephen emslie [EMAIL PROTECTED] wrote:
I've done some more playing here and I think I can see why the
multiple contains_eager options dont work on a self-referential
mapping.
Here's my example again:
ali = trees.alias()
ali2 = trees.alias()
statement = trees.outerjoin(ali,
and_(trees.c.node_id==ali.c.parent_node_id,
ali.c.node_name.in_('node2')))
statement = statement.outerjoin(ali2,
and_(ali.c.node_id==ali2.c.parent_node_id,
ali2.c.node_name.in_('subnode2')))
statement = statement.select().where(trees.c.parent_node_id==None)
query = create_session().query(TreeNode)\
.from_statement(statement)\
.options(contains_eager('children', alias=ali),
contains_eager('children.children', alias=ali2))
test = query.one()
The first contains_eager goes off without a hitch. However the second
one hits a problem in PropertyOption._get_properties when it tries to
use the mapper belonging to the first 'children' property as the basis
for the next 'children' property. If these were different mappers then
there wouldn't be a problem, but as this is a self-referential mapper
we end up getting the same mapper back and overwriting the first
'children' property with the second one.
The result is that we always get the last alias in the chain with
test.children.
It looks to me like self-referential joins created by the orm must
handle this by creating secondary mappers. I'm a bit off the beaten
track here and making it from a sql statement so those mappers never
get created.
At a glance it looks like _get_properties could create a secondary
mapper for a property if the next mapper in line was the same as the
current one (mapper == prop.mapper) and I'll give that a shot, but
then I'm not looking at the bigger picture of how PropertyOption is
used.
That was a bit of a mouthful, so if it doesn't make any sense then
please ask me to clarify and I'll do my best.
As an aside, I've worked around this issue a bit using add_entity and
I'm seeing a big performance improvement. Using contains_eager would
allow me to cut down significantly on the number of requests I'm
performing.
Thanks for the help!
Stephen Emslie
On 9/4/07, stephen emslie [EMAIL PROTECTED] wrote:
im going to play with this a little bit, but my first instinct is
that you might want to use contains_eager('children.children', ...)
for your deeper aliases. but im not sure if something might prevent
that from working since i havent tested contains_eager in self-
referential scenarios as of yet.
Thanks for taking a look. I did give
contains_eager('children.children') a try as it seemed the most likely
thing to work. Unfortunately it seemed to override the previous
contains_eager relation, so I ended up with the root's 'children'
relation coming up with subnode2 rather than node2 (i.e. skipping the
first relation), but its nice to know I wasn't completely off that
mark :)
Stephen Emslie
--~--~-~--~~~---~--~~
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: adjacency list: filtering relations eagerly
Thanks for the quick response.
Looking forward to the refactoring :)
Stephen Emslie
On 10/9/07, Michael Bayer [EMAIL PROTECTED] wrote:
On Oct 9, 2007, at 10:18 AM, stephen emslie wrote:
Hi. I've been quiet on this for a while. I'm getting by without this
behavior, though it would be nice to get a bit of clarity.
Just to refresh, I've got an implementation very similar to the
adjacency list example. Michael has helped me to eager load a subset
of a node's children with a call to from_statement and contains_eager,
however I got ambitious and wanted to get the same behavior for
grandchildren, etc. and I discovered that dotted relations like
contains_eager('children.children', alias=alias) don't seem to work
too well with self-referential mappers
(the alias of the last contains_eager statement to be issued ends up
as the only 'children' relation in the result)
Any idea whether this is a bug or expected behavior? I could try to
rustle up a test script if it would help.
without even looking, id classify the behavior youre getting as
both. i.e. its something that should be improved and also probably
whats expected for now.
I have an architectural plan to change the way query.options() are
targeted towards mapperproperties, i.e. via a full path instead of
identity (right now, the single children MapperProperty is targeted
with contains_eager across the board. it would have to be smarter
to understand that its only targeted with contains_eager when the
load operation hits the path children.children).
Theres some other eagerload options behaviors which are ticketed in
trac which would also be addresed by this (someone trying to do A-B-
C-B-A eagerloading...similar idea).
the timeline for this refactoring is somewhere beyond the first
release of 0.4 final which is hopefully by next week.
--~--~-~--~~~---~--~~
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: adjacency list: filtering relations eagerly
I've done some more playing here and I think I can see why the
multiple contains_eager options dont work on a self-referential
mapping.
Here's my example again:
ali = trees.alias()
ali2 = trees.alias()
statement = trees.outerjoin(ali,
and_(trees.c.node_id==ali.c.parent_node_id,
ali.c.node_name.in_('node2')))
statement = statement.outerjoin(ali2,
and_(ali.c.node_id==ali2.c.parent_node_id,
ali2.c.node_name.in_('subnode2')))
statement = statement.select().where(trees.c.parent_node_id==None)
query = create_session().query(TreeNode)\
.from_statement(statement)\
.options(contains_eager('children', alias=ali),
contains_eager('children.children', alias=ali2))
test = query.one()
The first contains_eager goes off without a hitch. However the second
one hits a problem in PropertyOption._get_properties when it tries to
use the mapper belonging to the first 'children' property as the basis
for the next 'children' property. If these were different mappers then
there wouldn't be a problem, but as this is a self-referential mapper
we end up getting the same mapper back and overwriting the first
'children' property with the second one.
The result is that we always get the last alias in the chain with test.children.
It looks to me like self-referential joins created by the orm must
handle this by creating secondary mappers. I'm a bit off the beaten
track here and making it from a sql statement so those mappers never
get created.
At a glance it looks like _get_properties could create a secondary
mapper for a property if the next mapper in line was the same as the
current one (mapper == prop.mapper) and I'll give that a shot, but
then I'm not looking at the bigger picture of how PropertyOption is
used.
That was a bit of a mouthful, so if it doesn't make any sense then
please ask me to clarify and I'll do my best.
As an aside, I've worked around this issue a bit using add_entity and
I'm seeing a big performance improvement. Using contains_eager would
allow me to cut down significantly on the number of requests I'm
performing.
Thanks for the help!
Stephen Emslie
On 9/4/07, stephen emslie [EMAIL PROTECTED] wrote:
im going to play with this a little bit, but my first instinct is
that you might want to use contains_eager('children.children', ...)
for your deeper aliases. but im not sure if something might prevent
that from working since i havent tested contains_eager in self-
referential scenarios as of yet.
Thanks for taking a look. I did give
contains_eager('children.children') a try as it seemed the most likely
thing to work. Unfortunately it seemed to override the previous
contains_eager relation, so I ended up with the root's 'children'
relation coming up with subnode2 rather than node2 (i.e. skipping the
first relation), but its nice to know I wasn't completely off that
mark :)
Stephen Emslie
--~--~-~--~~~---~--~~
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: adjacency list: filtering relations eagerly
im going to play with this a little bit, but my first instinct is
that you might want to use contains_eager('children.children', ...)
for your deeper aliases. but im not sure if something might prevent
that from working since i havent tested contains_eager in self-
referential scenarios as of yet.
Thanks for taking a look. I did give
contains_eager('children.children') a try as it seemed the most likely
thing to work. Unfortunately it seemed to override the previous
contains_eager relation, so I ended up with the root's 'children'
relation coming up with subnode2 rather than node2 (i.e. skipping the
first relation), but its nice to know I wasn't completely off that
mark :)
Stephen Emslie
--~--~-~--~~~---~--~~
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: adjacency list: filtering relations eagerly
On 8/20/07, Michael Bayer [EMAIL PROTECTED] wrote:
I'd be curious though if you could play around a little with
the approach I just suggested to see if its at all workable ?
After quite a bit of head-scratching I think I've got the hang of
this. Here's what I'm doing using the basic_tree.py example
(http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/adjacencytree/basic_tree.py):
my_eager_alias = mynodes.alias()
query = sess.query(TreeNode).filter_by(parent_id=None).\
.filter(my_eager_alias.c.node_name == 'node2')
query = query.from_statement(select([mynodes, my_eager_alias],
query._criterion)).\
.options(contains_eager('children', alias=my_eager_alias))
then query.one().children gives back just what I wanted!:
{u'node2': node2 (4,1, 139342252)}
this is exactly what you suggested and it works perfectly, it just
took me a while to realize how the join would behave around that
alias. However, one thing still remains as I'd like to be able to
repeat this behavior for more than one level of the tree. In other
words, if node2 in turn has children subnode1 and subnode2 (as in the
example) I want subnode 1 to be eagerloading on node2's children
relation just as node2 is eagerloading on the root's.
so far my only attempt was to filter on more than one alias, but
contains_eager refers to the statement as a whole and so only only one
relation can be set against one of the aliases so it doesn't seem
possible to chain these together.
I think theres probably some relatively simple options we can be
adding to Query here to support what you're trying to dosuch as
contains_eager('children', alias=myalias, add_columns=True) which
would automatically add the columns to the Query's SELECT statement
without needing to construct your from_statement() like we're doing
above.
This would certainly neaten things up :)
Stephen Emslie
--~--~-~--~~~---~--~~
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] between_op() error with property expressions: is this a bug?
I'm using the new property-based query expressions to create a between clause to filter on: MappedClass.age.between(1,2) This produces (on alchemy 0.4 beta4.): *** TypeError: between_op() takes exactly 2 arguments (3 given) Is this a bug, or should I not be using a property-based expression here? MappedClass.c.age.between(1,2) behaves normally. Stephen Emslie --~--~-~--~~~---~--~~ 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] adjacency list: filtering relations eagerly
Hi. I am using a self-referential mapper to represent a multi-level tree of parent-child relationships. Typically I've been querying each parent for children that I am interested in. Up till now I have made a new query for each child that I am looking for, which is doesn't seem like the most efficient way to approach this. I've found that 0.4's aliased joins are great for selecting parents based on their children, and can be eager-loaded to grab entire subtrees with a single query. However each parent in my table can have many children (and trees can many many levels deep) so eager loading root nodes can be a bit slow too. Ideally I would like to be able to eager load only the children that were involved in the join as those are the only ones that I am interested in, so that something like: parent_instance.children or equivalent, only loads children that were part of the original join rather than all of parent_instance's children. Is something like this possible? The closet that I've found is using add_entity after each join in my query. Thanks Stephen --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
