Section 21.3. Object-Relational Managers (ORMs)

21.3. Object-Relational Managers (ORMs)

As seen in the previous section, a variety of different database systems are available today, and most of them have Python interfaces to allow you to harness their power. The only drawback to those systems is the need to know SQL. If you are a programmer who feels more comfortable with manipulating Python objects instead of SQL queries, yet still want to use a relational database as your data backend, then you are a great candidate to be a user of ORMs.

21.3.1. Think Objects, Not SQL

Creators of these systems have abstracted away much of the pure SQL layer and implemented objects in Python that you can manipulate to accomplish the same tasks without having to generate the required lines of SQL. Some systems allow for more flexibility if you do have to slip in a few lines of SQL, but for the most part, you can avoid almost all the general SQL required.

Database tables are magically converted to Python classes with columns and features as attributes and methods responsible for database operations. Setting up your application to an ORM is somewhat similar to that of a standard database adapter. Because of the amount of work that ORMs perform on your behalf, some things are actually more complex or require more lines of code than using an adapter directly. Hopefully, the gains you achieve in productivity make up for a little bit of extra work.

21.3.2. Python and ORMs

The most well-known Python ORMs today are SQLAlchemy and SQLObject. We will give you examples of SQLAlchemy and SQLObject because the systems are somewhat disparate due to different philosophies, but once you figure these out, moving on to other ORMs is much simpler.

Some other Python ORMs include PyDO/PyDO2, PDO, Dejavu, PDO, Durus, QLime, and ForgetSQL. Larger Web-based systems can also have their own ORM component, i.e., WebWare MiddleKit and Django's Database API. Note that "well-known" does not mean "best for your application." Although these others were not included in our discussion, that does not mean that they would not be right for your application.

21.3.3. Employee Role Database Example

We will port our user shuffle application ushuffle_db.py to both SQLAlchemy and SQLObject below. MySQL will be the backend database server for both. You will note that we implement these as classes because there is more of an object "feel" to using ORMs as opposed to using raw SQL in a database adapter. Both examples import the set of NAMES and the random name chooser from ushuffle_db.py. This is to avoid copying-and-pasting the same code everywhere as code reuse is a good thing.

SQLAlchemy

We start with SQLAlchemy because its interface is somewhat closer to SQL than SQLObject's interface. SQLAlchemy abstracts really well to the object world but does give you more flexibility in issuing SQL if you have to. You will find both of these ORMs (Examples 21.2 and 21.3) very similar in terms of setup and access, as well as being of similar size, and both shorter than ushuffle_db.py (including the sharing of the names list and generator used to randomly iterate through that list).

Example 21.2. SQLAlchemy ORM Example (ushuffle_sa.py)

This "user shuffle" application features SQLAlchemy paired up with the MySQL database as its backend.

 1  #!/usr/bin/env python  2  3  import os  4  from random import randrange as rrange  5  from sqlalchemy import *  6  from ushuffle_db import NAMES, randName  7  8  FIELDS = ('login', 'uid', 'prid')  9  DBNAME = 'test'  10 COLSIZ = 10  11  12 class MySQLAlchemy(object):  13     def __init__(self, db, dbName):  14         import MySQLdb  15         import _mysql_exceptions  16         MySQLdb = pool.manage(MySQLdb)  17         url = 'mysql://db=%s' % DBNAME  18         eng = create_engine(url)  19         try:  20             cxn = eng.connection()  21         except _mysql_exceptions.OperationalError, e:  22             eng1 = create_engine('mysql://user=root')  23             try:  24                  eng1.execute('DROP DATABASE %s' % DBNAME)  25             except _mysql_exceptions.OperationalError, e:  26                  pass  27             eng1.execute('CREATE DATABASE %s' % DBNAME)  28             eng1.execute(  29             "GRANT ALL ON %s.* TO ''@'localhost'" % DBNAME)  30             eng1.commit()  31             cxn = eng.connection()  32  33         try:  34             users = Table('users', eng, autoload=True)  35         except exceptions.SQLError, e:  36             users = Table('users', eng,  37                 Column('login', String(8)),  38                 Column('uid', Integer),  39                 Column('prid', Integer),  40                 redefine=True)  41  42         self.eng = eng  43         self.cxn = cxn  44         self.users = users  45  46     def create(self):  47         users = self.users  48            try:  49             users.drop()  50         except exceptions.SQLError, e:  51             pass  52          users.create()  53  54     def insert(self):  55         d = [dict(zip(FIELDS,  56         [who, uid, rrange(1,5)])) for who, uid in randName()]  57         return self.users.insert().execute(*d).rowcount  58  59     def update(self):  60         users = self.users  61         fr = rrange(1,5)  62         to = rrange(1,5)  63         return fr, to, \  64     users.update(users.c.prid==fr).execute(prid=to).rowcount  65  66     def delete(self):  67         users = self.users  68         rm = rrange(1,5)  69         return rm, \  70     users.delete(users.c.prid==rm).execute().rowcount  71  72     def dbDump(self):  73         res = self.users.select().execute()  74         print '\n%s%s%s' % ('LOGIN'.ljust(COLSIZ),  75             'USERID'.ljust(COLSIZ), 'PROJ#'.ljust(COLSIZ))  76         for data in res.fetchall():  77             print '%s%s%s' % tuple([str(s).title().ljust (COLSIZ) for s in data])  78  79     def __getattr__(self, attr):  80         return getattr(self.users, attr)  81  82     def finish(self):  83         self.cxn.commit()  84         self.eng.commit()  85  86  def main():  87     print '*** Connecting to %r database' % DBNAME  88     orm = MySQLAlchemy('mysql', DBNAME)  89  90     print '\n*** Creating users table'  91     orm.create()  92  93     print '\n*** Inserting names into table'  94     orm.insert()  95     orm.dbDump()  96  97        print '\n*** Randomly moving folks',  98     fr, to, num = orm.update()  99     print 'from one group (%d) to another (%d)' % (fr, to)  100    print '\t(%d users moved)' % num  101    orm.dbDump()  102  103    print '\n*** Randomly choosing group',  104    rm, num = orm.delete()  105    print '(%d) to delete' % rm  106    print '\t(%d users removed)' % num  107    orm.dbDump()  108  109    print '\n*** Dropping users table'  110    orm.drop()  111    orm.finish()  112  113  if__name__ == '__main__':  114    main()

Example 21.3. SQLObject ORM Example (ushuffle_so.py)

This "user shuffle" application features SQLObject paired up with the MySQL database as its backend.

 1   #!/usr/bin/env python  2  3   import os  4   from random import randrange as rrange  5   from sqlobject import *  6   from ushuffle_db import NAMES, randName  7  8   DBNAME = 'test'  9   COLSIZ = 10  10  FIELDS = ('login', 'uid', 'prid')  11  12  class MySQLObject(object):  13      def __init__(self, db, dbName):  14           import MySQLdb  15           import _mysql_exceptions  16           url = 'mysql://localhost/%s' % DBNAME  17  18           while True:  19               cxn = connectionForURI(url)  20               sqlhub.processConnection = cxn  21               #cxn.debug = True  22              try:  23                   class Users(SQLObject):  24                       class sqlmeta:  25                            fromDatabase = True  26                       login = StringCol(length=8)  27                       uid = IntCol()  28                       prid = IntCol()  29                   break  30           except _mysql_exceptions.ProgrammingError, e:  31                class Users(SQLObject):  32                    login = StringCol(length=8)  33                    uid = IntCol()  34                    prid = IntCol()  35                break  36           except _mysql_exceptions.OperationalError, e:  37                  cxn1 = sqlhub.processConnection= connectionForURI('mysql://root@localhost')  38               cxn1.query("CREATE DATABASE %s" % DBNAME)  39               cxn1.query("GRANT ALL ON %s.* TO ''@'    localhost'" % DBNAME)  40               cxn1.close()  41         self.users = Users  42         self.cxn = cxn  43  44   def create(self):  45        Users = self.users  46         Users.dropTable(True)  47      Users.createTable()  48  49   def insert(self):  50      for who, uid in randName():  51          self.users(**dict(zip(FIELDS,  52               [who, uid, rrange(1,5)])))  53  54   def update(self):  55      fr = rrange(1,5)  56      to = rrange(1,5)  57      users = self.users.selectBy(prid=fr)  58      for i, user in enumerate(users):  59          user.prid = to  60      return fr, to, i+1  61  62   def delete(self):  63      rm = rrange(1,5)  64      users = self.users.selectBy(prid=rm)  65      for i, user in enumerate(users):  66          user.destroySelf()  67      return rm, i+1  68  69   def dbDump(self):  70      print '\n%s%s%s' % ('LOGIN'.ljust(COLSIZ),  71           'USERID'.ljust(COLSIZ), 'PROJ#'.ljust(COLSIZ))  72      for usr in self.users.select():  73          print '%s%s%s' % (tuple([str(getattr(usr,  74              field)).title().ljust(COLSIZ) \  75              for field in FIELDS]))  76  77   drop = lambda self: self.users.dropTable()  78   finish = lambda self: self.cxn.close()  79  80 def main():  81    print '*** Connecting to %r database' % DBNAME  82    orm = MySQLObject('mysql', DBNAME)  83  84    print '\n*** Creating users table'  85    orm.create()  86  87    print '\n*** Inserting names into table'  88    orm.insert()  89    orm.dbDump()  90  91    print '\n*** Randomly moving folks',  92    fr, to, num = orm.update()  93    print 'from one group (%d) to another (%d)' % (fr, to)  94    print '\t(%d users moved)' % num  95    orm.dbDump()  96  97    print '\n*** Randomly choosing group',  98    rm, num = orm.delete()  99    print '(%d) to delete' % rm  100   print '\t(%d users removed)' % num  101   orm.dbDump()  102  103   print '\n*** Dropping users table'  104   orm.drop()  105   orm.finish()  106  107 if__name__ == '__main__':  108   main()

Line-by-Line Explanation

Lines 110

As expected, we begin with module imports and constants. We follow the suggested style guideline of importing Python Standard Library modules first, followed by third-party or external modules, and finally, local modules to our application. The constants should be fairly self-explanatory.

Lines 1231

The constructor for our class, like ushuffle_db.connect(), does everything it can to make sure that there is a database available and returns a connection to it (lines 18-31). This is the only place you will see real SQL, as such activity is typically an operational task, not application-oriented.

Lines 3344

The TRy-except clause (lines 33-40) is used to reload an existing table or make a new one if it does not exist yet. Finally, we attach the relevant objects to our instance.

Lines 4670

These next four methods represent the core database functionality of table creation (lines 46-52), insertion (lines 54-57), update (lines 59-64), and deletion (lines 66-70). We should also have a method for dropping the table:

  def drop(self):       self.users.drop()

or

   drop = lambda self: self.users.drop()

However, we made a decision to give another demonstration of delegation (as introduced in Chapter 13, Object-Oriented Programming). Delegation is where missing functionality (method call) is passed to another object in our instance which has it. See the explanation of lines 79-80.

Lines 7277

The responsibility of displaying proper output to the screen belongs to the dbDump() method. It extracts the rows from the database and pretty-prints the data just like its equivalent in ushuffle_db.py. In fact, they are nearly identical.

Lines 7980

We deliberately avoided creating a drop() method for the table since it would just call the table's drop method anyway. Also, there is no added functionality, so why create yet another function to have to maintain? The __getattr__() special method is called whenever an attribute lookup fails.

If our object calls orm.drop() and finds no such method, getattr (orm, 'drop') is invoked. When that happens, __getattr__() is called and delegates the attribute name to self.users. The interpreter will find that self.users has a drop attribute and pass that method call to it: self. users.drop()!

Lines 8284

The last method is finish(), which commits the transaction.

Lines 86114

The main() function drives our application. It creates a MySQLAlchemy object and uses that for all database operations. The script is the same as for our original application, ushuffle_db.py. You will notice that the database parameter db is optional and does not serve any purpose here in ushuffle_sa.py or the upcoming SQLobject version ushuffle_so.py. This is a placeholder for you to add support for other RDBMSs in these applications (see Exercises at the end of the chapter).

Upon running this script, you may get output that looks like this:

$ ushuffle_sa.py *** Connecting to 'test' database *** Creating users table *** Inserting names into table LOGIN     USERID    PROJ# Serena    7003      4 Faye      6812      4 Leslie    7808      3 Ernie     7410      1 Dave      7306      2 Melissa   8602      1 Amy       7209      3 Angela    7603      4 Jess      7912      2 Larry     7311      1 Jim       7512      2 Davina    7902      3 Stan      7607      4 Pat       7711      2 Aaron     8312      2 Elliot    7911      3 *** Randomly moving folks from one group (1) to another (3)         (3 users moved) LOGIN     USERID    PROJ# Serena    7003      4 Faye      6812      4 Leslie    7808      3 Ernie     7410      3 Dave      7306      2 Melissa   8602      3 Amy       7209      3 Angela    7603      4 Jess      7912      2 Larry     7311      3 Jim       7512      2 Davina    7902      3 Stan      7607      4 Pat       7711      2 Aaron     8312      2 Elliot    7911      3 *** Randomly choosing group (2) to delete         (5 users removed) LOGIN     USERID    PROJ# Serena    7003      4 Faye      6812      4 Leslie    7808      3 Ernie     7410      3 Melissa   8602      3 Amy       7209      3 Angela    7603      4 Larry     7311      3 Davina    7902      3 Stan      7607      4 Elliot    7911      3 *** Dropping users table $

Line-by-Line Explanation

Lines 110

This modules imports and constant declarations are practically identical to those of ushuffle_sa.py except that we are using SQLObject instead of SQLAlchemy.

Lines 1242

The constructor for our class does everything it can to make sure that there is a database available and returns a connection to it, just like our SQLAlchemy example. Similarly, this is the only place you will see real SQL. Our application, as coded here, will result in an infinite loop if for some reason a Users table cannot be created in SQLObject.

We are trying to be clever in handling errors by fixing the problem and retrying the table (re)create. Since SQLobject uses metaclasses, we know that special magic is happening under the covers, so we have to define two different classesone for if the table already exists and another if it does not.

The code works something like this:

1. Try and establish a connection to an existing table; if it works, we are done (lines 23-29)

2. Otherwise, create the class from scratch for the table; if so, we are done (lines 31-36)

3. Otherwise, we have a database issue, so try and make a new database (lines 37-40)

4. Loop back up and try all this again

Hopefully it (eventually) succeeds in one of the first two places. When the loop is terminated, we attach the relevant objects to our instance as we did in ushuffle_sa.py.

Lines 4467, 7778

The database operations happen in these lines. We have table create (lines 44-47) and drop (line 77), insert (lines 49-52), update (lines 54-60), and delete (lines 62-67). The finish() method on line 78 is to close the connection. We could not use delegation for table drop like we did for the SQLAlchemy example because the would-be delegated method for it is called dropTable() not drop().

Lines 6975

This is the same and expected dbDump() method, which pulls the rows from the database and displays things nicely to the screen.

Lines 80108

This is the main() function again. It works just like the one in ushuffle_sa.py. Also, the db argument to the constructor is a placeholder for you to add support for other RDBMSs in these applications (see Exercises at the end of the chapter).

Here is what your output may look like if you run this script:

$ ushuffle_so.py *** Connecting to 'test' database *** Creating users table *** Inserting names into table LOGIN     USERID    PROJ# Jess      7912      1 Amy       7209      4 Melissa   8602      2 Dave      7306      4 Angela    7603      4 Serena    7003      2 Aaron     8312      1 Leslie    7808      1 Stan      7607      3 Pat       7711      3 Jim       7512      4 Larry     7311      3 Ernie     7410      2 Faye      6812      4 Davina    7902      1 Elliot    7911      4 *** Randomly moving folks from one group (2) to another (3)          (3 users moved) LOGIN     USERID    PROJ# Jess      7912      1 Amy       7209      4 Melissa   8602      3 Dave      7306      4 Angela    7603      4 Serena    7003      3 Aaron     8312      1 Leslie    7808      1 Stan      7607      3 Pat       7711      3 Jim       7512      4 Larry     7311      3 Ernie     7410      3 Faye      6812      4 Davina    7902      1 Elliot    7911      4 *** Randomly choosing group (3) to delete         (6 users removed) LOGIN     USERID    PROJ# Jess      7912      1 Amy       7209      4 Dave      7306      4 Angela    7603      4 Aaron     8312      1 Leslie    7808      1 Jim       7512      4 Faye      6812      4 Davina    7902      1 Elliot    7911      4 *** Dropping users table $

21.3.4. Summary

We hope that we have provided you with a good introduction to using relational databases with Python. When your application's needs go beyond those offered by plain files, or specialized files like DBM, pickled, etc., you have many options. There are a good number of RDBMSs out there, not to mention one completely implemented in Python, freeing one from having to install, maintain, or administer a real database system. Below, you will find information on many of the Python adapters plus database and ORM systems out there. We also suggest checking out the DB-SIG pages as well as the Web pages and mailing lists of all systems of interest. Like all other areas of software development, Python makes things easy to learn and simple to experiment with.