Create a Web-Based CRUD Automation Framework with Python : Page 2

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 used
db_url = 'sqlite:///test.db'
#log directory is created in the same directory program is running
log_dir = 'log'
#log file name
log_file = 'crud.log'
#the ip address of the server
host = '127.0.0.1'
#listening port
port = 9080

The DB URL to access is sqlite:///test.db. If you have a PostgreSQL DB, the URL would look like postgres://user:password@host: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.

Previous Page