Поиск
  • Alexanyan Andron

Insert numpy array in MySQL database using Python

If you struggle to insert numpy array in MySQL database than this post is defenitely for you!


I chose pickle module to solve this problem. Using pickle you can insert any multidimensional numpy array in MySQL blob.


So, here is the code:

import mysql.connector
import pickle
import numpy as np

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

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

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

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

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

#check the result
res

##here is the result:[(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.'),)]

#using pickle.loads restore our array 
pickle.loads(res[0][0])

##here is the result:
array([[1, 2, 3],
       [4, 5, 6]])

#close cursor and db connection          
cursor.close()
connection.close()

Thus you can absolutely without let or hindrance exchange multidimensional numpy arrays between Python and MySQL. If you upload an image using cv2.imread from OpenCV library, you can do the same thing because is's just a multidimensional numpy array, in fact.


#python #mysql #databases #numpy