How to solve Incorrect string value MySQL error?

Question

I'm trying to use MySQL to store a series of matrices and dictionaries. I'm using python 3.

To do this, I'm serializing the matrices with .dumps() and placing my dictionaries into np.arrays and serializing them too.

This is what I'm trying:

matriz_entry = serie.pvalue_matrix.dumps()
sinais_entry = np.array([serie.sinais]).dumps()

c.execute('INSERT INTO test_table (time, sinais, matriz) VALUES (%s,%s,%s)',
            (time.time(), sinais_entry, matriz_entry))

This error is returned: `pymysql.err.InternalError: (1366, "Incorrect string value: '\x80\x02cnum...' for column 'sinais' at row 1")

This is my MySQL table:

mysql> DESC test_table;
+--------+----------------+------+-----+---------+-------+
| Field  | Type           | Null | Key | Default | Extra |
+--------+----------------+------+-----+---------+-------+
| time   | varchar(100)   | YES  |     | NULL    |       |
| sinais | varchar(13000) | YES  |     | NULL    |       |
| matriz | varchar(2000)  | YES  |     | NULL    |       |
+--------+----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I'm actually new to SQL and I don't know if serializing an object is good practice since I may be exceeding the maximum row length of a table (the dictionary serialized is over 12000 characters long).

I've also tried using the type TEXT instead of VARCHAR(), but that won't work either.

Please request any relevant additional information.

Abhishek Garg · Accepted Answer · 2019-07-20 17:36:21Z

1

The data generated from dumps() method is binary data and since you are trying to store binary data in text format it is giving you the error.

BLOB is used for storing binary data while Text is used to store a large string.

BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values.

TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.

You should also look at this answer on how to store a pickle in the database.

answered Jul 20, 2019 at 17:36

Abhishek Garg

2,2981 gold badge19 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

How to solve Incorrect string value MySQL error?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related