ORM Pattern
Relational databases and OO memory models use different paradigms. Mapping between one and the other is often necessary but usually ends up being fiddly unless an auto-mapping ORM tool is used.
How an ORM works
Let’s face it - a lot of developers prefer to just code classes - that’s what OO is all about. Having then to persist those classes into tables creates all sorts of fiddly mapping code. If you don’t need to persist into tables - fine. But if you do need to persist into tables, one way to reduce your workload is to use a tool like SQLObject - you simply write your code in a certain way and the tables are automatically maintained.
- Each class becomes a table.
- Each attribute on a class is a column in a table.
The ORM tool automatically causes the setting of attributes on objects to have a wonderful side effect: the values are written to the database table. And conversely, pulling information out from your object triggers the appropriateSQL calls to retrieve the information from the table. You don’t have to deal with sql syntax yourself. However you can take advantage of the fact that you now have a sql backend, to make sql-like queries - thus you can save coding effort. You have all the power of SQL at your disposal now, you might as well use take advantage of it! You don’t have to use precise SQL syntax but you need to use something very much like it
ORM using SQLObject
In SQLObject (a Python library) you would write things like:
ps = Person.select("""address.postcode LIKE '3186'""", clauseTables=['address'])
print list(ps)
ORM (object relational mapping) tools usually let you choose what style of ‘back-end’ database to use with a configuration switch - e.g. develop on sql-lite and deploy on mysql - all without changing any of your code.
Presentation
Object Relational Mapping Pattern - using Sql Object - slideshare
Example
Before using SQLObject
This is an example of some Person, Cubicle and Order classes. Persons live in Cubicles and can place one or more orders. No database activity is involved here - its all just regular python code with pointers and lists.
# BEFORE the use of a database
class Cubicle:
def __init__(self, location="unknown"):
self.location = location
self.occupant = None
def SetOccupant(self, person):
# Evict any previous occupant
if self.occupant:
self.occupant.cubicle = None
self.occupant = person # New occupant wired in
person.cubicle = self # back pointer
class Person:
def __init__(self, firstname, lastname):
self.firstname = firstname
self.lastname = lastname
self.cubicle = None
self.address = None
self.orders = []
self.addresses = []
def AddOrder(self, order):
self.orders.append(order) # one to many
order.person = self # back pointer
def SetAddress(self, address):
self.addresses.append(address) # many to many
address.residents.append(self) # back pointer (note the 'append' cos many to many)
class Order:
def __init__(self, orderNumber, description):
self.orderNumber = orderNumber
self.description = description
class Address:
def __init__(self, street, suburb, postcode):
self.street = street
self.suburb = suburb
self.postcode = postcode
self.residents = []
# Test One to one
cubicle1 = Cubicle("North Wing D4")
tom = Person(firstname="Tom", lastname="Jones")
cubicle1.SetOccupant(tom)
assert cubicle1.occupant == tom
# Test One to many
o1 = Order(orderNumber="12345", description="new ipaq")
o2 = Order(orderNumber="12346", description="new ipod")
tom.AddOrder(o1)
tom.AddOrder(o2)
assert len(tom.orders) == 2
assert o1 in tom.orders
assert o2 in tom.orders
# Test Many to many
angelina = Person(firstname="Angelina", lastname="Jolie")
a1 = Address("Fox Studios", "California", 3186) # tom and angelina both work here
a2 = Address("Brads Place", "Manhattan", 40004)
angelina.SetAddress(a1)
angelina.SetAddress(a2)
tom.SetAddress(a1)
assert a1 in angelina.addresses
assert angelina in a1.residents
assert angelina in a2.residents
assert tom in a1.residents
assert tom not in a2.residents
################## Now do some more complex manipulations #########
# Move angelina into the North Wing D4 cubicle and
# move Tom into a new cubicle
cubicle1.SetOccupant(angelina)
assert cubicle1.occupant == angelina
assert tom.cubicle == None
cubicle2 = Cubicle("West Wing D5")
cubicle2.SetOccupant(tom)
assert tom.cubicle == cubicle2
print 'Done!'
Output:
Done!
Code - After SQLObject
Now for the fascinating transformation. We refactor the code to use SQLObject and we end up with the same logic, but as a bonus we have persistence in a sql database. And its not so hard either!
# AFTER - the database version - notice we import sqlobject
from sqlobject import *
from sqlobject.sqlite import builder; SQLiteConnection = builder()
conn = SQLiteConnection('person.db', debug=False)
class Cubicle(SQLObject):
_connection = conn
location = StringCol(length=20, default="unknown")
occupant = ForeignKey('Person', default=None)
def SetOccupant(self, person):
# Evict any previous occupant
if self.occupant:
self.occupant.cubicle = None
self.occupant = person # New occupant wired in
person.cubicle = self # back pointer
class Person(SQLObject):
_connection = conn
firstname = StringCol(length=20)
lastname = StringCol(length=20)
cubicle = ForeignKey('Cubicle', default=None)
orders = MultipleJoin('GiftOrder')
addresses = RelatedJoin('Address')
def AddOrder(self, giftOrder):
#self.orders.append(giftOrder) # one to many # SQL OBJECT doesn't need this
giftOrder.person = self # back pointer ** becomes the primary info sqlobject goes on
def SetAddress(self, address):
#self.addresses.append(address) # many to many # SQL OBJECT doesn't need this
#address.residents.append(self) # back pointer (note the 'append' cos many to many) # SQL OBJECT doesn't need this
address.addPerson(self) # SQLobject created this "addWHATEVER" method for us
class GiftOrder(SQLObject):
_connection = conn
orderNumber = IntCol()
description = StringCol()
person = ForeignKey('Person', default=None)
class Address(SQLObject):
_connection = conn
street = StringCol(length=20)
suburb = StringCol(length=20)
postcode = StringCol(length=20)
residents = RelatedJoin('Person')
#def _init(self):
# SQLObject._init(self, *args, **kw)
# self.postcodesDict = {'2323':'Brighton','22222':'Werribee'}
Cubicle.dropTable(True)
Cubicle.createTable()
Person.dropTable(True)
Person.createTable()
GiftOrder.dropTable(True)
GiftOrder.createTable()
Address.dropTable(True)
Address.createTable()
# Test One to one
cubicle1 = Cubicle(location="North Wing D4")
tom = Person(firstname="Tom", lastname="Jones")
cubicle1.SetOccupant(tom)
assert cubicle1.occupant == tom
# Test One to many
o1 = GiftOrder(orderNumber=12345, description="new ipaq")
o2 = GiftOrder(orderNumber=12346, description="new ipod")
tom.AddOrder(o1)
tom.AddOrder(o2)
assert len(tom.orders) == 2
assert o1 in tom.orders
assert o2 in tom.orders
# Test Many to many
angelina = Person(firstname="Angelina", lastname="Jolie")
a1 = Address(street="Fox Studios", suburb="California", postcode="3186") # tom and angelina both work here
a2 = Address(street="Brads Place", suburb="Manhattan", postcode="40004")
angelina.SetAddress(a1)
angelina.SetAddress(a2)
tom.SetAddress(a1)
assert a1 in angelina.addresses
assert angelina in a1.residents
assert angelina in a2.residents
assert tom in a1.residents
assert tom not in a2.residents
################## Now do some more complex manipulations #########
# Move angelina into the North Wing D4 cubicle and
# move Tom into a new cubicle
cubicle1.SetOccupant(angelina)
assert cubicle1.occupant == angelina
assert tom.cubicle == None
cubicle2 = Cubicle(location="West Wing D5")
cubicle2.SetOccupant(tom)
assert tom.cubicle == cubicle2
# Now SQLOBJECT lets us do other magic things, that leverage relational db technology
p = Person.get(1)
print p
#ps = Person.select(Person.q.firstName=="John")
#print list(ps)
#ps = Person.select("""address.id = person.id AND
# address.postcode LIKE '40004%'""",
# clauseTables=['address'])
ps = Person.select("""address.postcode LIKE '3186'""",
clauseTables=['address'])
print list(ps)
print 'all people'
ps = Person.select()
print list(ps)
print 'Done!'
Output:
What sort of tables are created?
You can browse the resulting database tables (in this case sqllite tables) using the SQLite Database Browser. Here are some screenshots of the tables we have created:
this is the table structure:
All the above tables and content were created by the Python code!
Alternatives
You may also be interested in Sqlalchemy for Python which is very powerful and flexible, perhaps getting a bit complex. Django of course has a built in ORM and is much easier to use, though you can only use it in Django web apps.
Then there are ORM frameworks for Java like hibernate and many, many others. Just google for ORM.
And if you are into .NET then the “low hanging fruit” solution of LINQ is worth looking at.
The point of this article has been to get you to see the essence of what an ORM does, with the minimal amount of code.