Python Example – Pickling Things into MySQL Databases

Sometimes you need to store something for later while using Python. For large cases, you might even need a database. In this example, I’ll show you how to pickle a Python object and store it into a MySQL database.

Pickling in Python is a way of storing sometimes complex data structures into a binary representation that can be unpickled later to get back the same data structure. For instance, a list of nested lists will pickle and unpickle into the same list of nested lists.

In this example, I’m assuming there is a MySQL server at ‘localhost’, with the user ‘pickle’ / password ‘cucumber’ granted rights on the database ‘lists’. In that database is a table called ‘pickleTest’ with the following columns:

ID: a standard auto-incrementing ID int field
Name: a character field for a name
List: a binary BLOB field for storing pickled things

See the commented script which you can run yourself for an example of how to use the pickle module along with a MySQL database to store mass amounts of Python structures that you can retrieve later and unpickle back into the original structure.

It goes over connecting to the database, pickling a semi-complex list, storing the list, retrieving the results, and then unpickling back into the original list.

import cPickle
import MySQLdb

## Create a semi-complex list to pickle
listToPickle = [(10, 10), (20, 10.0), (1.0, 2.0)]

## Pickle the list into a string
pickledList = cPickle.dumps(listToPickle)

## Connect to the database as localhost, user pickle,
## password cucumber, database lists
connection = MySQLdb.connect('localhost','pickle','cucumber','lists')

## Create a cursor for interacting
cursor = connection.cursor()

## Add the information to the database table pickleTest
cursor.execute("""INSERT INTO pickleTest VALUES (NULL, 'testCard', %s)""", (pickledList, ))

## Select what we just added
cursor.execute("""SELECT features FROM pickleTest WHERE card = 'testCard'""")

## Dump the results to a string
rows = cursor.fetchall()

## Get the results
for each in rows:
	## The result is also in a tuple
	for pickledStoredList in each:
		## Unpickle the stored string
		unpickledList = cPickle.loads(pickledStoredList)
		print unpickledList