Get best of the week

Insert numpy array into MySQL database via Python

If you encounter a problem that you cannot normally save a numpy array into a MySQL database , then this post is for you! The original post was posted on my blog .

I chose a save method for myself through the pickle module . With it, you can safely save a numpy array of any dimension in the blob of the MySQL database.

So, an example code:

import mysql.connector
import pickle
import numpy as np

#    
connection = mysql.connector.connect(host='localhost',
                                         database='database',
                                         user='root',
                                         password='')
# 
cursor = connection.cursor()

#       blob
query = 'create table test(`column` blob);'
cursor.execute(query)

#  numpy 
array = np.array([[1,2,3],[4,5,6]])

#    ,   pickle.dumps
query = 'insert into test values(%s);'
db_array = pickle.dumps(array)
cursor.execute(query, [db_array ])

#     test
query = 'select * from test;'
cursor.execute(query)
res = cursor.fetchall()

#  
res

##   : [(bytearray(b'\x80\x03cnumpy.core.multiarray\n_reconstruct\nq\x00cnumpy\nndarray\nq\x01K\x00\x85q\x02C\x01bq\x03\x87q\x04Rq\x05(K\x01K\x02K\x03\x86q\x06cnumpy\ndtype\nq\x07X\x02\x00\x00\x00i8q\x08K\x00K\x01\x87q\tRq\n(K\x03X\x01\x00\x00\x00<q\x0bNNNJ\xff\xff\xff\xffJ\xff\xff\xff\xffK\x00tq\x0cb\x89C0\x01\x00\x00\x00\x00\x00\x00\x00\x02\x00\x00\x00\x00\x00\x00\x00\x03\x00\x00\x00\x00\x00\x00\x00\x04\x00\x00\x00\x00\x00\x00\x00\x05\x00\x00\x00\x00\x00\x00\x00\x06\x00\x00\x00\x00\x00\x00\x00q\rtq\x0eb.'),)]

#  pickle.load      numpy
pickle.loads(res[0][0])

##  
array([[1, 2, 3],
       [4, 5, 6]])

#                   
cursor.close()
connection.close()

In this way, you can exchange numpy multidimensional (and regular) numpy arrays between Python and MySQL in an absolutely unhindered fashion. If you read the image using cv2.imread from the OpenCV library, for example, then the principle remains the same - after all, it is, in fact, a multidimensional array.

I hope this helps someone to solve a similar problem!

More articles:


All Articles