CODEWITHSUNDEEP
pythonmysqlstringlistblob
user3369214
user3369214

Reputation: 431

Python MySQL insert and retrieve a list in Blob

I'm trying to insert a list of element into a MySQL database (into a Blob column). This is an example of my code is:

myList = [1345,22,3,4,5]
myListString = str(myList)
myQuery = 'INSERT INTO table (blobData) VALUES (%s)'
cursor.execute(query, myListString)

Everything works fine and I have my list stored in my database. But, when I want to retrieve my list, because it's now a string I have no idea how to get a real integer list instead of a string.

For example, if now i do :

myQuery = 'SELECT blobData FROM db.table'
cursor.execute(myQuery)
myRetrievedList = cursor.fetch_all()
print myRetrievedList[0]

I ll get :

[

instead of :

1345

Is there any way to transform my string [1345,22,3,4,5] into a list ?

Upvotes: 0

Views: 2124

Answers (2)

Sudipta Chatterjee
Sudipta Chatterjee

Reputation: 4670

As per the comments in this answer, the OP has a list of lists being entered as the blob field. In that case, the JSON seems a better way to go.

import json
...
...
myRetrievedList = cursor.fetch_all()
jsonOfBlob = json.loads(myRetrievedList)
integerListOfLists = []
for oneList in jsonOfBlob:
  listOfInts = [int(x) for x in oneList]
  integerListOfLists.append(listOfInts)

return integerListOfLists #or print, or whatever

Upvotes: 1

Dima Tisnek
Dima Tisnek

Reputation: 11781

You have to pick a data format for your list, common solutions in order of my preference are:

  • json -- fast, readable, allows nested data, very useful if your table is ever used by any other system. checks if blob is valid format. use json.dumps() and json.loads() to convert to and from string/blob representation
  • repr() -- fast, readable, works across Python versions. unsafe if someone gets into your db. user repr() and eval() to get data to and from string/blob format
  • pickle -- fast, unreadable, does not work across multiple architectures (afaik). does not check if blob is truncated. use cPickle.dumps(..., protocol=(cPickle.HIGHEST_PROTOCOL)) and cPickle.loads(...) to convert your data.

Upvotes: 1

Related Questions