[sqlalchemy] relationships for no-table-related Class
I have a tree structure
Root
|
+--Area
||
|+--SubArea
|||
||+--Item
|||
||+--Item
||
|+--SubArea
| |
| +--Item
| |
| +--Item
|
+--Area
|
+--SubArea
||
|+--Item
||
|+--Item
|
+--SubArea
|
+--Item
|
+--Item
The tree structure corresponds to slqalchemy db tables `areas`,
`subareas` and `items`.
Something like this:
mapper(Area, areas_table, properties={
'subareas': relationship(SubArea, backref='parent'),
})
mapper(SubArea, subareas__table, properties={
'items': relationship(Item, backref='parent'),
})
mapper(Item, items_table)
so each Area instance will have a `subareas` list and each SubArea
will have a `items` list,
also I easyly get a backref `parent` from Item to parent SubArea and
from
SubArea to parent Area.
But this won't be for Root: it will not have a `areas` list in Root
nor its areas will have a parent reference to Root.
The quick-and-dirty solution is to do this in Root:
self.areas = query(Area).all()
for area in self.areas:
area.parent = self
But it won't be the same thing as sqlalchemy `relationship` attributes
so:
are there alternative solutions more sqlalchemy-like?
Any tip appreciated!
Thank you for your support
Greetings
neurino
--
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: Delete and Concrete Table Inheritance
Yes, that was what I searched for. My Solution now is to use pythons
introspection like this:
for dataClass in Master.__subclasses__():
table = sqlalchemy.orm.class_mapper(dataClass).local_table
delete_query = table.delete(). \
where(...)
session.execute(delete_query, dict(...))
I was searching for something in sqlalchemy, that does the same by
calling something like:
Master.deleteAll().where(...) or similar
Thanks for the help!
On 10 Nov., 15:43, Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 10, 2010, at 8:00 AM, Mene wrote:
Yes, I definitely want to emit a DELETE statement. The Problem is that
calling master.delete() only gives an delete statement for the master
table. However, I need n querys: DELETE child1... , DELETE child2 and
so on.
OK, so I think there's another question that you mean to be asking here,
since I'm sure its obvious that to emit a DELETE statement for child1,
child2, etc., you do the same thing for those Table objects:
child1.delete().where(...)
child2.delete().where(...)
child3.delete().where(...)
... etc
I think the question you want here is how do I get at all the Table objects
in an inheritance hierarchy given only the base class?
for mapper in master_mapper.polymorphic_iterator():
Session.execute(mapper.local_table.delete().where(...))
Note that concrete inheritance is the least wieldy inheritance scheme. If
you were using joined table inheritance, you could configure ON DELETE
CASCADE on all your tables and you could then emit a single DELETE just for
the master table that would automatically delete from all related tables.
On 8 Nov., 20:31, Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 8, 2010, at 11:02 AM, Mene wrote:
Hi all,
I have some 'child' tables which inherit from the same 'master' table,
now I'd like to delete some entries from all tables. The 'where'-part
comes solely from the master table.
I have decided to use Concrete Table Inheritance since I don't need
the inheritance at points other than deleting, but I expect the tables
to have a lot of entries and at the moment I don't know how many child
tables I will have in the future. Also the delete process won't occur
often.
Each table has a compound primary key of user and room.
I use version 0.4.8 (and I can't change this)
I need a delete statement that deletes according to user_id and the
length of the room identifier.
Also master is only an abstract class, so I don't need to have a table
in my database (AFAIK).
There's only two choices here, you can either load the objects with the
ORM and individually pass them to session.delete(), or you can emit DELETE
statements against the tables directly using SQL expressions or strings.
The SQL expression would be along the lines of
Session.execute(master.delete().where(...)).
master = Table('master', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True)
)
child1 = Table('child1', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True),
Column('child1_data', Unicode(16))
)
child2 = Table('child2', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True),
Column('child2_data', Unicode(16))
)
join = polymorphic_union({
'master': master,
'child1': child1,
'child2': child2
}, 'type', 'pjoin')
master_mapper = mapper(Master, master, with_polymorphic=('*',
vote_join), \
polymorphic_on=vote_join.c.type, polymorphic_identity='vote_data')
child1_mapper = mapper(Child1, child1, inherits=master, \
concrete=True, polymorphic_identity='child1')
child2_mapper = mapper(Child2, child2, inherits=master, \
concrete=True, polymorphic_identity='child2')
Thanks for your time,
Mene
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
[sqlalchemy] how to graph database structure?
Hi, I've reflected a database, and it would like to get a graphic representation of it something like the graph_models command in django command extensions. The best would be if the tool could create the graphics without a database connection, simply using my metadata for example. I've googled for this, but couldn't find anything seemingly up-to-date. What would be your suggestion? thanks, Viktor -- 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.
Re: [sqlalchemy] how to graph database structure?
On Thu, Nov 11, 2010 at 12:59 PM, Nagy Viktor viktor.n...@toolpart.hu wrote: Hi, I've reflected a database, and it would like to get a graphic representation of it something like the graph_models command in django command extensions. The best would be if the tool could create the graphics without a database connection, simply using my metadata for example. I've googled for this, but couldn't find anything seemingly up-to-date. What would be your suggestion? http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay -- 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] Two relationships with a same backref name. Is that (even) possible or I got everything wrong?
I have a class that has two relationships to the same type of objects.
One of the relationships will store objects of type VR and the other
objects with a type CC. One object can only be in one of the lists
(relationships) at the same time:
This is the container class and its two relationships:
class Container(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(containers)
id = Column(id, Integer, primary_key=True)
relation1 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == VR)),
cascade=all, delete, delete-orphan
)
relation2 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == CC)),
cascade=all, delete, delete-orphan
)
I don't think there's need to mention, but, MyObject.containerId is
a ForeignKey pointing to the Container.id.
I'd like to know if there's a way to create a backref so I will be
able to access the container through the MyObject class. The idea
would be having something like:
relation1 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == VR)),
cascade=all, delete, delete-orphan,
backref=backref('container', order_by=id)
)
relation2 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == CC)),
cascade=all, delete, delete-orphan,
backref=backref('container', order_by=id)
)
But of course, that fails because it's trying to add two .container
fields to the MyObject class.
I have also seen that you can define joins in the backref, but I
haven't been able to find examples about how to define it. And I am
still not very sure that that would allow me to have to backrefs with
the same name/identifier.
I just need to know if it's even possible having two backrefs with the
same name. Actually, a you really got the whole concept wrong may
help too (if that's the case) . If it's doable, does any of you know
where can I find examples of advanced backref usage? With primary
joins, secondary joins and all that juicy stuff...
Thank you in advance!!
--
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.
Re: [sqlalchemy] Re: Odd many-to-one problem
For cases like this I have found something like this to be useful http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child using lazy loading and viewonly=True as needed I found this to be clearer than column property because it fits cleanly with the rest of the relationship configuration. -- Mike Conley -- 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: Odd many-to-one problem
relationship() expects a class or a mapper instance, not a string. I
got this error:
ArgumentError: relationship 'available_deals' expects a class or a
mapper argument (received: type 'str')
On Nov 10, 4:46 pm, Sergey V. sergey.volob...@gmail.com wrote:
The twist is that I've spread out my tables and ORM classes across
several files. I've tried to keep it so that I don't have circular
dependencies. That means I've defined Merchant first, and then Deal
later, in separate files
To avoid problems with imports and dependencies you can pass strings
to the relationship function instead of the actual classes:
mapper(Deal, deals, properties=dict(
merchant=relationship('Merchant', backref='deals'),
))
This greatly simplifies everything if you split your classes into
separate files.
Regarding 'available_deals', 'deleted_deals' etc. - the approach with
properties is sub-optimal. Consider a merchant having thousands of
deals, only a few of which are available - the method would have to
fetch all those deals only to discard most of them. Also, that won't
work with eager loading. The optimal way would be to make SA to
generate a query like SELECT ... FROM Deals WHERE ... AND deleted=1
which would return only the records we're interested in. I'm sure it's
possible but I'll leave it to you to find it in SA docs :) When you
find it please post it here :)
--
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: Odd many-to-one problem
That is useful for mapping single or combined columns to an attribute.
Here, I want to map entire objects.
On Nov 10, 10:20 pm, Eric Ongerth ericonge...@gmail.com wrote:
Good point, Sergey.
Here is the relevant documentation regarding mapping attributes to
selects:http://www.sqlalchemy.org/docs/orm/mapper_config.html?highlight=arbit...
On Nov 10, 4:46 pm, Sergey V. sergey.volob...@gmail.com wrote:
The twist is that I've spread out my tables and ORM classes across
several files. I've tried to keep it so that I don't have circular
dependencies. That means I've defined Merchant first, and then Deal
later, in separate files
To avoid problems with imports and dependencies you can pass strings
to the relationship function instead of the actual classes:
mapper(Deal, deals, properties=dict(
merchant=relationship('Merchant', backref='deals'),
))
This greatly simplifies everything if you split your classes into
separate files.
Regarding 'available_deals', 'deleted_deals' etc. - the approach with
properties is sub-optimal. Consider a merchant having thousands of
deals, only a few of which are available - the method would have to
fetch all those deals only to discard most of them. Also, that won't
work with eager loading. The optimal way would be to make SA to
generate a query like SELECT ... FROM Deals WHERE ... AND deleted=1
which would return only the records we're interested in. I'm sure it's
possible but I'll leave it to you to find it in SA docs :) When you
find it please post it here :)
--
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: Odd many-to-one problem
This is what I need to do, except the Merchant object is defined before the Deal object. In the example in the documentation, I have mapped User before I have mapped Address. On Nov 11, 10:25 am, Mike Conley mconl...@gmail.com wrote: For cases like this I have found something like this to be useful http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relati... using lazy loading and viewonly=True as needed I found this to be clearer than column property because it fits cleanly with the rest of the relationship configuration. -- 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.
Re: [sqlalchemy] Re: Odd many-to-one problem
If it's simply a matter of sequence of how code is organized:
1. Define Merchants table and mappers
2. Define Deals table and mappers
3. Add relations to Merchant
All of this can be in separate files if needed; just import right
definitions where needed.
metadata = MetaData()
merchants = Table('merchants', metadata,
Column('id', Integer, primary_key=True),
Column('name', String)
)
class Merchant(object):
pass
mapper(Merchant, merchants)
deals = Table('deals', metadata,
Column('id', Integer, primary_key=True),
Column('merch_id', Integer, ForeignKey('merchants.id')),
Column('deal_status', String(10))
)
class Deal(object):
pass
mapper(Deal, deals)
Merchant.all_deals = relation(Deal, backref='merchant')
Merchant.active_deals = relation(Deal, primaryjoin=
and_(merchants.c.id==deals.c.merch_id,
deals.c.deal_status=='active'))
This is one advantage of using declarative because the primaryjoin can be
defined as a string that will not be compiled until later. That can be
deferred until after everything is defined.
--
Mike Conley
On Thu, Nov 11, 2010 at 1:33 PM, Jonathan Gardner
jgard...@jonathangardner.net wrote:
This is what I need to do, except the Merchant object is defined
before the Deal object. In the example in the documentation, I have
mapped User before I have mapped Address.
--
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: Odd many-to-one problem
relationship() expects a class or a mapper instance, not a string. I
got this error:
ArgumentError: relationship 'available_deals' expects a class or a
mapper argument (received: type 'str')
Hmm... I'm not sure what I'm doing wrong but passing strings to
relation() definitely works for me:
class Host(Base):
__tablename__ = 'hosts'
id = sa.Column(sa.Integer, primary_key = True)
...
datacentre_id = sa.Column(sa.Integer,
sa.ForeignKey('datacentres.id'))
datacentre = sa.orm.relation('Datacentre', backref='hosts')
Can it be because I'm using declarative? In my case I don't even need
to import Datacentre class before I declare Host class.
--
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: Two relationships with a same backref name. Is that (even) possible or I got everything wrong?
Hi Hector,
If I'm not mistaken, everywhere you wrote
(MyObject.id==MyObject.containerId),
you meant to write: (Container.id==MyObject.containerId).
Instead of the backref technique, why not just create the MyObject--
Container relationship a single time in your MyObject class. That
should be able to coexist with your first code example (with no
backrefs).
On Nov 11, 8:16 am, Hector Blanco white.li...@gmail.com wrote:
I have a class that has two relationships to the same type of objects.
One of the relationships will store objects of type VR and the other
objects with a type CC. One object can only be in one of the lists
(relationships) at the same time:
This is the container class and its two relationships:
class Container(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(containers)
id = Column(id, Integer, primary_key=True)
relation1 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id ==
MyObject.containerId),
(MyObject._type == VR)),
cascade=all, delete, delete-orphan
)
relation2 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id ==
MyObject.containerId),
(MyObject._type == CC)),
cascade=all, delete, delete-orphan
)
I don't think there's need to mention, but, MyObject.containerId is
a ForeignKey pointing to the Container.id.
I'd like to know if there's a way to create a backref so I will be
able to access the container through the MyObject class. The idea
would be having something like:
relation1 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id ==
MyObject.containerId),
(MyObject._type == VR)),
cascade=all, delete, delete-orphan,
backref=backref('container', order_by=id)
)
relation2 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id ==
MyObject.containerId),
(MyObject._type == CC)),
cascade=all, delete, delete-orphan,
backref=backref('container', order_by=id)
)
But of course, that fails because it's trying to add two .container
fields to the MyObject class.
I have also seen that you can define joins in the backref, but I
haven't been able to find examples about how to define it. And I am
still not very sure that that would allow me to have to backrefs with
the same name/identifier.
I just need to know if it's even possible having two backrefs with the
same name. Actually, a you really got the whole concept wrong may
help too (if that's the case) . If it's doable, does any of you know
where can I find examples of advanced backref usage? With primary
joins, secondary joins and all that juicy stuff...
Thank you in advance!!
--
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: Odd many-to-one problem
Mike, what you set forth is more of what I was actually trying to
bring into the discussion (having used that same technique myself),
rather than the link I gave above. I need to get more sleep and check
my doc references more carefully!
On Nov 11, 1:39 pm, Mike Conley mconl...@gmail.com wrote:
If it's simply a matter of sequence of how code is organized:
1. Define Merchants table and mappers
2. Define Deals table and mappers
3. Add relations to Merchant
All of this can be in separate files if needed; just import right
definitions where needed.
metadata = MetaData()
merchants = Table('merchants', metadata,
Column('id', Integer, primary_key=True),
Column('name', String)
)
class Merchant(object):
pass
mapper(Merchant, merchants)
deals = Table('deals', metadata,
Column('id', Integer, primary_key=True),
Column('merch_id', Integer, ForeignKey('merchants.id')),
Column('deal_status', String(10))
)
class Deal(object):
pass
mapper(Deal, deals)
Merchant.all_deals = relation(Deal, backref='merchant')
Merchant.active_deals = relation(Deal, primaryjoin=
and_(merchants.c.id==deals.c.merch_id,
deals.c.deal_status=='active'))
This is one advantage of using declarative because the primaryjoin can be
defined as a string that will not be compiled until later. That can be
deferred until after everything is defined.
--
Mike Conley
On Thu, Nov 11, 2010 at 1:33 PM, Jonathan Gardner
jgard...@jonathangardner.net wrote:
This is what I need to do, except the Merchant object is defined
before the Deal object. In the example in the documentation, I have
mapped User before I have mapped Address.
--
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.
