Object Relational Mapping with SQLObject

Most projects today need to connect to one database or another. Many applications depend on the database and the quality of the database product. However, when you start building an application, you may feel that you do not require a highly scaleable solution and may opt for something that is simpler and smaller.

Often, companies that are growing have to switch databases for need of power, scalability or other features. In such cases, migrating databases even involves making modifications in the application code. To solve this problem, object oriented programming gurus have developed ORM techniques and tools.

Object-Relational mapping (O/RM) is a programming technique that links databases to object-oriented language concepts, creating (in effect) a "virtual object database." There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to code their own object-relational mapping for their systems.

The idea is very simple. You create objects that can be mapped into RDBMS structures. For example, a simple address book, can be designed, created and populated using ORM tools.

There are several ORM tools in available in the Open Source world. The most popular is the Java-based Hibernate promoted by Apache foundation. RS Ramaswamy had run a tutorial on Hibernate many months back.

In this article, we will take a look at SQLObject, an ORM tool written and developed by Ian Bickling a leading Open Source developer.

It is recommended that you have the latest Python interpreter, a copy of sqlite, pysqlite (pysqlite.org) and SQLObject (www.SQLObject.org).

I am not getting into the installation details. These are elementary.

URI

A usual hiccup that occurs when you start off with SQLObject is to get the current scheme for declaring the URI of the database.

The connection URI must follow the standard URI syntax:

scheme://[user[:password]@]host[:port]/database[?parameters]

Scheme is one of sqlite, mysql, postgres, firebird, interbase, maxdb, sapdb, sybase, mssql.

Examples:

mysql://user:password@host/database
mysql://host/database?debug=1
postgres://user@host/database?debug=&cache=
postgres://host:5432/database
sqlite:///full/path/to/database
sqlite:/C|/full/path/to/database
sqlite:/:memory:

In case of Windows, you need to be very careful in getting the correct syntax. 

First, figure out your present working directory, or change your working directory to a directory of your choice.

>>> os.getcwd()
'C:\\Documents and Settings\\user'
>>> os.chdir('C:\\tg')
>>> os.getcwd()
'C:\\tg'

Import sqlobject module into your interpreter.

>>> from sqlobject import *

Now you need to be careful. SQLObject demands that the path must use the UNIX style forward slashes, instead of Windows style slashes, when declaring the URI path. Hence, the database you hope to create will be created as follows:

>>> URIstring = 'sqlite:/C|/tg/data.db'
>>> connection = connectionForURI(URIstring)
>>> sqlhub.processConnection = connection

What you have done is to establish an SQL connection to the database. The sqlhub.processConnection is the API to connect a database to the Python interpreter. 

Now you can browse the directory TG or the directory you have chosen on your PC, and you will find that a database file data.db has been created. You have neither invoked sqlite or any other databases so far. Without leaving the python interpreter, you can create databases very easily and rapidly.

Create a table

You can now create a table very easily using simple OO concepts in Python.

Let us create an employee table by first creating a class called employee.

>>> class employee(SQLObject):
... name = StringCol(length=22)
... age = StringCol(length=2)
... salary = StringCol(length=7)

The class employee is created through inheritance from the SQLObject class in the module sqlobject.

You can create a table with a simple command:

Employee.createTable()

Now you can create a couple of table rows very easily:

>>> dev = employee( name = 'Dev',age = 43, salary = 67233)
>>> raj = employee (name = 'Raj', age = 49, salary =78000)

Check the data.db using sqlite:
sqlite> select * from employee;
1|Dev|43|67233
2|Raj|49|78000
sqlite>

It is that simple, you have already populated your database table with a couple of entries, without writing a single line of SQL queries or statements.

Now you can add, delete, modify rows and columns of the table.

We will add more rows to the table:

>>> employee(name = 'Peter', age =34, salary ='40000')
<employee 3 name='Peter' age='34' salary='40000'>
>>> employee(name = 'Sid', age =35, salary ='30000')
<employee 4 name='Sid' age='35' salary='30000'>
>>>

You can get specific information.

employee.get(1)
<employee 1 name='Dev' age='43' salary='67233'>
>>>

You can even go very specific and pick up a column by checking out the object attribute.
employee.get(1).name

‘Dev’

You can even make a few changes to the properties. For example:

>>> dev.set(salary = 100000)
>>> dev.salary
100000
>>>

You can even delete a few rows or a column:

>>> employee.delete(4)
>>> employee.delColumn("age")
>>> employee.age

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
AttributeError: type object 'employee' has no attribute 'age'


You can use the select and selectby clauses to print SQL statements. Classes have an attribute q, which gives access to special objects for constructing query clauses. All attributes under q refer to column names and if you construct logical statements with these, it'll give you the SQL for that statement. You can also create your SQL more manually.

>>> Sal_statement = employee.select(employee.q.salary > 40000)
>>> Sal_statement = employee.select(employee.q.salary > 40000)
>>> print Sal_statement
SELECT employee.id, employee.name, employee.salary FROM employee WHERE (employee.salary > 40000)

Understanding joins and keys

To be really productive with SQLObjects, you need to understand the concept of joins and keys. In fact, the entire SQL world is enhanced by the concepts of keys and joins.

Foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables. We will create a couple of simple tables:

>>> class Artist (SQLObject):
... name = StringCol()
... music = StringCol()
... 
>>> class Album (SQLObject):
... name = StringCol()
... artist = ForeignKey ('Artist')
... 
>>> Artist.createTable()
>>> Album.createTable()

Note the column artist = ForeignKey("Artist"). This is a reference to an Artist object. We refer to other classes by name (with a string), because sometimes you create classes without creating classes, which are referred inside them. In the database, there will be a person_id column, type INT, which points to the artist column. Check the listing below.

Now we will populate the database with a few randomly chosen data:

>>> Artist (name =" Kishore K", music = " Hindi Vocals")
<Artist 1 name=' Kishore K' music=' Hindi Vocals'>
>>> Artist (name = " Zakir H", music = " Tabla")
<Artist 2 name=' Zakir H' music=' Tabla'>
>>> rafi = Artist ( name = " Rafi", music = " Hindi Vocals")
>>> Album (name = "Collections", artist = " Kishore K")
<Album 1 name='Collections' artistID=0>

Joins

Sometimes we have to select data from two or more tables to make our results complete. We have to perform a join.
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.
You can add columns, joins and just about anything to a class. For example, you can add a Multiple Join with the following command:
Artist.sqlmeta.addJoin(MultipleJoin('Album', joinMethodName='albums'))

Populating Album table…
>>> Album (name = " Collections 70", artist = rafi)
<Album 2 name=' Collections 70' artistID=5>

Checking for the details we get
>>> rafi.albums
[<Album 2 name=' Collections 70' artistID=5>]

Several key word arguments are allowed to the MultipleJoin constructor:

joinColumn: 

The column name of the key that points to this table. So, if you have a table Product and another table has a column ProductNo that points to this table, then you would use joinColumn="ProductNo".

orderBy: 

Like the orderBy argument to select(), you can specify the order that the joined objects should be returned in. _defaultOrder will be used if not specified; None forces unordered results.

joinMethodName: 

When adding joins dynamically (using the class method addJoin), you can give the name of the accessor for the join. It can also be created automatically and is normally implied (i.e., addresses = MultipleJoin(...) implies joinMethodName="addresses").
RelatedJoin has all the key word arguments of MultipleJoin, plus:

otherColumn: 

Similar to joinColumn, but referring to the joined class. 

intermediateTable: 

The name of the intermediate table that references both classes. 

addRemoveName: 

You can use this function to change the attributes.

Conclusion
ORM tools are fairly useful in creating and manipulating databases. SQLObject is a very interesting tool that helps you in creating tables, and building relationships between tables. It is highly recommended that you use these tools for development work.