Transcript Slides - Edwards Lab

Relational Databases:
Object Relational
Mappers - SQLObject
BCHB524
2014
Lecture 22
11/14/2014
BCHB524 - 2014 - Edwards
Outline

Object relational mappers





Tables as classes, rows as instances
Advantages & disadvantages
Minimal SQLObject example
Legacy databases
Exercises
11/14/2014
BCHB524 - 2014 - Edwards
2
Relational Databases

Store information in a table
Rows represent items
Columns represent items' properties or
attributes


Name
Continent
Region
Surface Area
Population
GNP
Brazil
South America
South America
8547403
170115000
776739
Indonesia
Asia
Southeast Asia
1904569
212107000
84982
India
Asia
Southern and Central Asia
3287263
1013662000
447114
China
Asia
Eastern Asia
9572900
1277558000
982268
Pakistan
Asia
Southern and Central Asia
796095
156483000
61289
United States
North America
North America
9363520
278357000
8510700
11/14/2014
BCHB524 - 2014 - Edwards
3
... as Objects



Objects have
data members
or attributes.
Store objects
in a list or
iterable.
Abstract away
details of
underlying
RDBMS
11/14/2014
c1 = Country()
c1.name = 'Brazil'
c1.continent = 'South America'
c1.region = 'South America'
c1.surfaceArea = 8547403
c1.population = 170115000
c1.gnp = 776739
# initialize c2, ..., c6
countryTable = [ c1, c2, c3, c4, c5, c6 ]
for cnty in countryTable:
if cnty.population > 100000000:
print cnty.name, cnty.population
BCHB524 - 2014 - Edwards
4
Pros and Cons
Pros:
 Learn one language
 Ease of development
 Simplified joins
 One set of data-types
 Easy storage of higher-level
objects
 Can apply the power of
python as necessary
 Abstract away RDBMS
 Distribute CPU load
11/14/2014
Cons:
 Execution speed
 Sometimes forced into poor
strategies
 Optimal SQL construct may
be impossible
 Tend not to take advantage
of RDBMS quirks.
 Can be difficult to apply to
legacy databases
BCHB524 - 2014 - Edwards
5
SQLObject


Just one of many object-relational mappers
Each tool makes different tradeoffs in






Table/row/attribute abstraction
How much SQL you need to know
Overhead
Ease of adapting to legacy databases
SQLObject is almost completely devoid of
SQL and is almost entirely "objecty".
See http://sqlobject.org.
11/14/2014
BCHB524 - 2014 - Edwards
6
Minimal SQLObject Example:
Define the database model (model.py)
from sqlobject import *
import os.path
dbfile = 'myworld.db3'
# Magic formatting for database URI
conn_str = os.path.abspath(dbfile)
conn_str = 'sqlite:'+ conn_str
sqlhub.processConnection = connectionForURI(conn_str)
class Country(SQLObject):
name = StringCol()
continent = StringCol()
region = StringCol()
surfaceArea = FloatCol()
population = IntCol()
gnp = FloatCol()
11/14/2014
BCHB524 - 2014 - Edwards
7
Minimal SQLObject Example:
Populate the database
from model import Country
# Initialize the table
Country.createTable()
# Add some rows
c = Country(name="Brazil", continent="South America",
region="South America", surfaceArea=8547403,
population=170115000, gnp=776739)
c = Country(name="China", continent="Asia",
region="Eastern Asia", surfaceArea=9572900,
population=1277558000, gnp=982268)
# ...
c = Country(name="United States", continent="North America",
region="North America", surfaceArea=9363520,
population=278357000, gnp=8510700)
# Retrieve and print all countries
for c in Country.select():
print c.id, c.name, c.continent, c.gnp
11/14/2014
BCHB524 - 2014 - Edwards
8
Minimal SQLObject Example:
Access/Change the database
from model import Country
# Change country #6
c = Country.get(6)
c.name = 'United States of America'
# Retrieve and print all countries
for c in Country.select():
print c.id, c.name, c.continent, c.gnp
11/14/2014
BCHB524 - 2014 - Edwards
9
Minimal SQLObject Example:
Access the rows as objects
from model import Country
# Select countries with more than 500,000,000 in population
for c in Country.select(Country.q.population >= 500000000):
print "A:", c.id, c.name, c.population
# Select countries that start with 'U'
for c in Country.select(Country.q.name.startswith("U")):
print "B:", c.id, c.name, c.population
# Lookup by id, exactly 1 country with each id
c = Country.get(5)
print "C:", c.id, c.name, c.population
# Get exception for bad id
# c = Country.get(100)
# Shortcut for select, countries with continent == 'Asia'
for c in Country.selectBy(continent = 'Asia'):
print "D:", c.id, c.name, c.population
11/14/2014
BCHB524 - 2014 - Edwards
10
Legacy databases


If the legacy database is well-structured, SQLObject
can figure out (most of) the definitions
If there is no id column...



Need to tell SQLObject what to use for the ID.
May need to specify the id at instantiation time.
Have to fill in MultipleJoins and ForeignKeys
yourself



11/14/2014
Need to declare which columns in two different tables
should correspond.
Enables SQLObject to make relationships explicit
Enables SQLObject to turn joins into lists
BCHB524 - 2014 - Edwards
11
Legacy databases
from sqlobject import *
import os.path
dbfile = 'taxa.db3'
conn_str = os.path.abspath(dbfile)
conn_str = 'sqlite:'+ conn_str
sqlhub.processConnection = connectionForURI(conn_str)
class Taxonomy(SQLObject):
class sqlmeta:
idName = "tax_id"
fromDatabase = True
names = MultipleJoin('Name', joinColumn="tax_id")
class Name(SQLObject):
class sqlmeta:
fromDatabase = True
taxa = ForeignKey('Taxonomy', dbName="tax_id")
11/14/2014
BCHB524 - 2014 - Edwards
12
Legacy databases
# Set up data-model
from model import *
# get homo sapiens
hs1 = Taxonomy.get(9606)
# select the Taxonomy object
# with scientific name Homo sapiens
hs2 = Taxonomy.selectBy(scientificName='Homo sapiens')[0]
# get the name human
try:
hsname = Name.selectBy(name='human')[0]
except IndexError:
print "Can't find name 'human'"
sys.exit(1)
# get the Taxonomy object from the Name object
# Uses the magic Foreign Key attribute
hs3 = hsname.taxa
# hs1, hs2, hs3 the same!
print hs1
print hs2
print hs3
11/14/2014
BCHB524 - 2014 - Edwards
13
Legacy databases
# Set up data-model
from model import *
# get homo sapiens
hs = Taxonomy.get(9606)
# find rows in the Name table with taxa the same as hs
# Use ForeignKey to create condition, equality test
# between objects
condition = (Name.q.taxa == hs)
for n in Name.select(condition):
print n
# find rows in the Name table corresonding to hs
# Easy shortcut, using MultipleJoin iterable
for n in hs.names:
print n.name, "|", n.nameClass
# More general conditions
condition = Name.q.name.startswith('Da')
for n in Name.select(condition):
11/14/2014 print n.name, "|", n.nameClass
BCHB524 - 2014 - Edwards
14
Exercises

Read through the SQLObject documentation

Write a python program using SQLObject to
lookup the scientific name for a user-supplied
organism name.
11/14/2014
BCHB524 - 2014 - Edwards
15