CODEWITHSUNDEEP
pythonmysqlserializationpicklepymysql
Lexicon
Lexicon

Reputation: 2627

Saving Python Array in MySQL db

I have an array fn_arrand I'd like to save it into a field in my database. The field Datatype is TEXT but I can change it if necessary. Some online reading seems to suggest that I should pickle the array then save it to the field, but the pickle dump converts it to a byte string. I think this is why I'm getting the error:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b'\\x80\\x03]q\\x00(K\\x02K\\x03K\\x04K\\x05K\\x06K\\x07K\\x08K\\x0ce.'

What is the best way for me to save my array in a MySQL database? What Datatype should my field be and should I pick a specific collation (currently latin1_swedish_ci)?

import pickle 

fn_arr = [2,3,4,5,6,7,8,12]
fn_pickle = pickle.dumps(fn_arr)
insert = "insert into signature (signature) values (%s)"%(fn_pickle)
cur.execute(insert)

Upvotes: 0

Views: 643

Answers (1)

nbk
nbk

Reputation: 49375

You could use json to store the data in a TEXT column, blob are better suited for bytes

import json
fn_arr = [2,3,4,5,6,7,8,12]
testjson =  json.dumps(fn_arr)
insert = "insert into signature (signature) values (%s)"
cur.execute(insert,(testjson ,)

Upvotes: 2

Related Questions