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


Create a Web-Based CRUD Automation Framework with Python

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.

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('')

#create a Genre record and set it to active
record = { 'name' : 'Comedy', 'active' : True}
s.create('genre', record)

#Fetch all the records from genre table that are in active status
conditions = {'active' : True}
for result in s.read('genre', conditions):
    print result

Author's Note: The framework was tested with the following technologies:
  • Python 2.5.x
  • SQLAlchemy 0.4.7
  • SQLite
  • pysqlite
  • Twisted 8.2.0 for python 2.5

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date