The problem consists of two parts, one is to upload the data which is an 8*8 numpy array to the mysql database, the other one is to retrieve and update the data with seaborn and matplotlib. For the first part, I has been solved, anyone can create a database with 64 float value and id as primary key. However, the second part is very confusing, I learn from certain website to do things as test with numpy random value generator, it works well. However, when I use my own retrieving code from the database, it will keep the same value instead of retrieving the latest one. Even if the new values are kept inserting to the database. This is part of my uploading file:
import mysql.connector
import serial
import numpy as np
from matplotlib import pyplot as plt
import seaborn as sns
import pandas as pd
import openpyxl
from multiprocessing import Process, cpu_count, Pool
from matplotlib.animation import FuncAnimation
ser = serial.Serial('', )
ser.close()
print(ser.name)
temarray = []
host_str = ""
user_str = ""
password_str = ""
dbname = ""
pydb = mysql.connector.connect(host=host_str, user=user_str, password=password_str, database=dbname)
sql_insert_stmt = "insert into sensor_reads(value0, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24, value25, value26, value27, value28, value29, value30, value31, value32, value33, value34, value35, value36, value37, value38, value39, value40, value41, value42, value43, value44, value45, value46, value47, value48, value49, value50, value51, value52, value53, value54, value55, value56, value57, value58, value59, value60, value61, value62, value63) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
sql_retrieve_stmt = "select value0, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24, value25, value26, value27, value28, value29, value30, value31, value32, value33, value34, value35, value36, value37, value38, value39, value40, value41, value42, value43, value44, value45, value46, value47, value48, value49, value50, value51, value52, value53, value54, value55, value56, value57, value58, value59, value60, value61, value62, value63 from sensor_reads ORDER BY id DESC LIMIT 0, 1;"
cursor1 = pydb.cursor()
cursor2 = pydb.cursor()
def animate(list_corr0):
ax = sns.heatmap(list_corr0, annot=True, fmt='.1f',
vmin=0, vmax=300, linewidth=0.5)
ax.invert_yaxis()
ax.set(xlabel='Column number', ylabel='Row number')
def readcom():
with serial.Serial(' ', ) as ser:
while True:
line0 = ser.readline()
line0 = ser.readline()
if line0 != None:
line = line0[0: -3]
print(line)
line = line.decode('utf-8')
print(line)
line = line.split(",")
print(line)
list = np.array(line)
print(list)
list = list.astype(np.float64)
list = list / 10
print(list)
print("Size: ", list.shape[0])
data = (list[0], list[1], list[2], list[3], list[4], list[5], list[6], list[7], list[8], list[9],
list[10], list[11], list[12], list[13], list[14], list[15], list[16], list[17], list[18], list[19],
list[20], list[21], list[22], list[23], list[24], list[25], list[26], list[27], list[28], list[29],
list[30], list[31], list[32], list[33], list[34], list[35], list[36], list[37], list[38], list[39],
list[40], list[41], list[42], list[43], list[44], list[45], list[46], list[47], list[48], list[49],
list[50], list[51], list[52], list[53], list[54], list[55], list[56], list[57], list[58], list[59],
list[60], list[61], list[62], list[63] )
cursor1.execute(sql_insert_stmt, data)
pydb.commit()
def main():
process1 = Process(target=readcom)
process1.start()
process1.join()
if __name__ == '__main__':
main()
ser.close()
pydb.close()
This is my retrieve and plot file:
import mysql.connector
import numpy as np
from matplotlib import pyplot as plt
import seaborn as sns
import pandas as pd
import openpyxl
from multiprocessing import Process, cpu_count, Pool
import matplotlib.animation as animation
import multiprocessing
temarray = []
host_str = ""
user_str = ""
password_str = ""
dbname = ""
pydb = mysql.connector.connect(
host=host_str, user=user_str, password=password_str, database=dbname)
sql_insert_stmt = "insert into sensor_reads(value0, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24, value25, value26, value27, value28, value29, value30, value31, value32, value33, value34, value35, value36, value37, value38, value39, value40, value41, value42, value43, value44, value45, value46, value47, value48, value49, value50, value51, value52, value53, value54, value55, value56, value57, value58, value59, value60, value61, value62, value63) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
sql_retrieve_stmt = "select value0, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24, value25, value26, value27, value28, value29, value30, value31, value32, value33, value34, value35, value36, value37, value38, value39, value40, value41, value42, value43, value44, value45, value46, value47, value48, value49, value50, value51, value52, value53, value54, value55, value56, value57, value58, value59, value60, value61, value62, value63 from sensor_reads ORDER BY id DESC LIMIT 0, 1;"
cursor1 = pydb.cursor()
def retrieve():
# listfromdb = np.zeros(64)
cursor2 = pydb.cursor()
cursor2.execute(sql_retrieve_stmt)
result = cursor2.fetchall()
result = np.array(result)
temparray1 = result.reshape(8, 8)
temparray2 = np.array(temparray1)
temparray3 = temparray2.astype(np.float32)
for i in range(temparray3.shape[0]):
for j in range(temparray3.shape[1]):
temparray3[i, j] = temparray3[i, j]
cursor2.execute(sql_retrieve_stmt)
result = cursor2.fetchall()
result = np.array(result)
temparray1 = result.reshape(8, 8)
temparray2 = np.array(temparray1)
temparray3 = temparray2.astype(np.float32)
for i in range(temparray3.shape[0]):
for j in range(temparray3.shape[1]):
temparray3[i, j] = temparray3[i, j]
listfromdb = temparray3.astype(float)
cursor2.close()
return listfromdb
def animate_heat_map():
fig = plt.figure()
nx = ny = 8
data = retrieve()
ax = sns.heatmap(data, annot=True, vmin = 0, vmax=300)
ax.invert_yaxis()
ax.set(xlabel='Column number', ylabel='Row number')
def init():
plt.clf()
ax = sns.heatmap(data, annot=True, vmin = 0, vmax=300)
ax.invert_yaxis()
ax.set(xlabel='Column number', ylabel='Row number')
def animate(i):
plt.clf()
data = retrieve()
ax = sns.heatmap(data, annot=True, vmin = 0, vmax=300)
ax.invert_yaxis()
ax.set(xlabel='Column number', ylabel='Row number')
anim = animation.FuncAnimation(fig, animate, init_func=init, interval=1000)
plt.show()
def main():
process1 = Process(target=retrieve)
process2 = Process(target=animate_heat_map)
process1.start()
process2.start()
process1.join()
process2.join()
if __name__ == '__main__':
main()
There might be some overlapping in the code, as I originally want them to be run in one file. The biggest problem is in the retrieve and plot file that it is not updating even if I think my retrieve function is keep running.
Well, the problem is easier than I thought, I just need to pydb.close() every time I retrieve the values from the Mysql, and then everything went well.