MySQL, Storm, and Relationships

01-one-to-many.sql

CREATE TABLE parent (
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   PRIMARY KEY (id)
   ) ENGINE=INNODB;

CREATE TABLE child (
   id INT NOT NULL AUTO_INCREMENT,
   parent_id INT,
   name VARCHAR(50),
   PRIMARY KEY (id),
   INDEX par_ind (parent_id),
   FOREIGN KEY (parent_id) REFERENCES parent(id)
   ) ENGINE=INNODB;

02-models.py

from storm.locals import Int, Storm, Reference, ReferenceSet, Unicode


class Parent(Storm):
   __storm_table__ = 'parent'
   id = Int(primary=True)
   name = Unicode()


class Child(Storm):
   __storm_table__ = 'child'
   id = Int(primary=True)
   name = Unicode()
   parent_id = Int()
   parent = Reference(parent_id, Parent.id)


Parent.children = ReferenceSet(Parent.id, Child.parent_id)

03-interactive.py

>>> from storm.locals import *
>>> p1 = Parent()
>>> p1.name = u'parent 1'
>>> store.add(p1)
>>> store.flush()
>>> print p1.id

04-interactive.py

>>> child1 = Child()
>>> child1.name = u'child 1'
>>> store.add(child1)
>>> child1.parent = p1
>>> store.flush()
>>> print child1.id

05-interactive.py

>>> child2 = Child()
>>> child2.name = u'child 2'
>>> store.add(child2)
>>> p1.children.add(child2)
>>> store.flush()
>>> print child2.id

06-interactive.py

>>> store.commit()
>>> p1.children.count()
2
>>> for ch in p1.children:
...     print "id: %s, name: %s" % (ch.id, ch.name)
...
id: 1, name: child 1
id: 2, name: child 2