An amazingly simple way to store and retrieve Numpy arrays in MySQL database via PNG conversion in Python
Sometime ago I was facing a challenge which involved processing of huge quantities of Numpy arrays and matrices. The data sets were grouped in clusters and nested sub categories. And because the data sets were required to be accessible via Internet I was looking for an easiest way to store them in MySQL database.
Let’s start with the road map
Import libraries for data handling
import mysql.connector
import os
import numpy as np
from imgarray import save_array_img, load_array_img
from os import fsyncWrite your first function, which makes sure that data is written to disk, so that buffering doesn’t influence the timings.
def sync(fh):
fh.flush()
fsync(fh.fileno())
return TrueWrite the next function which converts any given Numpy data set into PNG image. As arguments we will be using numpy array name and save path where the new image will be stored
def save_array_to_PNG(numpy_array, save_path):
with open(save_path, 'wb+') as fh:
save_array_img(numpy_array, save_path, img_format='png')
sync(fh)
return save_pathThe next function is pretty short and it converts the image file into binary format, which we will need for future processing in SQL
def convertToBinaryData(filename):
with open(filename, 'rb') as file:
binaryData = file.read()
return binaryDataThe following function will handle the image upload to your MySQL database. And I assume that you already have set up the database table with a column for image storage (column type = Blob).
def insertBLOB(png_image):
try:
connection = mysql.connector.connect(
user='Your_user_name',
password='Your_MySQL_password',
host='host_address',
database='database_name')
sql_table = 'your_table_name'
image_column_name = 'your_blob_column_name'
file = convertToBinaryData(png_image)
cursor = connection.cursor()
sql_insert_blob_query = "INSERT INTO " + sql_table + " (" + image_column_name + ") VALUES (" + file + ")"
result = cursor.execute(sql_insert_blob_query)
connection.commit()
except mysql.connector.Error as error:
print("Failed inserting BLOB data into MySQL table {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed")That was easy! We just have set up the functions which will handle the conversion and upload to MySQL database.
In the following steps, I will show you how to retrieve this data from SQL and convert it back to Numpy array format. But let’s start with process road map first.
The next function will enable the conversion of binary object to proper file format on your hard drive.
def write_file(data, filename):
# Convert binary data to proper format and write it on Hard Disk
with open(filename, 'wb') as file:
file.write(data)And the very last function will retrieve the PNG image from MySQL database and convert it back to Numpy array
def readBLOB(save_file_as, ID):
try:
connection = mysql.connector.connect(
user='Your_user_name',
password='Your_MySQL_password',
host='host_address',
database='database_name')
sql_table = 'your_table_name'
image_column_name = 'your_blob_column_name'
cursor = connection.cursor()
sql_fetch_blob_query = "SELECT " + image_column_name + " from " + sql_table + " where ID = '"+ str(ID)
cursor.execute(sql_fetch_blob_query)
record = cursor.fetchone()
file = record[0]
write_file(file, save_file_as)
numpy_arr_sql = load_array_img(save_file_as)
return numpy_arr_sqlDone! We have managed the whole process of conversion from Numoy array to SQL and back to Numpy array.
And here is an example how the whole code could look like.
import mysql.connector
import os
import numpy as np
from imgarray import save_array_img, load_array_img
from os import fsync
def sync(fh):
fh.flush()
fsync(fh.fileno())
return True
def save_array_to_PNG(numpy_array, save_path):
with open(save_path, 'wb+') as fh:
save_array_img(numpy_array, save_path, img_format='png')
sync(fh)
return save_path
def convertToBinaryData(filename):
with open(filename, 'rb') as file:
binaryData = file.read()
return binaryData
def insertBLOB(png_image):
try:
connection = mysql.connector.connect(
user='Your_user_name',
password='Your_MySQL_password',
host='host_address',
database='database_name')
sql_table = 'your_table_name'
image_column_name = 'your_blob_column_name'
file = convertToBinaryData(png_image)
cursor = connection.cursor()
sql_insert_blob_query = "INSERT INTO " + sql_table + " (" + image_column_name + ") VALUES (" + file + ")"
result = cursor.execute(sql_insert_blob_query)
connection.commit()
except mysql.connector.Error as error:
print("Failed inserting BLOB data into MySQL table {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed"
def write_file(data, filename):
# Convert binary data to proper format and write it on Hard Disk
with open(filename, 'wb') as file:
file.write(data)
def readBLOB(save_file_as, ID):
try:
connection = mysql.connector.connect(
user='Your_user_name',
password='Your_MySQL_password',
host='host_address',
database='database_name')
sql_table = 'your_table_name'
image_column_name = 'your_blob_column_name'
cursor = connection.cursor()
sql_fetch_blob_query = "SELECT " + image_column_name + " from " + sql_table + " where ID = '"+ str(ID)
cursor.execute(sql_fetch_blob_query)
record = cursor.fetchone()
file = record[0]
write_file(file, save_file_as)
numpy_arr_sql = load_array_img(save_file_as)
return numpy_arr_sql
# write Numpy array into MySQL database
my_numpy_array = np.ones(150,150)
insertBLOB(save_array_to_PNG(my_numpy_array, 'image.png'))
#retrieve numpy array from MySQL
my_numpy_array_from_SQL = readBLOB('image_from_SQL.png', ID = 1)And if you’re feeling particularly generous, you could buy me a coffee at www.buymeacoffee.com/kingmichael. Your support would greatly help in keeping me motivated and writing articles that you love.
