1 / 18

Relational Databases: Object Relational Mappers - SQLObject

Relational Databases: Object Relational Mappers - SQLObject. BCHB524 2008 Lecture 13 . Outline. Object relational mappers Tables as classes, rows as instances Advantages & disadvantages Install & configure SQLObject Minimal SQLObject example Revisit taxonomy example

ranae
Download Presentation

Relational Databases: Object Relational Mappers - SQLObject

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Databases: Object Relational Mappers - SQLObject BCHB5242008Lecture 13 BCHB524 - 2008 - Edwards

  2. Outline • Object relational mappers • Tables as classes, rows as instances • Advantages & disadvantages • Install & configure SQLObject • Minimal SQLObject example • Revisit taxonomy example • Multiple and related Joins • Exceptions • Legacy databases • Exercises • Lecture 11 exercises BCHB524 - 2008 - Edwards

  3. Relational Databases • Store information in a table • Rows represent items • Columns represent items' properties or attributes Table: Students BCHB524 - 2008 - Edwards

  4. Objects have data members or attributes. Store objects in a list oriterable. Abstract awaydetails of underlyingRDBMS ... as Objects s1 = Student() s1.guid = 11111 s1.bioTrack = True s1.givenName = 'Robinder' s1.familyName = 'Gauba' s1.preferredName = 'Robin' # initialize s2, and s3 studentTable = [ s1, s2, s3 ] for st in studentTable: if st.guid == 1111: print st.givenName,st.familyName BCHB524 - 2008 - Edwards

  5. 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 Cons: Execution speed Sometimes forced into poor strategies Optimal SQL construct may be impossible Tend not to take advantage of RDBMS quirks. Difficult to apply to legacy databases Pros and Cons BCHB524 - 2008 - Edwards

  6. 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 • SQLObject is almost completely devoid of SQL and is almost entirely objecty. • See http://sqlobject.org. BCHB524 - 2008 - Edwards

  7. Minimal SQLObject Example from sqlobject import * import os.path dbfile = 'student.db3' # Magic formatting for database URI conn_str = os.path.abspath(dbfile) conn_str = conn_str.replace('\\','/').replace(':','|') conn_str = 'sqlite:/'+ conn_str sqlhub.processConnection = connectionForURI(conn_str) class Student(SQLObject): guid = IntCol() givenName = StringCol() familyName = StringCol() BCHB524 - 2008 - Edwards

  8. Minimal SQLObject Example from model import Student Student.createTable() # Add some rows st = Student(guid=11111, givenName='Robinder', familyName='Gauba') st = Student(guid=22222, givenName='Xiaoyu', familyName='Liu') st = Student(guid=33333, givenName='Mustafa', familyName='Djani') # Change student #1 st = Student.get(1) st.givenName = 'Robin' # Retrieve and print all students for st in Student.select(): print st.id, st.guid, st.givenName, st.familyName BCHB524 - 2008 - Edwards

  9. Minimal SQLObject Example from model import Student # Select student with GUID == 11111 for st in Student.select(Student.q.guid == 11111): print st.id, st.guid, st.givenName, st.familyName # Select students with family names that start with D for st in Student.select(Student.q.familyName.startswith("D")): print st.id, st.guid, st.givenName, st.familyName # Lookup by id, exactly 1 student with each id st = Student.get(1) print st.id, st.guid, st.givenName, st.familyName # Shortcut for select, use [0] to get first item st = Student.selectBy(familyName='Liu')[0] BCHB524 - 2008 - Edwards

  10. Back to the Taxonomy example def init(new=False): # Set up database connection if new: # create empty Taxonomy and Name tables class Taxonomy(SQLObject): taxid = IntCol(alternateID=True) scientific_name = StringCol() rank = StringCol() parent = ForeignKey("Taxonomy") class Name(SQLObject): taxonomy = ForeignKey("Taxonomy") name = StringCol() name_class = StringCol() BCHB524 - 2008 - Edwards

  11. Back to the Taxonomy example import sys from model import * init(new=True) # Read in the names and pick out the scientific names h = open(sys.argv[1]) for l in h: l = l.strip('\t|\n') sl = l.split('\t|\t') taxid = int(sl[0]) name_class = sl[3] name = sl[1] if name_class == 'scientific name': t = Taxonomy(taxid=taxid, scientific_name=name, rank=None, parent=None) h.close() BCHB524 - 2008 - Edwards

  12. Back to the Taxonomy example import sys from model import * init(new=False) # Read in the names and put then into the names table. # Presumes that the taxonomy table has entries for all NCIB ids. h = open(sys.argv[1]) for l in h: l = l.strip('\t|\n') sl = l.split('\t|\t') taxid = int(sl[0]) name_class = sl[3] name = sl[1] t = Taxonomy.byTaxid(taxid) n = Name(name=name, name_class=name_class, taxonomy=t) h.close() BCHB524 - 2008 - Edwards

  13. Back to the Taxonomy example import sys from model import * init(new=False) # Read in the parent and rank and put then into the taxonomy table. # Presumes that the taxonomy table has entries for all NCIB ids. h = open(sys.argv[1]) for l in h: l = l.strip('\t|\n') sl = l.split('\t|\t') taxid = int(sl[0]) parent_taxid = int(sl[1]) rank = sl[2] t = Taxonomy.byTaxid(taxid) p = Taxonomy.byTaxid(parent_taxid) t.parent = p t.rank = rank h.close() BCHB524 - 2008 - Edwards

  14. Back to the Taxonomy example • Each taxonomy entry can have multiple names • Many names can point (ForeignKey) to a single taxonomy entry • name → taxonomy is easy... • taxonomy → list of names requires a select statement from model import * hs = Taxonomy.byTaxid(9606) names = Name.selectBy(taxnonomy=hs) for n in names: print n.name BCHB524 - 2008 - Edwards

  15. Back to the Taxonomy example • This relationship (one-to-many) is called a multiple join. • Related joins(many-to-many)too... model.py class Taxonomy(SQLObject) # other data members ... names = MultipleJoin("Name") children = MultipleJoin("Taxonomy",joinColumn='parent_id') from model import * hs = Taxonomy.byTaxid(9606) for n in hs.names: print n.name for c in hs.children: print c.scientific_name BCHB524 - 2008 - Edwards

  16. Exceptions • What happens when the row isn't in the table? from model import * try: hs = Taxonomy.get(7921) hs = Taxonomy.byTaxid(9606) except SQLObjectNotFound: # if row id 7921 / Tax id 9606 is not in table... results = Taxonomy.selectBy(taxid=9606) if results.count() == 0: # No rows satisfy the constraint! try: first_item = results[0] except IndexError: # No first item in the results BCHB524 - 2008 - Edwards

  17. Legacy databases • If the legacy database is well-structured, SQLObject can figure out the definitions • Have to fill in MultipleJoins yourself • Needs an integer id per row... model.py class Taxonomy(SQLObject): class sqlmeta: fromDatabase = True class Name(SQLObject): class sqlmeta: fromDatabase = True BCHB524 - 2008 - Edwards

  18. Lab exercises • Try each of the examples shown in these slides. • Read through the SQLObject documentation • Write a python program using SQLObject to lookup the scientific name for a user-supplied organism name. • Write a python program using SQLObject to find the lineage of a user-supplied organism name. BCHB524 - 2008 - Edwards

More Related