python - Plotting mysql data on a real time graph using pyplot -

plotting mysql data on real time graph using pyplot

hi everyone, reading in advance. i've been working on mysql database, adding sample every few seconds sensor, , create table every one, tables don't become huge. i've done this, want know plot last 15 recent samples database, last 15, use query extract data mysql:

"select columnname tablename id > ((select max(id) tablename) - (15 + 1)) order id asc;"

here's code:

#!/usr/bin/python # -*- coding: utf-8 -*-  import mysqldb, time, locale, datetime, pyoo, random, os.path, matplotlib matplotlib import pyplot plt itertools import chain subprocess import popen import rpi.gpio gpio import mysql.connector import numpy np  locale.setlocale(locale.lc_all, '')  def informacion():         info_dato = round(random.uniform(1,5),2)         info_dia = time.strftime('%a')         info_fecha_hora = time.strftime('%y-%m-%d %h:%m:%s')         info_lugar = 'cd python'         return info_dato, info_dia, info_fecha_hora, info_lugar  def tiempo():         tiempo_justo_ahora = datetime.datetime.now()         tiempo_dia = tiempo_justo_ahora.strftime('%a')         tiempo_fecha = tiempo_justo_ahora.strftime('%x')         tiempo_hora_actual = tiempo_justo_ahora.strftime('%x')         tiempo_hoy = tiempo_justo_ahora         while (tiempo_hoy.isoweekday() > 1):                tiempo_hoy = tiempo_hoy - (datetime.timedelta(days = 1))         tiempo_iso = tiempo_hoy.isocalendar()         tiempo_fecha_inicio = tiempo_hoy.strftime('%x')         tiempo_fecha_fin = (tiempo_hoy + datetime.timedelta(days=6)).strftime('%x')         time.sleep(0.1)         return tiempo_dia, tiempo_fecha, tiempo_hora_actual, tiempo_iso[0], tiempo_iso[1], tiempo_fecha_inicio, tiempo_fecha_fin  x = tiempo() chequeo = 'el programa fue ejecutado el dia %s %s las %s' % (x[0],x[1], x[2]) print chequeo #this know when program executed, prints # "the program execute day %(day in letters)s %(date)s %(time)s  parametros = { 'user': 'xxxx', 'password': 'xxxx', 'host': 'localhost', 'database': 'xxxx', 'raise_on_warnings': true, 'collation': 'utf8_spanish2_ci',}  idioma = "set lc_time_names = 'es_pa';" tabla = "semana_%s_del_%s" % (x[4], x[3]) crear_tabla = ("create table %s (num_muestra int auto_increment not null,"             "valor float not null, dia char(10) not null, fecha_hora datetime not null, "             "punto_muestra char(10) not null, primary key (num_muestra)) character set utf8 collate utf8_spanish2_ci;") insertar_dato = ("insert %s (valor, dia, fecha_hora, punto_muestra) values"             "(%f, '%s', str_to_date('%s','%%y-%%m-%%d %%h:%%i:%%s'), '%s')") existe_tabla = "select count(*) information_schema.tables table_schema = 'prueba1' , table_name = '%s';" buscar_datos = "select %s %s;" datos_grafica = ("select %s %s num_muestra > "              "((select max(num_muestra) %s) - %d) order num_muestra asc;")  #...functions query...too long, tough if needed please let me know :)  def formatos_grafica (fig_subplot):        plt.cla()        fig_subplot.patch.set_facecolor('lightgrey')        fig_subplot.xaxis.set_major_formatter(formato_fecha)        fig_subplot.set_title('prueba 1')        fig_subplot.set_xlabel('tiempo')        fig_subplot.set_ylabel('valor')        fig_subplot.grid(true)  formato_fecha = matplotlib.dates.dateformatter('%a\n%h:%m:%s') plt.ion() figura = plt.figure() ax = figura.add_subplot(111) formatos_grafica(ax) plt.show(block = false)  try:       #checking if database exist, otherwise create it...       #...adding value informacion() function database..        #...graph part       ti = extraer_datos(datos_grafica % ('fecha_hora', tabla, tabla, 16))       tiempos = matplotlib.dates.date2num(ti)       valores = extraer_datos(datos_grafica % ('valor', tabla, tabla, 16))       formatos_grafica(ax)       ax.plot(tiempos, valores)       ax.scatter(tiempos, valores, color = 'green')       figura.canvas.draw()       time.sleep(5)  except keyboardinterrupt:        print('\n se ha ejecutado la salida manual del programa (control + c)')       plt.cla()       plt.clf()       plt.close()  except exception e:        print('ha ocurrido un error inesperado \n')       print(e)       plt.cla()       plt.clf()       plt.close() 

what code made want, plot last 15 (altough count 16 unknown reason me, doesn't bother bbut it's kinda weird) in times around 9 12 seconds per graph (adding 5 seconds time.sleep(5) function , approximately 0.5 since tested stopwatch on phone jaja)

so here questions regarding issue:

1. am using matplotlib pyplot function in bad way?
2. for function erase axes , format again before ploting, it's because times didn't this, have error on graph, everytime appears new plot on same axes (different plots different colors, that's how notice). there's way change this?
3. before loop, warning saying: "futurewarning: elementwise comparison failed; returning scalar instead, in future perform elementwise comparison if self._edgecolors == str('face'):", should worry about, or bug since searched far it, of times warning happen it's because of bug.
4. if there's way make real time plotting faster using matplotlib? i've been reading animation, i'm not sure if it's worth, or graph @ arouund same time per sample.
5. i'm using plt.ion() activate interactive mode, i've heard don't need if want use interface tinker (which plan make , use after plotting correctly samples). true?
6. i tough of way of not having query database 15 newest samples, involves doing once prior loop , store in list, everytime new sample, add iit list , drop 1 list. there's way this?

best regards

**edit: tips or advise appreciated. lot :)