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


Fast, Easy Database Access with Python : Page 5

You don't have to keep writing the same old database access code over and over again—instead, it's simple to roll your own database wrapper and use Python syntax to retrieve, search, and update data. Implement the classes and methods shown here to speed up your Python database development process.

Inserting Rows
Reading data is useful, especially if you need to build reports or otherwise sort through data. But you probably will want to write to the database too. The simplest way to write is to insert new rows, which you can achieve with the following syntax:
contacts.insert('', "Stanley", "Spudowski", 
This is similar to a direct SQL query, but even simpler. And, because of the way the Python database spec works, you won't even have to worry about escaping data to make it safe. The spec requires automatic escaping for anything in the second parameter of the cursor's execute() function. If you ever plan to insert data supplied by a user, it is a good idea to use that protection, because it will prevent possible exploits in your program. Doing this requires a minor modification to the Table._query() method:
def _query(self, q, data=None):
	if self.debug: print "Query: %s (%s)" % (q, data)
	self.dbc.execute(q, data) 
With that modification in place, you can now write an insert function. All it needs to do is pass the data to your database in a format compatible with the DB API spec. However, because it needs to support tables of any size and shape, the format string should be created dynamically. The following creates a format string such as "%s,%s,%s,%s" according to the length of the row it is given, and then executes the query.
def insert(self, *row):
	fmt = ("%s," * len(row))[:-1]
	q = "insert into %s values (%s)" % (self.name, fmt)
	self._query(q, row) 
As an additional usability feature, it avoids the need to pass a tuple object, by collecting all the function's arguments into a tuple automatically. The *row syntax does this. Its effect, basically, is to remove one set of parenthesis, allowing you to run insert(a,b,c) instead of insert((a,b,c)).

Deleting Rows
Removing rows is also likely to be useful. And, in most SQL databases, it is not as simple as just telling the DB to remove row number 87. The difficulty is a matter of telling the database to identify which row to remove. One approach is to identify the row by specifying every value in its cells. For example, DELETE FROM movies WHERE title='Harvey' AND year='1950' AND genre=37;. The problem with this approach is that you might have two rows with identical data. In that case, you would accidentally delete both rows instead of just one.

A less error-prone approach is to use row IDs. Row IDs are supported by most databases, though the implementation differs. In MySQL, the query would look more like this: DELETE FROM movies WHERE _rowid=58;. That query will remove one and only one row, regardless of the content of the table. Even if every row is identical, each one will have a unique row ID. As an added bonus, the query is smaller, simpler, and quite similar to the array-like syntax Python uses to delete list items:
del movies[58] 
To make your Table class respond to the del command, add the following method to it:
def __delitem__(self, item):
   q = "select %s from %s %s %s limit %s, 1" % 
      ("_rowid", self.name, self._search, self._sort, 
   rid = self.dbc.fetchone()[0]
   q = "delete from %s where %s=%s" % (self.name, 
      "_rowid", rid)
This function performs two queries. First, it is necessary to obtain the row ID. Then it deletes the row identified by that ID. You can eliminate the need for the first query by always including the row ID in your query results, but for the sake of brevity, that is not demonstrated here. The provided example code includes row IDs, though, along with several other enhancements.

What's Next?
The data-access approach described above should help you use your time more efficiently and help you write data-processing scripts more quickly. You could extend and refine the approach into a much more sophisticated library, and make it more appropriate for larger applications. For example, you could modify the code to use dict-style cursors, instead of tuples. Or, you could add a Row class, with methods to modify individual cells. Another useful extension would be to formally support joined tables instead of using the somewhat tricky method described earlier.

This "bottom-up" approach should help you build up the Python language to better deal with the tasks you commonly perform. By adding a few special functions, you can turn Python into a richer, more appropriate language for whatever you need. And hopefully, by letting the interpreter do more of the work for you, you can build faster, cleaner solutions.

Scott Scriven is a freelance open-source hacker who enjoys dabbling in a wide variety of technologies, and creating beauty through simplicity. He strives, in all aspects of life, for three things: to learn, to love, and to play.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date