[sqlalchemy] Re: To select only some columns from some tables using session object, relation many-to-many
On Aug 3, 8:43 am, Alvaro Reinoso alvrein...@gmail.com wrote:
Hello,
I have these classes where items (class Item) is related to channel
object. Channel can contain many items:
channel_items = Table(
channel_items,
metadata,
Column(channel_id, Integer,
ForeignKey(channels.id)),
Column(item_id, Integer,
ForeignKey(Item.id))
)
class Channel(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(channels)
id = Column(id, Integer, primary_key=True)
title = Column(title, String(100))
items = relation(Item, secondary=channel_items,
backref=channels)
class Item(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(items)
id = Column(id, Integer, primary_key=True)
title = Column(title, String(100))
I know how to get all the columns using something like:
session = rdb.Session() channels =
session.query(Channel).order_by(Channel.title)
However, I'd like to select some columns from both tables with some
conditions in Item. For example, select all the channels where
item.type = 'jpg'. I'd like to get a channel object with items
attributes with that condition for example. How can I do that?
I've tried something like (no one worked out):
result = session.query(Channel).filter(Item.typeItem != 'zeppelin/
channel').all()
result = session.query(Channel, Item).filter(Item.typeItem !=
'zeppelin/channel').all()
Thanks in advance!
Try something like
session.query(Channel).join('items').filter(Item.typeItem !=
'whatever').all()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Exists clause on relations of different mapped objects
On Jul 28, 11:37 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 27, 2010, at 9:59 PM, Kalium wrote: I'm trying to determine whether or not there are any value in one mapped object relation that correspond to another mapped object relation i.e A.x - represents a relation of x's on A B.x - represents a relation of x's on B They both use their own separate mapping tables for these relations. So I want to find out of any of A.x is also present in B.x how about query(x).filter(x.a_id==my_a.id).filter(x.b_id==my_b.id) ? thats assuming you're starting with a particular A and B. Opps, accidentaly replied to Michael and not the group. I wasn't dealing with a particular A and B. I wasn't successful in using the tables in the outer query so I merely reconstructed the where clause in the exists query that I used to join the outer tables in the first place, i.e A.x.any(B.x.any()).where(X.id == my_id) Thanks The closes I've had to it working was in the follwing fashion, by doing the following; exists([1],from_obj=join(A_x_mapping_table,B_x_mapping_table, onclause=A_x_mapping_table.c.id==B_x_mapping_table.c.id)) I put this exists clause into an existing filter() but it didn't correlate the tables with those in the outer query (which I really need it to do!), so I tried using correlate function to correlate the table on the join() but it didn't seem to do anything (I've used it in other situations with success but not this time). Also , A.x.any(B.x.any()) seems to almost do what I want as well, but again, doesn't correlate to outer query and because I'm using 0.4 it does not have a correlate() method in ORM! Any other hints as to how I can do this ? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] add_column does not correlate with aliased table.
Hi,
I've had a look through the docs and a quick look through the forum
here, and haven't been able to solve my problem. I'm using 0.4
The following works as expected.
q =
System.query().join('activity').group_by(model.System.id).add_column(func.max(Activity.id))
The add_column() recognises that the activity table is already joined.
and thus does not add it to the tables in the 'FROM' clause.
However, the following does not work. The only difference is that now
the joined table (activity) is aliased.
q =
System.query().join('activity',aliased=True).group_by(model.System.id).add_column(func.max(Activity.id))
add_column() does not recognise the aliased activity table and the
from clause now looks something like 'FROM system,activity', whereas
it should be joined.
What are my best solutions?
Cheers
Ray
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: add_column does not correlate with aliased table.
On Mar 30, 10:54 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Mar 30, 2010, at 2:47 AM, Kalium wrote:
Hi,
I've had a look through the docs and a quick look through the forum
here, and haven't been able to solve my problem. I'm using 0.4
The following works as expected.
q =
System.query().join('activity').group_by(model.System.id).add_column(func.max(Activity.id))
The add_column() recognises that the activity table is already joined.
and thus does not add it to the tables in the 'FROM' clause.
However, the following does not work. The only difference is that now
the joined table (activity) is aliased.
q =
System.query().join('activity',aliased=True).group_by(model.System.id).add_column(func.max(Activity.id))
add_column() does not recognise the aliased activity table and the
from clause now looks something like 'FROM system,activity', whereas
it should be joined.
What are my best solutions?
in 0.4 you'd need to use SQL-level Table and alias() objects to achieve the
desired effect. in 0.5 and above, you'd use aliased(Activity) as your
entity. the aliased=True option only affects subsequent
filter/order_by/group_by calls.
Thanks Michael. Another reason to upgrade to 0.5
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] correlate on Query in 0.4
Hi, 0.4 doesn't seem to support correlate() on a Query object. I have a subquery (which is actually just another Query object), so at no point can I actually do a correlate(). Any other good options other than upgrading to 0.5 or using a select ? Cheers -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Dynamic mapper issue
Thanks Michael, I'm going to take a closer look at this and rework my
code.
Cheers
Raymond
On Nov 27 2009, 12:56 pm, Michael Bayer mike...@zzzcomputing.com
wrote:
On Nov 26, 2009, at 2:05 AM, Kalium wrote:
Hi, I'm coming to grief trying to create aMapperon the fly.
At first I tried this
classDynamic(object):
pass
Dynamic.mapper=mapper(Dynamic, self.j);
self.j is a join statement. This now maps all the columns from the
tables in the join statement. Which is fine. Except that in the SQL
query it looks something like SELECT table1.id AS id, table2.id AS
id etc etc. So that won't work as the columns don't have alias'.
the join() by itself is automatically disambiguating so you shouldn't have
such an issue:
from sqlalchemy import *
from sqlalchemy.orm import *
m = MetaData(e)
t1 = Table('t1', m, Column('id', Integer, primary_key=True))
t2 = Table('t2', m, Column('id', Integer, primary_key=True))
j = t1.join(t2, t1.c.id==t2.c.id)
# j now has j.c.t1_id, j.c.t2_id
print j.c.t1_id, j.c.t2_id
returns:
t1.id t2.id
now map:
class C(object):
pass
mapper(C, j)
print create_session().query(C)
returns:
SELECT t1.id AS t1_id, t2.id AS t2_id
FROM t1 JOIN t2 ON t1.id = t2.id
the map of C will have a single attribute id on it, which is based on that
name. in this case, that's fine since the two id columns are equated, and
you'd want them to be under one attribute. If you didn't, you can set up the
mapping explicitly with the names you prefer:
m = MetaData(create_engine('sqlite://'))
t1 = Table('t1', m, Column('id', Integer, primary_key=True), Column('data',
String))
t2 = Table('t2', m, Column('id', Integer, primary_key=True), Column('data',
String))
m.create_all()
j = t1.join(t2, t1.c.id==t2.c.id)
class C(object):
pass
mapper(C, j, properties={
't1data':t1.c.data,
't2data':t2.c.data
})
s = sessionmaker()()
c1 = C()
c1.t1data='foo'
c1.t2data='bar'
s.add(c1)
s.commit()
print s.execute(select * from t1 join t2 on t1.id=t2.id,mapper=C).fetchall()
returns:
[(1, u'foo', 1, u'bar')]
above, themapperfor C has a single attribute id representing t1.c.id and
t2.c.id, but separate t1data and t2data attributes representing t1.c.data
and t2.c.data, respectively.
Next I tried something like this
Dynamic.mapper=mapper(Dynamic,select(fields_to_select,from_obj=
[self.j]),primary_key=[table1.c.id,table2.c.id,table3.c.id])
here, you've mapped to a select() construct - that is your selectable. The
mistake here is that you used columns for primary_key which are from your
tables - which are represented within the internal FROM clause of your
select() but are not the columns it publically exposes in its columns clause.
Any columns you specify to primary_key must be in terms of the mapped
selectable's exported columns clause, i.e. myselect.c.table1_id,
myselect.c.table2_id, myselect.c.table3_id. Specifying use_labels on your
select() construct will allow the column names it exports in its c
collection to be prepended with the table name, the same way that the join()
does automatically.
But also, if your select() already has all the PK fields from the three
tables represented, the primary key columns will automatically be pulled out
in the mapping so there is probably no need for the primary_key argument.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] string type passed to join's onclause
Hi, It seems that although would work fine in place of a ClauseElement when passed to filter(), but not so when passed to the onclause parameter of join() ? There doesn't seem to be anything in the ClauseElement class that will convert a string to something of type ClauseElement. Have I missed anything? Any workarounds ? Cheers -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: string type passed to join's onclause
Sorry, I should have looked at the source first. sql.text(string) should fix it. On Dec 7, 5:22 pm, Kalium raymond.ma...@gmail.com wrote: Hi, It seems that although would work fine in place of a ClauseElement when passed to filter(), but not so when passed to the onclause parameter of join() ? There doesn't seem to be anything in the ClauseElement class that will convert a string to something of type ClauseElement. Have I missed anything? Any workarounds ? Cheers -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Dynamic mapper issue
Hi, I'm coming to grief trying to create a Mapper on the fly.
At first I tried this
class Dynamic(object):
pass
Dynamic.mapper = mapper(Dynamic, self.j);
self.j is a join statement. This now maps all the columns from the
tables in the join statement. Which is fine. Except that in the SQL
query it looks something like SELECT table1.id AS id, table2.id AS
id etc etc. So that won't work as the columns don't have alias'.
Next I tried something like this
Dynamic.mapper = mapper(Dynamic,select(fields_to_select,from_obj=
[self.j]),primary_key=[table1.c.id,table2.c.id,table3.c.id])
fields_to_select are the fields I actually need, and they look
something like [table1.c.id,table2.c.id,table3.c.id]. If I want to I
imagine I could individually attach a label() to these and that would
overcome any conflicts with the SQL query. That's not the problem
though, as it gives me this error
mapper Mapper|Dynamic|{ANON 158092684 anon} could not assemble any
primary key columns for mapped table '{ANON 158092684 anon}'
I've looked around and read the docs, been through the newgroup but
can't seem to figure out where I've gone wrong. Any ideas ?
Cheers
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Determine what joins are in select statement
Hi, Just wondering if there is an easy way to determine what tables are joined in a query? I'm doing something like this query = session.query(System, dynamic_obj).select_from(self.j) Where dynamic_obj could be any mapped object and self.j represents a join produced by the sqlalchemy.schema.Table.join() function. I'd like to know if the table represented by dynamic_obj is already in the self.j I could of course do this programmatically outside of sqla, but it would be nice if sqla could tell me this. Cheers --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
