uppose you have a constantly growing database that currently contains 75 tables. Like any other database, a significant percentage of the tables carry metadata (i.e., the kind of data that appears as checkboxes or dropdown lists in your web form). Whenever you add a new table, you have to implement database access layer classes that look roughly like this:

class genre:
create(params)read(params)update(params)delete(params)

You then expose this class as an XML-RPC service so that client code written in any language can invoke it.

Now imagine the data access layer getting created auto-magically whenever a new table is added, without you needing to do a thing. This article explains how to implement just such a function using a CRUD (Create, Read, Update, Delete) automation framework that handles insert and simple queries.

Inside the Framework

The sample framework uses two open source, Python-based technologies: Twisted (asynchronous networking framework with built-in protocol implementations) and SQLAlchemy (SQL toolkit and object relational mapper). Twisted allows you to focus on application logic and not worry about protocol implementation intricacies. SQLAlchemy allows you to map a table as a Python class and an instance of class maps to a record. That way you can write maintainable code easily without worrying about the underlying RDBMS. The CRUD automation framework leverages Twisted’s own database API as well as SQLAlchemy’s database reflection API to iterate through all the tables in a database.

Click here to download the source code for the framework, which consists of the following:

• Two core modules, dbinterface and crud-service
• A config.py file, which consists of configuration parameters that you can modify to talk to your database
• A test database (test.db), which has one table, Genre, that consists of a list of genres that classify a video (e.g., Comedy, Sports, News, etc.).
• A dbcreate.py file, which you can use to create test databases of your own
• A crud-client.py file, a sample client that demonstrates how easy it is to create a record and perform queries (see below)

The following listing is the code for crud-client.py:

s = xmlrpclib.Server('http://127.0.0.1:9080')#create a Genre record and set it to activerecord = { 'name' : 'Comedy', 'active' : True}s.create('genre', record)#Fetch all the records from genre table that are in active statusconditions = {'active' : True}for result in s.read('genre', conditions):    print result

How the Framework Works

This section breaks down the components of the CRUD automation framework.

The dbinterface module has two classes, basetable and dbinterface. The basetable class has two key methods, pack and unpack. The pack method populates the table class instance with an input dictionary consisting of column names and values. The unpack method converts the table class instance back to a dictionary with column names as keys and column values as values.

Here is the code for dbinterface.py:

class basetable(object):    #map the record dictionary to table instance variables    def pack(self, record):        for column in record:            self.__dict__[column] = record[column]        #return the dictionary representation of the table instance    def unpack(self):        record = {}        for name in self.__dict__:            if name[0] == "_": continue #exclude non column keys            value = self.__dict__[name]            try:                record[name] = str(value)            except:                record[name] = repr(value)        return record

The dbinterface class maps all the tables of the database to table classes that inherit this base table. Here is the dbinterface init method for this mapping:

#Initialize DB and create a hashmap of table name and associated ORM mapper class        metadata = MetaData(db)        #retrieve database table information dynamically        metadata.reflect()        for table_name in metadata.tables:            #create a class that inherits basetable class and maps the class to table            table_class = type(str(table_name),(basetable,),{})            mapper(table_class, Table(table_name, metadata, autoload=True))            self.table_map[table_name] = table_class

The dbinterface also provides two methods, read and create, and you can easily implement update and delete methods as well. The read method shown below maps the resultset into an array of dictionaries:

#fetch all the records from tables that have conditions specified    def read(self, table_name, **keywords):        session = None        records = []        try:            table_class = self.table_map[table_name]            session = self.Session()            query = session.query(table_class)            resultset = query.filter_by(**keywords).all()            for record in resultset:                records.append(record.unpack())        except:            print traceback.format_exc()        finally:            if session:                session.close()        return records

The create method shown below creates a new record. The input is the dictionary with column name and column value as a key-value pair:

#create a record    def create(self, table_name, record):        session = None        status = False        try:            #lookup the corresponding table class and create an instance            table_instance = self.table_map[table_name]()            table_instance.pack(record)            session = self.Session()            session.save(table_instance)            session.commit()            status = True        except:            print traceback.format_exc()        finally:            if session:                session.close()        return status

The dbinterface class is wrapped around an XML-RPC class. (XML-RPC is simpler to use than SOAP.)

The crud-service class is very straightforward. It initializes db interfaces and sets up loggers. The configuration for the service is stored in config.py:

#Configuration Parameters#db url of your database. for simplicity, sqlite is useddb_url = 'sqlite:///test.db'#log directory is created in the same directory program is runninglog_dir = 'log'#log file namelog_file = 'crud.log'#the ip address of the serverhost = '127.0.0.1'#listening portport = 9080

The DB URL to access is sqlite:///test.db. If you have a PostgreSQL DB, the URL would look like postgres://user:[email protected]:port/dbname.

The crudservice proxies the requests to the dbinterface class. Here is the code for crudservice.py:

class crudservice(xmlrpc.XMLRPC):    def __init__(self, dbi):        self.dbi = dbi         xmlrpc.XMLRPC.__init__(self)    def xmlrpc_create(self, table_name, record):        return self.dbi.create(table_name, record)        def xmlrpc_read(self, table_name, conditions):        return self.dbi.read(table_name, **conditions)

To start the framework, run the command “python crud-service.py.” Whenever a new table is added, all you have to do is restart the service.

You can easily extend the framework to handle complex queries involving joins across tables. You also could easily replace XML-RPC with REST or SOAP.