relational databases object relational mappers sqlobject n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational Databases: Object Relational Mappers - SQLObject PowerPoint Presentation
Download Presentation
Relational Databases: Object Relational Mappers - SQLObject

Loading in 2 Seconds...

play fullscreen
1 / 15

Relational Databases: Object Relational Mappers - SQLObject - PowerPoint PPT Presentation


  • 177 Views
  • Uploaded on

Relational Databases: Object Relational Mappers - SQLObject. BCHB524 2013 Lecture 22. Outline. Object relational mappers Tables as classes, rows as instances Advantages & disadvantages Minimal SQLObject example Legacy databases Exercises. Relational Databases.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Relational Databases: Object Relational Mappers - SQLObject' - karif


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
relational databases object relational mappers sqlobject

Relational Databases: Object Relational Mappers - SQLObject

BCHB5242013 Lecture 22

BCHB524 - 2013 - Edwards

outline
Outline
  • Object relational mappers
    • Tables as classes, rows as instances
    • Advantages & disadvantages
  • Minimal SQLObject example
  • Legacy databases
  • Exercises

BCHB524 - 2013 - Edwards

relational databases
Relational Databases
  • Store information in a table
    • Rows represent items
    • Columns represent items' properties or attributes

BCHB524 - 2013 - Edwards

as objects
Objects have data members or attributes.

Store objects in a list oriterable.

Abstract awaydetails of underlyingRDBMS

... as Objects

c1 = Country()c1.name = 'Brazil'c1.continent = 'South America'c1.region = 'South America'c1.surfaceArea = 8547403c1.population = 170115000c1.gnp = 776739# initialize c2, ..., c6countryTable = [ c1, c2, c3, c4, c5, c6 ]for cnty in countryTable:if cnty.population > 100000000:print cnty.name, cnty.population

BCHB524 - 2013 - Edwards

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

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

Pros and Cons

BCHB524 - 2013 - Edwards

sqlobject
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.

BCHB524 - 2013 - Edwards

minimal sqlobject example define the database model model py
Minimal SQLObject Example:Define the database model (model.py)

from sqlobject import *import os.pathdbfile = 'myworld.db3'# Magic formatting for database URIconn_str = os.path.abspath(dbfile)conn_str = 'sqlite:'+ conn_strsqlhub.processConnection = connectionForURI(conn_str)classCountry(SQLObject):    name = StringCol()    continent = StringCol()    region = StringCol()    surfaceArea = FloatCol()    population = IntCol()    gnp = FloatCol()

BCHB524 - 2013 - Edwards

minimal sqlobject example populate the database
Minimal SQLObject Example: Populate the database

from model import Country# Initialize the tableCountry.createTable()# Add some rowsc = 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 countriesfor c in Country.select():print c.id, c.name, c.continent, c.gnp

BCHB524 - 2013 - Edwards

minimal sqlobject example access change the database
Minimal SQLObject Example: Access/Change the database

from model import Country# Change country #6c = Country.get(6)c.name = 'United States of America'# Retrieve and print all countriesfor c in Country.select():print c.id, c.name, c.continent, c.gnp

BCHB524 - 2013 - Edwards

minimal sqlobject example access the rows as objects
Minimal SQLObject Example:Access the rows as objects

from model import Country# Select countries with more than 500,000,000 in populationfor 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 idc = 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

BCHB524 - 2013 - Edwards

legacy databases
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
    • 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 - 2013 - Edwards

legacy databases1
Legacy databases

from sqlobject import *import os.pathdbfile = 'taxa.db3'conn_str = os.path.abspath(dbfile)conn_str = 'sqlite:'+ conn_strsqlhub.processConnection = connectionForURI(conn_str)classTaxonomy(SQLObject):classsqlmeta:        idName = "tax_id"        fromDatabase = True    names = MultipleJoin('Name', joinColumn="tax_id")className(SQLObject):classsqlmeta:        fromDatabase = True    taxa = ForeignKey('Taxonomy', dbName="tax_id")

BCHB524 - 2013 - Edwards

legacy databases2
Legacy databases

# Set up data-modelfrom model import *# get homo sapienshs1 = Taxonomy.get(9606)# select the Taxonomy object# with scientific name Homo sapienshs2 = Taxonomy.selectBy(scientificName='Homo sapiens')[0]# get the name humantry:    hsname = Name.selectBy(name='human')[0]exceptIndexError:print"Can't find name 'human'"    sys.exit(1)# get the Taxonomy object from the Name object# Uses the magic Foreign Key attributehs3 = hsname.taxa# hs1, hs2, hs3 the same!print hs1print hs2print hs3

BCHB524 - 2013 - Edwards

legacy databases3
Legacy databases

# Set up data-modelfrom model import *# get homo sapienshs = Taxonomy.get(9606)# find rows in the Name table with taxa the same as hs# Use ForeignKey to create condition, equality test# between objectscondition = (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 iterablefor n in hs.names:print n.name, "|", n.nameClass# More general conditionscondition = Name.q.name.startswith('Da')for n in Name.select(condition):print n.name, "|", n.nameClass

BCHB524 - 2013 - Edwards

exercises
Exercises
  • Read through the SQLObject documentation
  • Write a python program using SQLObject to lookup the scientific name for a user-supplied organism name.

BCHB524 - 2013 - Edwards