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.