Create simple 1:2 relationship with Megrok.rdb and SqlAlchemy (over MySql)¶
| Author: | BorrajaX |
|---|---|
| Version: | unkown |
This howto describes step by step how to create a simple 1:2 relationship using Megrok.rdb and SqlAlchemy and how to split the different classes definition in different Python modules.
I was migrating our current database from an Object-Oriented model (ZopeDB) to a relational model (MySql). At a certain point, I needed to have a Parent class that has two different instances of a Child() class inside. It couldn’t be a list, it needed to be two separate instances. It took me a while to have it working, so I thought someone else could benefit from my experiences.
Step by step¶
I needed to have something that would look like:
from Child import Child
class Parent(object):
def __init__(self):
self._whateverField = "Whatever"
self.child1 = Child()
self.child2 = Child()
The MySql schema (which, in other contexts is sometimes referred to as “database”) was called test2()
In order to move this to a relational model, I needed to do the following:
1) Create a simple file to set up the connection to the database and the metadata (the object that keeps track of the tables, the mapping Object ↔ Table, etc)¶
Tables.py >¶
import grok
import logging
from megrok import rdb
from z3c.saconfig import EngineFactory
from z3c.saconfig.interfaces import IEngineCreatedEvent
from z3c.saconfig.interfaces import IEngineFactory
from zope import component
log = logging.getLogger(__name__)
log.setLevel(logging.DEBUG)
DSN = "mysql://root:*******@localhost/test2?charset=utf8"
engine_factory = EngineFactory(DSN, echo=True)
grok.global_utility(engine_factory, provides=IEngineFactory, direct=True)
zepMetadata = rdb.MetaData()
@grok.subscribe(IEngineCreatedEvent)
def create_engine_created(event):
rdb.setupDatabase(zepMetadata)
component.provideHandler(create_engine_created)
Comments to Tables.py:
- I must say that I was never able to automatically call the
create_engine_created function()(it seems the event was never triggered). That’s why is manually invoked from another module (Test.py) - If you don’t have the logging package installed, substitute log.debug, log.info... by regular prints (after all, this is a very simple howto)
- In the DNS string I substituted my root password by * characters. Make sure you have the right configuration there. Also, be aware that is strongly discouraged using the MySql root user in production. In “real life”, you should access the database with a less privileged user.
2) After setting up the database connection, prepare the two classes itself (Parent and Child):¶
Parent.py >¶
from megrok import rdb
from sqlalchemy import Column
from sqlalchemy import and_
from sqlalchemy.orm import relationship
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from mylibraries.database.tests.Child import Child
from mylibraries.database.tests.Tables import zepMetadata
class Parent(rdb.Model):
rdb.metadata(zepMetadata)
rdb.tablename("parents_table")
rdb.tableargs(schema='test2', useexisting=False)
id = Column("id", Integer, primary_key=True, nullable=False, unique=True)
_whateverField = Column("whatever_field", String(16)) #Irrelevant
child1 = relationship(
"Child",
primaryjoin=lambda: and_((Child.parent_id == Parent.id), (Child.type == "VR")),
uselist=True,
collection_class=list
)
child2 = relationship(
"Child",
primaryjoin=lambda: and_((Child.parent_id == Parent.id), (Child.type == "CC")),
uselist=True,
collection_class=list
)
def __init__(self):
print "Parent __init__"
self._whateverField = "Water"
self.child1 = list()
self.child2 = list()
def addChild(self, child):
if child.type == "VR":
self.child1.append(child)
elif child.type == "CC":
self.child2.append(child)
Comments to Parent.py:
- We are specifying the name of table where we want our Parent instances stored (for persistence) with
rdb.tablename("parents_table")(). - This is not required. If it’s not specified, a parent (with lowercase letters) should be created automatically.
- The two children are going to be lists. One of them will contain children with a type “VR” and the other will contain children with a type “CC” (just because...)
- My modules are stored in the path mylibraries/database/tests. Please, make sure you change this to your own path when importing.
- The lambda (callable) function used in the joins to get the children is useful to get around circular dependencies. It will delay the “check” of the existance of Child and Parent classes until they are actually defined.
- Realize that we are using the rdb.Metadata() instance from Tables.py (not creating a new instance). The metadata is what keeps track of the mappings Class ↔ Tables. It will be automatically filled on each class.
Child.py >¶
from megrok import rdb
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from mylibraries.database.tests.Tables import zepMetadata
class Child(rdb.Model):
rdb.metadata(zepMetadata)
rdb.tablename("children_table")
rdb.tableargs(schema='test2', useexisting=False)
id = Column("id", Integer, primary_key = True)
parent_id = Column("parent_id", Integer, ForeignKey("test2.parents_table.id"))
type = Column("type", String(2))
def __init__(self):
self.type = "VR"
Comments to Child.py:
1) One of the things that took me a while to realize is that, if you are using the directive
rdb.tableargs(schema='schema_name', useexisting=False)(), the ForeignKey needs to be invoked by using an string of the shape “schema_name.table_name.column_name”. If you’re not using said directive, you can simply use “table_name.column_name” (see SqlAlchemy docs1).
- What is done in the __init__ (assigning “VR” to the type) is merely for the example. It doesn’t need to be done that way.
3) This is basically all what is needed for the database definition. Now let’s prepare a file where the classes will be groked, the tables will be actually created and a little test will be performed.¶
Test.py >¶
from grokcore.component.testing import grok_component
import logging
import mylibraries
from mylibraries.database.tests.Child import Child
from mylibraries.database.tests.Parent import Parent
import mylibraries.database.tests.Tables
log = logging.getLogger(__name__)
log.setLevel(logging.DEBUG)
def setComponents():
"""Grok all the classes related to the database"""
grok_component("Parent", Parent)
grok_component("Child", Child)
def createDatabase():
mylibraries.database.tests.Tables.create_engine_created(None)
def fillASampleParent():
parent = Parent()
for i in range(5):
child = Child()
if i % 2 == 0:
child.type = "VR"
else:
child.type = "CC"
parent.addChild(child)
return parent
To test this in our server, let’s go to app.py (where the grokserver is defined) and create a new “page” for the testing.
App.py >¶
#[ . . . ]
# Lots of useful data, classes and methods
class Test(grok.View):
grok.context(Grokserver)
import mylibraries.database.tests.Parent
import mylibraries.database.tests.Child
import mylibraries.database.tests.Test
import mylibraries.database.tests.Tables
from megrok import rdb
def render(self):
session = rdb.Session()
mylibraries.database.tests.Test.setComponents()
mylibraries.database.tests.Test.createDatabase()
parent_sample = mylibraries.database.tests.Test.fillASampleParent()
session.add(parent_sample)
session.flush()
#[ . . . ]
# Another lot of useful data, classes and methods
You should be able to execute that by typing in your browser: http://server_address:port/app_name/test (change server_address, port and app_name by your own values)
After this, 5 new children should have been added to the database.
Further information¶
Basic documentation for MeGrok.
And thanks to all the people who answered my questions in the following threads.
