160 likes | 338 Views
Relational Databases: Object Relational Mappers - SQLObject. BCHB524 2009 Lecture 17 . Outline. Object relational mappers Tables as classes, rows as instances Advantages & disadvantages Install & configure SQLObject Minimal SQLObject example Legacy databases Exercises.
E N D
Relational Databases: Object Relational Mappers - SQLObject BCHB5242009Lecture 17 BCHB524 - 2009 - Edwards
Outline • Object relational mappers • Tables as classes, rows as instances • Advantages & disadvantages • Install & configure SQLObject • Minimal SQLObject example • Legacy databases • Exercises BCHB524 - 2009 - Edwards
Relational Databases • Store information in a table • Rows represent items • Columns represent items' properties or attributes BCHB524 - 2009 - Edwards
Objects have data members or attributes. Store objects in a list oriterable. Abstract awaydetails of underlyingRDBMS ... as Objects BCHB524 - 2009 - Edwards
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 - 2009 - Edwards
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 - 2009 - Edwards
Install SQLObject • My mistake! I thought I packaged it in the installation zip file. • Start a command-line using the BHCB524 batch script • Type “easy_install” to check that this program is available. • Try: c:\BCHB524\Python25\Scripts\easy_install.exeif the command isn’t found. • If easy_install isn’t there at all, need to install it... • Download ez_setup.py from the course data-directory and save to your computer. • http://edwardslab.bmcb.georgetown.edu/teaching/bchb524/2009/data/ez_setup.py • Open the file with IDLE, and run the module. • Then, at BHCB524 command-line prompt: • “easy_install sqlobject”, or • “c:\BCHB524\Python25\Scripts\easy_install.exe sqlobject” BCHB524 - 2009 - Edwards
Install SQLObject BCHB524 - 2009 - Edwards
Install SQLObject • Test! • Open BCHB524 IDLE • Type “import sqlobject” BCHB524 - 2009 - Edwards
Minimal SQLObject Example:Define the database model BCHB524 - 2009 - Edwards
Minimal SQLObject Example: Populate the database BCHB524 - 2009 - Edwards
Minimal SQLObject Example:Access the rows as objects BCHB524 - 2009 - Edwards
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 - 2009 - Edwards
Legacy databases BCHB524 - 2009 - Edwards
Legacy databases BCHB524 - 2009 - Edwards
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. BCHB524 - 2009 - Edwards