How to Pickle a python dictionary into MySQL?


Problem :

I looked through several SO-Questions for how to pickle a python object and store it into a database. The information I collected is:

  • import pickle or import cpickle. Import the latter, if performance is an issue.
  • Assume dict is a python dictionary (or what so ever python object): pickled = pickle.dumps(dict).
  • store pickled into a MySQL BLOB Column using what so ever module to communicate with Database.
  • Get it out again. And use pickle.loads(pickled) to restore the python dictionary.

I just want to make sure I understood this right. Did I miss something critical? Are there sideeffects? Is it really that easy?

Background-Info: The only thing I want to do, is store Googlegeocoder-Responses, which are nested python dictionarys in my case. I am only using a little part of the response object and I don't know if I will ever need more of it later on. That's why I thought of storing the response to save me repetition of some million querys.



Solution :

It's really that easy... so long as you don't need your DB to know anything about the dictionary. If you need any sort of structured data access to the contents of the dictionary, then you're going to have to get more involved.

Another gotcha might be what you intend to put in the dict. Python's pickle serialization is quite intelligent and can handle most cases without any need for adding custom support. However, when it doesn't work, it can be very difficult to understand what's gone wrong. So if you can, restrict the contents of the dict to Python's built-in types. If you start adding instances of custom classes, keep them to simple custom classes that don't do any funny stuff with attribute storage or access. And beware of adding instances of classes or types from add-ons. In general, if you start running into hard-to-understand problems with the pickling or unpickling, look at the non-built-in types in the dict.


Mysql Tutorials

Mysql Howto..

How to limit the increment of variable in mysql

MySQL: How to fetch data from two table and store into one table

How to display field name from MySQL with PHP

how to return mysql data from a php function

How to calculate percentage of “equal or better” in mysql DB?

how to add condition in Nth row of the MySQL database fetching in codeigniter

PHP -MySQL insert and select statements - how to make them atomic?

How to increase performance for heavy inserts in mysql?

how to update with select in MySQL

How to get records that exist in one MySql table and not another [duplicate]

how to store this date string in mysql db to allow sorting by date?

MySQL : How to get the sum columns by name of alias

How to abbreviate MySQL queries in PHP?

How to pass DataTable from one class to the other class

How to create default relationship for 2 MySQL tables with many-to-many relationships?

How to write MySQL full-text query with MATCH(), ROUND() & Subquery?

mysql query errors out with no-duplicate - but how to make it skip over the error and continue with execution of other?

How to design DB for question-answer (MySql)

How to Insert form values into MySQL database without refreshing the form and losing the field values of the form?

how to import a massive JSON object into MySQL

how to convert mysql query into mongodb

How can I connect to MySQL on a WAMP server?

How to insert current date in database mysql

How to insert sub string of a mysql column into empty columns within loop using php?

How to force Mysql to use primary Index in IN clause…?

How do I simply create a connection to a remote mySQL database with ASP.NET VB

How to optimize MySQL query which contains table and left join of sql view

How can I work around no “LOAD DATA” support in stored procedures, and/or lack of EXIT HANDLER in TRANSACTION in MySQL?

How to find the smallest difference between two columns in Mysql?

how to determine space in a post method while form processing?