Storing a Pickle in MySql

Question

This is something that has been biting me for quite sometime now. I have the following pickle file (named rawFile.raw) that I generated by serializing some Python Dict objects :

content of rawFile.raw (truncated for legibility) :

(dp0
S'request_body # 1'
p1
S''
p2
sS'port # 1'
p3
I80
sS'query_params # 1'
p4
ccopy_reg
_reconstructor
p5
(cnetlib.odict
ODict
p6
c__builtin__
object
p7
Ntp8
Rp9
(dp10
S'lst'
p11
(lp12
(S'layoutId'
p13
S'-1123196643'
p14
tp15
asbsS'headers # 1'
p16
g5
(cnetlib.odict
ODictCaseless
p17
g7
Ntp18
Rp19
(dp20
g11
(lp21
(lp22
S'sn'
p23
aS'2.VI7D9DF640615B4948854C88C5E769B94C.SIE5FB3A28D0DA4F27A3D2C03B8FAAFFAE.VS144257070601359422212.1442570840'
p24
aa(lp25
S'Browser-Name'
p26
aS'Mobile Safari'
p27
aa(lp28
S'Accept-Encoding'
p29
aS'gzip'
p30
aa(lp31
S'secureToken'
p32
aS'5nANXZrwYBrl9sNykA+qlpLsjHXlnF97tQLHnPgcjwZm9u0t8XAHtO4XTjKODcIb0ee4LlFchmUiptWZEPDUng=='
p33
aa(lp34
S'User-Agent'
p35
aS'Mozilla/5.0 (Linux; U; Android 4.3; en-us; Google Galaxy Nexus - 4.3 - API 18 - 720x1280 Build/JLS36G) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30 FKUA/Retail/590206/Android/Mobile (Genymotion/Google Galaxy Nexus - 4.3 - API 18 - 720x1280/fd6babaa1ff9127ed7e9cc7a916639e5)'
p36
aa(lp37
S'Device-Id'
p38
aS'fd6babaa1ff9127ed7e9cc7a916639e5'
p39
aa(lp40
S'Host'
p41
aS'mobileapi.flipkart.net'
p42
aa(lp43
S'Connection'
p44
aS'Keep-Alive'
p45
aasbsS'method # 1'
p46
S'GET'
p47
sS'scheme # 1'
p48
S'http'
p49
sS'path # 1'
p50
(lp51
S'3'
p52
aS'layout'
p53
aS'widget'
p54
aS'productBundle'
p55
as.(dp0
S'request_body # 1'
p1
S''
p2
sS'method # 1'
p3
S'GET'
p4
sS'query_params # 2'
p5
ccopy_reg
_reconstructor
p6
(cnetlib.odict
ODict
p7
c__builtin__
object
p8
Ntp9
Rp10
(dp11
S'lst'
p12
(lp13
sbsS'query_params # 1'
p14

... more similar content truncated.

Now I need to store the contents of this file in a column in a MySql table. Through some Googling I learned that these should be stored in a BLOB column. So I did. Also if you observe, the above content has a lot of single and (may have) double quotes as well. I am using MySqlDB python connector. So when I try inserting the above data in the respective column as :

INSERT INTO session_logs(request_as_on, session_pickle) VALUES (%s, %s)

and then

self.cur.execute(insertValueQry,(parser.parse(reqTimeStamp).strftime('%Y-%m-%d %H:%M:%S'), putThisPickleInDb))

I get the following error :

mysqldb insertion error while inserting Error 1241: Operand should contain 1 column(s)

So to solve the above I replace the above execute statement with :

self.cur.execute(insertValueQry,(parser.parse(reqTimeStamp).strftime('%Y-%m-%d %H:%M:%S'), self.con.escape_string(str(putThisPickleInDb))))

and it solves the problem by escaping all single and double quotes from the pickle data. But it does not store the data in the table in the same format as it was in the raw file.

Apparently hence, now when I need to read back this pickle content from the db,

1. it is still escaped - So I do the following to un-escape it and get the original content:

   aalu = "" <br>
   aalu = aalu + str(userSelectedSessionPickle[0]).decode('string_escape').decode('string_escape')
   

where userSelectedSessionPickle is a tuple returned from the db.

2. After un-escaping it I try making a pickle out of it again with:

   pickle.dump (aalu, currentPickleRawFile)
   

so that I can load the pickle file later and use it as needed. But when doing a pickle.load on the file currentPickleRawFile, it throws the following error :

ValueError: dictionary update sequence element #0 has length 1; 2 is required

Just in case if it helps : When I open the file currentPickleRawFile in a text editor, the format of the contents in it is different than in rawFile.raw (content shown above).

All I need is store the above pickle file in a db and then be able to load it back. How can I get this done?

Answer 1

Saying that I figured out the solution would be unjust. I have to, HAVE TO make a mention of this angel blog that finally came to my rescue and help me get my job done that had almost drove me nuts by now.

http://blog.cameronleger.com/2011/05/31/python-example-pickling-things-into-mysql-databases/

Looks like the way he is inserting the pickle to the db, is what's doing the trick.

As someone suggested in the comments below, if the blog goes dead, we would loose the solution to the problem again. So here the code snippet, copy pasted from the the blog post:

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