[sqlalchemy] Mapping a graph data structure
Hi there,There was a question on IRC yesterday on how to map a graph (as in nodes and edges) with SA. I didn't have much time to dwell on this but this was a start: http://paste.ufsoft.org/90 I was curious if someone has done this successfully? The problem I have with the above is that the viewonly property _lower_neighbours isn't updated until the session is clear()ed and the object is reloaded. Is there a way to refresh a specific relation? Now that I think about this, it would have been alot easier to create a directed graph and just create edges in both directions for an undirected one.Arnar --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: TurboEntity announcement
Hello, Well, thank you for that. It's exactly what I needed. The funny thing here is that I've been working on developping approximately the same code for the past two or three weeks, with exactly the same syntax... And I was about to announce it in a few days (after cleaning it up and documenting it a bit)... I got a first working prototype yesterday. I've looked briefly at your code and it seems like we've taken drastically different approaches for exactly the same goal. Anyway, I'm too far in the process now to just let it go, so I'll just finish it as if I didn't know your project existed, then I'll look at what you did more thoroughly and either contribute to your project or try to convince you to switch to mine. ;-) PS: I hope I don't sound too frustrated here. Of course, I'm a bit frustrated not to have been the first here, but I am sincerely happy somebody did what I need. Greetings, Gaëtan. On 10/30/06, Daniel Haus [EMAIL PROTECTED] wrote: TurboEntity is a high-level declarative layer on top of SQLAlchemy, inspired by - but somewhat thicker than - Jonathan LaCour's ActiveMapper extension. Features currently include: - automatic polymorphic inheritance - easy specification of relationships - automatic creation of primary keys - automatic creation of foreign keys - automatic creation of secondary tables - relations can be specified across modules More information, documentation, examples and baloons for the kids can be found at http://turboentity.ematia.de/ And here's the blog post. Comments are open! http://www.danielhaus.de/2006/10/30/announcing-turboentity/ Please let me know what you think about TurboEntity and if you find it useful. Greetings, Daniel --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: TurboEntity announcement
That's very good news, maybe we can pull together the best pieces of both approaches? I'd really like to see your solution. Daniel --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: TurboEntity announcement
I'll send you a copy as soon as the basics work. For now, the system is in place but only ManyToOne work. I wanted to also implement the 3 possible types of inheritance before publishing my code, but well, now the circumstances are different... I'd be very glad if we could mix both code bases. FYI, the main difference in my code is that I don't defer classes, I only defer fields, and create them as soon as what is needed for a particular field is defined. So I had to implement a very simple dependency system... It felt like a better approach but I'm not really sure about it... Gaëtan. On 11/1/06, Daniel Haus [EMAIL PROTECTED] wrote: That's very good news, maybe we can pull together the best pieces of both approaches? I'd really like to see your solution. Daniel --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping a graph data structure
Arnar Birgisson wrote:
Hi there,
There was a question on IRC yesterday on how to map a graph (as in nodes and
edges) with SA. I didn't have much time to dwell on this but this was a
start: http://paste.ufsoft.org/90
I was curious if someone has done this successfully? The problem I have with
the above is that the viewonly property _lower_neighbours isn't updated
until the session is clear()ed and the object is reloaded. Is there a way to
refresh a specific relation?
Now that I think about this, it would have been a lot easier to create a
directed graph and just create edges in both directions for an undirected
one.
I haven't done this, but I'm curious about it as well. The application
I'm working on has a graph with ~100,000 edges. I have Node and Edge
tables/objects similar to yours but with some more fields. I pull stuff
from the DB to create an adjacency list, which I save to disk using the
marshal package (it gets saved as a .pyc file, ~7MB).
Reading the marshaled graph and running it through Dijkstra's shortest
paths algorithm is pretty fast. It takes about half a second to read in
the graph and another half second (on average) to get a result from
Dijkstra.
I'm using GIS data that is handed to me by someone else, and I don't
really have a need to invent a way to update it, since I could just use
GRASS or QGIS, but it's still an interesting problem.
Looking at your code, I'm curious about (confused by) the upper and
lower neighbors. Do you need to keep track of which neighbors are
above and below?
Here's a slightly modified version that doesn't use upper and lower and
only uses a single property, _neighbours. There is a flag for
add_neighbour that says whether the node being added (othernode) should
have the node being added to (self) as a neighbor. So, instead of this:
node1.add_neighbour(node2)
node2.add_neighbour(node1)
You'd do this:
node1.add_neighbour(node2, True) # To say that node2 also connects
to node1
or:
node1.add_neighbour(node2, False) # When node2 doesn't connect to
node1
#!/usr/bin/env python
import sys
import os
from sqlalchemy import *
engine = create_engine('sqlite://')
meta = BoundMetaData(engine)
nodes = Table('nodes', meta,
Column(nodeid, Integer, primary_key=True)
)
edges = Table('edges', meta,
Column(node_1_id, Integer, ForeignKey('nodes.nodeid')),
Column(node_2_id, Integer, ForeignKey('nodes.nodeid'))
)
class Node(object):
def __str__(self):
return Node %d, neighbours [%s] % (self.nodeid,
','.join(map(str, [n.nodeid for n in self.neighbours])))
def get_neighbours(self):
return list(self._neighbours)
neighbours = property(get_neighbours)
def add_neighbour(self, othernode, both_ways=True):
if othernode not in self._neighbours:
self._neighbours.append(othernode)
if both_ways and self not in othernode._neighbours:
othernode._neighbours.append(self)
nodes_mapper = mapper(Node, nodes)
nodes2 = nodes.alias('nodes2')
nodes2_mapper = mapper(Node, nodes2, non_primary=True)
nodes_mapper.add_property('_neighbours',
relation(nodes2_mapper,
secondary=edges,
primaryjoin=nodes.c.nodeid==edges.c.node_1_id,
secondaryjoin=nodes2.c.nodeid==edges.c.node_2_id
)
)
def main():
meta.create_all()
s = create_session(engine)
for i in range(10):
n = Node()
n.nodeid = i
s.save(n)
s.flush()
s.clear()
nodes = s.query(Node).select()
nodes[1].add_neighbour(nodes[2])
nodes[1].add_neighbour(nodes[3])
nodes[1].add_neighbour(nodes[4])
nodes[4].add_neighbour(nodes[5], False)
nodes[5].add_neighbour(nodes[1], False)
nodes[2].add_neighbour(nodes[1])
for n in nodes:
print n
if __name__ == '__main__':
main()
Maybe that's useful? Maybe not. It was fun to think about it anyway.
__wyatt
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Activemapper and selection
percious wrote: So, 2 questions. 1) can we add a from clause to the active mapper? 2) are there plans to alleviate the need for '.' replacement in the future? Ooops, ignore part of my last reply, somehow my procmail filter dumped your message into a different folder than my SQLAlchemy lists folder :) Either way, my advice still applies. It would be great if you could illustrate with your example the current SQL that is being generated, along with some improved SQL that would be faster. -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Activemapper and selection
if you use a table with mixed case identifiers, quotes are required. additionally, SA's ORM queries use the use_label=True flag so that joins among many tables can be constructed without the issue of column names conflicting. as for too much work on the MySQL side, i can assure you that you'd hardly detect even a milliecond's difference in performance between quoted/labeled and non, even if you ran the query a million times in each style. the database is *definitely* where you want to put the most computation possible, compared to a Python script. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] Typed Relations - Any Easier way to do Association Object?
(moving to google groups) the idea of using an instrumented list subclass is that you *would* use the association object, and just have the list subclass handle the references to the association object for you, i.e.: class MyList(list): def append(self, item): super(MyList, self).append(new ItemAssociation(item)) def __getitem__(self, index): return super(MyList, self).__getitem__(index).item ..etc On Nov 1, 2006, at 3:15 PM, Karl Guertin wrote: I'm working on a database where the relation between users and objects is defined by an association table that looks like: role_objects_table = Table(role_objects, metadata, Column(objects_id, Integer, ForeignKey(objects.objects_id), primary_key=True), Column(role,Role_Type), Column(user_id, Integer, ForeignKey(tg_user.user_id), primary_key=True)) Where Role_Type is an Enum class that translates roles onto Unicode (1). And Objects is mapped like so: assign_mapper(session.context, Objects, objects_table, properties = dict( #... responsibles = relation(User, secondary=role_objects_table, primaryjoin = and_(objects_table.c.objects_id == role_objects_table.c.objects_id, role_objects_table.c.role == 'R')), approvers = relation(User, secondary=role_objects_table, primaryjoin = and_(objects_table.c.objects_id == role_objects_table.c.objects_id, role_objects_table.c.role == 'A')), #... ) So basically the relationship between users and objects is typed by the role field. This is a textbook use case for the use of an association object, but using an association object is really annoying. I would really like to be able to treat each relation as a normal many to many relation and not have to create intermediate objects just to set one field to a constant value. In looking through the archives I found: You could go more nuts with [object properties] by having the list be a subclass of list which properly does mutation operations too, creates the association objects automatically, etc. Which seems to be what I'm looking to do. I'm assuming that the list referenced here is custom collection_class, but I don't see any obvious hooks into the secondary table and trying to manage the state of intermediate objects is more work than I'd like. In looking through the code, I noticed sqlalchemy.orm.attributes.AttributeExtension, which makes it pretty easy to hook into the event I'm interested in, but I'm having trouble finding a reference to the secondary table. Any guidance would be appreciated. -- --- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel? cmd=lnkkid=120709bid=263057dat=121642 ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] Typed Relations - Any Easier way to do Association Object?
On 11/1/06, Michael Bayer [EMAIL PROTECTED] wrote: (moving to google groups) Eh, sorry cached email address. the idea of using an instrumented list subclass is that you *would* use the association object, and just have the list subclass handle the references to the association object for you, i.e.: Simple enough class MyList(list): def append(self, item): super(MyList, self).append(new ItemAssociation(item)) def __getitem__(self, index): return super(MyList, self).__getitem__(index).item ..etc I'm not understanding what's happening in the append. I know item is one side of my relation, but how to I get access to the other? Is the InstrumentedList magic supposed to handle this somehow? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] Typed Relations - Any Easier way to do Association Object?
OK, i was oversimplifying. if you really want a total bi-directional
many-to-many relationship where the association object is essentially
invisible, you have to set up proxies in both directions. The
attached script illustrates an almost generic way of doing this
which, after a few more iterations, could possibly become an
extension to SA so that folks could re-use this approach without the
ugliness. It basically sets up a proxy list that translates the
association instances in both directions to the underlying target
object. in this example, both sides of the association are many-to-
many. further property tricks can be used to make one side of it
scalar instead
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
m = BoundMetaData('sqlite://', echo=True)
objecttable = Table('objects', m,
Column('id', Integer, primary_key=True),
Column('data', String(30))
)
associationtable = Table('association', m,
Column('oid', Integer, ForeignKey('objects.id'), primary_key=True),
Column('iid', Integer, ForeignKey('items.id'), primary_key=True))
itemtable = Table('items', m,
Column('id', Integer, primary_key=True),
Column('data', String(30))
)
m.create_all()
class Association(object):
association object
pass
class AssociationList(object):
generic proxying list which proxies list operations to a different
list-holding attribute of the parent object, converting Association objects
to and from a target attribute on each Association object.
def __init__(self, parent, collectionname, attr):
create a new AssociationList.
parent - the parent object that contains this list
collectionname - the attribute name which stores the collection of Associations
attr - name of the attribute on the Association in which to get/set target values
self.parent = parent
self.collectionname = collectionname
self.attr = attr
def append(self, item):
a = Association()
setattr(a, self.attr, item)
getattr(self.parent, self.collectionname).append(a)
def __iter__(self):
return iter([getattr(x, self.attr) for x in getattr(self.parent, self.collectionname)])
def __repr__(self):
return repr([getattr(x, self.attr) for x in getattr(self.parent, self.collectionname)])
def __len__(self):
return len(getattr(self.parent, self.collectionname))
def __getitem__(self, index):
return getattr(getattr(self.parent, self.collectionname)[index], self.attr)
def __setitem__(self, index, value):
a = Association()
setattr(a, self.attr, value)
getattr(self.parent, self.collectionname)[index] = a
class AssocProp(object):
a property object that automatically sets up AssociationLists on a parent object.
def __init__(self, collectionname, attr):
create a new association property.
collectionname - the attribute name which stores the collection of Associations
attr - name of the attribute on the Association in which to get/set target values
self.collectionname = collectionname
self.attr = attr
def __get__(self, obj, owner):
if obj is None:
return self
storage_key = '_AssocProp_%s' % self.collectionname
try:
return getattr(obj, storage_key)
except AttributeError:
a = AssociationList(obj, self.collectionname, self.attr)
setattr(obj, storage_key, a)
return a
class MyObject(object):
items = AssocProp('associations', 'item')
class Item(object):
myobjs = AssocProp('associations', 'myobj')
mapper(MyObject, objecttable)
mapper(Item, itemtable)
mapper(Association, associationtable, properties={
'myobj':relation(MyObject, backref='associations'),
'item':relation(Item, backref='associations')
})
s = create_session()
o = MyObject()
o.items.append(Item())
o.items.append(Item())
i = Item()
i.myobjs.append(o)
s.save(o)
s.flush()
s.clear()
o = s.query(MyObject).get(o.id)
print o
print o.items
assert len(o.items) == 3
assert o in o.items[1].myobjs
On Nov 1, 2006, at 7:09 PM, Karl Guertin wrote:
On 11/1/06, Michael Bayer [EMAIL PROTECTED] wrote:
(moving to google groups)
Eh, sorry cached email address.
the idea of using an instrumented list subclass is that you *would*
use the association object, and just have the list subclass handle
the references to the association object for you, i.e.:
Simple enough
class MyList(list):
[sqlalchemy] how to redefine a table object
hi there, I am new to SA. For a tool I am creating I am also writing unittests. In these I recreate the same table object repeatedly. Up to now (with 0.28) this worked fine when I added redefine=True. In 0.3 this does not work anymore. How would I remove/update a table from from a MetaData object? How do I clean everything at the start of a unittest? thanks robert --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- begin:vcard fn:robert rottermann n:rottermann;robert email;internet:[EMAIL PROTECTED] tel;work:031 333 10 20 tel;fax:031 333 10 23 tel;home:031 333 36 03 x-mozilla-html:FALSE version:2.1 end:vcard
