Python Data Persistence using MySQL Part III: Building Python Data Structures Upon the Underlying Database Data

Exclusive offer: get 50% off this eBook here
Expert Python Programming

Expert Python Programming — Save 50%

Best practices for designing, coding, and distributing your Python software

$26.99    $13.50
by Yuli Vasiliev | December 2008 | Open Source

This article, the third one in the Python Data Persistence using MySQL series by Yuli Vasiliev, discusses how you can implement Python data structures representing data structures stored in the underlying database and then manipulate those data structures on the Python side.

Using Python Built-in Object Types to Hold Structured Data

The most common way to hold structured data in Python is to use built-in object types such as lists, list comprehensions, tuples, and dictionaries. In particular, you may find the above Python types useful when dealing with database data.

Dictionaries can be of particular interest to you when you need to represent data stored in a database table that has a primary key column. Turning back to the posts table created and populated with data as discussed in the first article of this series, you might create the following dictionary to represent the records stored in this table:

posts = {}
posts["Layouts in Ext JS"] = {"guid":"http://www.packtpub.com/article/layouts-in-ext-
js","pubDate":"Fri, 28 Nov 2008 10:31:03 +0000"}

posts["WordPress Plug-in Development (Beginner's Guide)"] = {"guid":"http://www.packtpub.com/
wordpress-plug-in-development","pubDate":"Fri, 28 Nov 2008 00:00:00 +0000"}

For clarity, you manually set up the dictionary here. In reality, though, you would most likely populate it with the data obtained from the database or the Web.

In the above example, the posts dictionary uses the values of the title column in the posts table as the dictionary’s keys. The dictionary’s values are also dictionaries each of which represents the rest of a record, containing the guid and pubDate fields.

Since the keys within a dictionary cannot be repeated, the above approach guarantees uniqueness of the title field in the posts’ records represented in the dictionary. Now to obtain a certain record, you can use its key like this:

rec = posts["Layouts in Ext JS"]
print rec

This should produce the following output:

{'guid':'http://www.packtpub.com/article/layouts-in-
ext-js','pubDate':'Fri, 28 Nov 2008 10:31:03 +0000'}

If you need to get to a certain field in the obtained record, you could use the following syntax:

guid = posts["Layouts in Ext JS"]["guid"]
print guid

The above should give you the following:

http://www.packtpub.com/article/layouts-in-ext-js

To iterate over all of the records in the posts dictionary, you could use a for loop. Here is how you could iterate over the guid field, for example:

for post in posts.items():
print post[1]['guid']

Note that the value of the first index of the post variable representing a key/value pair of the dictionary is set to 1, meaning you’re interested in the value part of the pair. The above should generate the following output:

http://www.packtpub.com/article/layouts-in-ext-js
http://www.packtpub.com/wordpress-plug-in-development

If you want to iterate over the dictionary keys, you could use the following code:

for post in posts.items():
print post[0]

This should give you the following lines:

Layouts in Ext JS
WordPress Plug-in Development (Beginner's Guide)

Now that you have an idea of how database data can be represented in Python, let’s look at an example of how you might persist it to a database. Here is a quick example that illustrates how you might persist the posts dictionary to the posts database table.

import MySQLdb
db=MySQLdb.connect(host="localhost",user="usrsample",passwd="pswd",db="dbsample")
c=db.cursor()
for post in posts.items():
c.execute("""INSERT INTO posts (title, guid, pubDate) VALUES
(%s,%s,%s)""", (post[0], post[1]['guid'], post[1]['pubDate']))
db.commit()
db.close()

Assuming you have populated the posts dictionary with data as discussed at the beginning of the article, the above code should insert two records into the posts table.

Fetching Database Records

You typically persist data to the database in order to retrieve it from there later. How can you retrieve data from the posts table? The following script answers this question:

import MySQLdb
db=MySQLdb.connect(host="localhost",user="usrsample",passwd="pswd",db="dbsample")
c=db.cursor()
c.execute("SELECT * FROM posts")
c.fetchall()

The cursor’s fetchall method in the above code fetches all the rows retrieved by the query, making them available as a list of tuples. To iterate over this list, you could use the following loop:

for row in c:
print row
db.close()

This should produce the following output:

('Layouts in Ext JS','http://www.packtpub.com/article/layouts-in-
ext-js','Fri, 28 Nov 2008 10:31:03 +0000')
("WordPress Plug-in Development (Beginner's Guide)",'http://www.packtpub.com/wordpress-plug-
in-development','Fri, 28 Nov 2008 00:00:00 +0000')

As you can see, each line in the above output represents a tuple rather than a dictionary. To have a dictionary instead, you will need to obtain the column names along with the rows being fetched. You can do this with the help of the cursor’s description read-only attribute, as illustrated in the updated script below:

import MySQLdb
db=MySQLdb.connect(host="localhost",user="usrsample",passwd="pswd",db="dbsample")
c=db.cursor()
c.execute("SELECT * FROM posts")
heads = [d[0] for d in c.description]
c.fetchall()
for row in c:
print dict(zip(heads,row))
db.close()

The output should give you a set of dictionaries each of which represents a record in the posts table. But how can you get the data so that it is structured as it were in the posts dictionary discussed at the beginning of the article? To do this, you could revise the above script as follows:

import MySQLdb
db=MySQLdb.connect(host="localhost",user="usrsample",passwd="pswd",db="dbsample")
c=db.cursor()
c.execute("SELECT * FROM posts")
heads = [d[0] for index, d in enumerate(c.description) if index > 0]
c.fetchall()
print heads
posts={}
for row in c:
posts[row[0]]= dict(zip(heads,[r for index, r in enumerate(row) if index > 0]))
print posts
db.close()

Notice the use of comprehension lists in the above code. First time, you use it to exclude the first column head from the heads list. Then, you use a similar technique to exclude the first field from each row when iterating fetched rows in the loop. As a result, you should have the same posts dictionary as you saw at the beginning of the article.

Customizing Built-in Types to Simulate Trigger Functionality

In the world of relational databases, triggers are programs stored inside the database, which run implicitly in response to a certain event. For example, you can define a BEFORE INSERT trigger on a certain table, so that it fires just before a new record is inserted into that table. It is interesting to note that triggers can be used in MySQL starting with version 5.0. If you have an older MySQL version, you won’t be able to take advantage of triggers. In that case, though, you still can simulate trigger functionality on the Python side of your application.

So, you want to define triggers on the data structures implemented in Python, much like you would do that in the underlying database. To achieve this, you could for example subclass the Python’s dict built-in type, overriding the __setitem__ method so that it takes the appropriate action implicitly whenever a new item is added. Next, you could use this customized dict’s subclass instead of dict.

Consider the following example. Suppose you want to implement the BEFORE INSERT trigger functionality on the posts dictionary, so that it restricts inserting new items to those that represent an article from the Packt Article Network. To achieve this, you will need to override the dict’s __setitem__ method so that it checks to see whether the value of the item’s guid includes the following fragment: http://www.packtpub.com/article/. Below, you create the dict’s subclass called artdict, and then use this subclass to create the posts dictionary, populating it with the same data you used at the beginning of the article:

class artdict(dict):
def __setitem__(self, key, value):
x = 'http://www.packtpub.com/article/'
if (x in value['guid']):
super(artdict, self).__setitem__(key, value)

posts=artdict()

posts["Layouts in Ext JS"] = {"guid":"http://www.packtpub.com/article/layouts-in-ext-
js","pubDate":"Fri, 28 Nov 2008 10:31:03 +0000"}

posts["WordPress Plug-in Development (Beginner's Guide)"] = {"guid":"http://www.packtpub.com/
wordpress-plug-in-development","pubDate":"Fri, 28 Nov 2008 00:00:00 +0000"}

print posts

Although you have tried to insert two records into the posts dictionary, only the first insertion should have succeeded. So the print should generate the following output:

{"Layouts in Ext JS":{"guid":"http://www.packtpub.com/article/layouts-in-
ext-js","pubDate":"Fri, 28 Nov 2008 10:31:03 +0000"}}

The second item, whose title is WordPress Plug-in Development (Beginner's Guide), was excluded because its guid does not include substring http://www.packtpub.com/article/. This is because this item is not associated with an article but the book.

Summary

As you learned in this article, Python language provides a wide variety of useful tools to deal with structured data. You can utilize lists, tuples, list comprehensions, and dictionaries when it comes accessing and manipulating data stored in the underlying database. You can even customize the above built-in types to meet the requirements of your application.

Expert Python Programming Best practices for designing, coding, and distributing your Python software
Published: September 2008
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

About the Author :


Yuli Vasiliev

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open-source development, Oracle technologies, and service-oriented architecture (SOA). He has over 10 years of software development experience as well as several years of technical writing experience. He wrote a series of technical articles for Oracle Technology Network (OTN) and Oracle Magazine.

Contact Yuli Vasiliev

Books From Packt

Expert Python Programming
Expert Python Programming

CherryPy Essentials: Rapid Python Web Application Development
CherryPy Essentials: Rapid Python Web Application Development

Professional Plone Development
Professional Plone Development

Learning Website Development with Django
Learning Website Development with Django

OpenCms 7 Development
OpenCms 7 Development

Zenoss Core Network and System Monitoring
Zenoss Core Network and System Monitoring

Building Websites with Joomla! 1.5
Building Websites with Joomla! 1.5

Building Powerful and Robust Websites with Drupal 6
Building Powerful and Robust Websites with Drupal 6

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
D
e
a
T
q
P
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software