Saving Python Array in MySQL db

Question

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)

nbk · Accepted Answer · 2021-09-02 22:16:11Z

2

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

answered Sep 2, 2021 at 22:16

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Saving Python Array in MySQL db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related