1. Relational Databases: Object Relational Mappers - SQLObject BCHB5242009Lecture 17 BCHB524 - 2009 - Edwards

2. Outline • Object relational mappers • Tables as classes, rows as instances • Advantages & disadvantages • Install & configure SQLObject • Minimal SQLObject example • Legacy databases • Exercises BCHB524 - 2009 - Edwards

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

4. Objects have data members or attributes. Store objects in a list oriterable. Abstract awaydetails of underlyingRDBMS ... as Objects BCHB524 - 2009 - 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 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

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 • 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

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

8. Install SQLObject BCHB524 - 2009 - Edwards

9. Install SQLObject • Test! • Open BCHB524 IDLE • Type “import sqlobject” BCHB524 - 2009 - Edwards

10. Minimal SQLObject Example:Define the database model BCHB524 - 2009 - Edwards

11. Minimal SQLObject Example: Populate the database BCHB524 - 2009 - Edwards

12. Minimal SQLObject Example:Access the rows as objects BCHB524 - 2009 - Edwards

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

14. Legacy databases BCHB524 - 2009 - Edwards

15. Legacy databases BCHB524 - 2009 - Edwards

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