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)
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 ,)