Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

Implement a CRUD automation framework that handles insert and simple queries, an easy way to talk to a database without needing to write any database-access code.




Application Security Testing: An Integral Part of DevOps

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 = '' #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.

Narendra Venkataraman is a software architect at Tellytopia Inc. In his spare time, he blogs at Naren's Developer Diary.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date