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