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
