Dibuja gráfico y guarda imagen con logo en Python y Pyplot. Código desarrollado para elpetro.pe.
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# https://matplotlib.org/tutorials/introductory/pyplot.html#sphx-glr-tutorials-introductory-pyplot-py
import time
from datetime import datetime, timedelta
import pytz
import sys, getopt, os
import argparse
import urllib2
import mysql.connector
from pymysql import Binary
import matplotlib
matplotlib.use('Agg')
from pylab import *
import matplotlib.pyplot as plt
import matplotlib as mpl
import matplotlib.font_manager as font_manager
import matplotlib.image as image
import mpl_toolkits
import numpy as np
import pandas as pd
import matplotlib.dates as mdates
import matplotlib.cbook as cbook
from matplotlib.dates import DayLocator, HourLocator, DateFormatter, drange
from numpy import arange
import pymysql
import pymysql.cursors
from phpserialize import serialize, unserialize
import zlib
import json
import types
import unicodedata
from datetime import datetime
import png
from PIL import Image, ImageFile
import string
import ast
path_font = '/var/www/elpetro.pe/htdocs/wp-content/plugins/eb/fonts/arial_regular/arial_regular.ttf'
prop = font_manager.FontProperties(fname=path_font)
# BASE DE DATOS
# Connect DDBB
def bbdd_con():
try:
conn = pymysql.connect(host='localhost', port=3306, user='xxx', passwd='xxx', db='xxx', charset='utf8')
except Exception as e:
print(e)
else:
return conn
# Criptodivisas
def get_db_criptodivisas ( id = 0 ) :
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
if id == 0 :
cur.execute("SELECT * FROM c_criptodivisa WHERE nolistado = 0 AND enabled = 1 ORDER BY name")
else :
cur.execute("SELECT * FROM c_criptodivisa WHERE id_c_criptodivisa = %s", ( [id] ))
items = cur.fetchall()
cur.close()
conn.close()
return items
# Divisas
def get_db_divisas ( id = 0 ) :
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
if id == 0 :
cur.execute("SELECT * FROM c_divisa WHERE enabled = 1 ORDER BY menu_order" )
else :
cur.execute("SELECT * FROM c_divisa WHERE id_c_divisa = %s", ( [id] ))
items = cur.fetchall()
cur.close()
conn.close()
return items
# Criptodivisa Json
def get_db_prices_criptodivisas_hours ( hours = 24, compression = 4) :
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("SELECT id_c_criptodivisa_json, datetime, json_data, compression FROM c_criptodivisa_json WHERE datetime >= DATE_SUB(NOW(),INTERVAL %s HOUR) ORDER BY datetime ASC", ( [ hours, ] ) )
items = cur.fetchall()
cur.close()
conn.close()
price24 = []
for item in items :
id_c_criptodivisa_json = item[0]
datetime = item[1]
compression = item[3]
if compression == 4 :
json_data = json.loads(zlib.decompress ( item[2] ))
elif compression == 2 :
unserialize_j = unserialize( zlib.decompress(item[2]))
json_data_dumps = json.dumps(unserialize_j).replace("'", "\"")
json_data = json.loads(json_data_dumps)
price24.append ( { 'id_c_criptodivisa_json' : id_c_criptodivisa_json, 'datetime' : datetime, 'json_data' : json_data, 'compression' : compression } )
return price24
# Criptodivisa Json ID
def get_db_criptodivisa_id ( id ) :
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("""SELECT id_c_criptodivisa_json, datetime, json_data, json_data, compression FROM c_criptodivisa_json WHERE id_c_criptodivisa_json = %s""" , ( [ id, ]))
items = cur.fetchall()
cur.close()
conn.close()
return items
# Divisas Json (tipos de cambio)
def get_db_divisas_json () :
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute( """SELECT * FROM c_divisa_json WHERE compression = 4 ORDER BY datetime DESC LIMIT 1""" )
items = cur.fetchall()
cur.close()
conn.close()
return items
# FILE GET CONTENTS
def file_get_contents(filename, use_include_path = 0, context = None, offset = -1, maxlen = -1):
if (filename.find('://') > 0):
ret = urllib2.urlopen(filename).read()
if (offset > 0):
ret = ret[offset:]
if (maxlen > 0):
ret = ret[:maxlen]
return ret
else:
fp = open(filename,'rb')
try:
if (offset > 0):
fp.seek(offset)
ret = fp.read(maxlen)
return ret
finally:
fp.close( )
# CREA CHARTS
def draw_chart ( idc, datetime, plot_x, plot_y, criptodivisa_name, criptodivisa_symbol, divisa_name, divisa_symbol ) :
formatter = DateFormatter('%d/%m %H:%M')
json_date_time_file = datetime.strftime('%Y%m%d%H%M')
file_name = str(idc) + "_" + criptodivisa_symbol + "_" + divisa_symbol + ".png"
path_file = "/var/www/elpetro.pe/htdocs/wp-content/plugins/eb/graficos/"
min_date = plot_x[0]
max_date = plot_x[-1]
str_date = min_date.strftime('%d %b %Y - %H:%M') + " al " + max_date.strftime('%d %b %Y - %H:%M')
titulo = "Precios del " + criptodivisa_symbol + " " + criptodivisa_name + " en " + divisa_name + " (" + divisa_symbol + ")"
titulo_x = "Del " + str_date + " (UTC-5/Lima)"
titulo_y = "Precio en " + divisa_symbol
x = plot_x
y = plot_y
fig = plt.figure()
fig, ax = plt.subplots(figsize=(15,5))
ax.plot(x, y)
fig.suptitle(titulo, fontsize=20)
ax.set(xlabel=titulo_x, ylabel=titulo_y )
plt.margins(0.000,0.1)
ttl = ax.title
ttl.set_position([.5, 1.03])
fig.autofmt_xdate()
plt.gcf().axes[0].xaxis.set_major_formatter(formatter)
plt.xticks(fontsize=8)
ax.xaxis.labelpad = 20
ax.grid()
logo = plt.imread("/var/www/elpetro.pe/python/elpetroperu_chart_25.png")
ax.figure.figimage(logo, 120, 130, alpha=1, zorder=1)
url_graf = path_file + file_name
url_graf_th = path_file + "t_" + file_name
fig.savefig(url_graf, bbox_inches='tight', pad_inches = 0.25)
fig_th = image.thumbnail(url_graf, url_graf_th, scale=0.25)
plt.close('all')
# DIBUJA CHART 24 HORAS TEMP
def last24 ( dtime = -1 ) :
precios24 = get_db_prices_criptodivisas_hours ()
criptodivisas = get_db_criptodivisas ()
divisas = get_db_divisas ()
id_errors = []
for criptodivisa in criptodivisas :
done = False
criptodivisa_name = criptodivisa[1]
criptodivisa_symbol = criptodivisa[2]
for divisa in divisas :
divisa_name = divisa[1]
divisa_symbol = divisa[2]
plot_x = []
plot_y = []
for item_criptodivisa in precios24:
id_c_criptodivisa_json = item_criptodivisa.get('id_c_criptodivisa_json')
datetime = item_criptodivisa.get('datetime')
datetime_pe = datetime
if dtime > 0 :
datetime_pe = datetime + timedelta( hours = dtime )
elif dtime < 0 :
datetime_pe = datetime - timedelta( hours = abs ( dtime ))
data_y = item_criptodivisa.get('json_data')
y = 0
try :
y = data_y[criptodivisa_symbol][divisa_symbol] #[criptodivisa_symbol][divisa_symbol]
if y :
plot_y.append( y )
plot_x.append( datetime_pe )
else :
id_errors.append( id_c_criptodivisa_json )
done = True
except :
id_errors.append( id_c_criptodivisa_json )
done = True
if done :
break
if done :
break
draw_chart ( id_c_criptodivisa_json, datetime, plot_x, plot_y, criptodivisa_name, criptodivisa_symbol, divisa_name, divisa_symbol)
id_c_criptodivisa_json = precios24[-1].get('id_c_criptodivisa_json')
date_min = precios24[0].get('datetime') - timedelta( hours = abs ( dtime ))
date_max = precios24[-1].get('datetime') - timedelta( hours = abs ( dtime ))
str_date = " del " + date_min.strftime('%d %b %Y - %H:%M') + " al " + date_max.strftime('%d %b %Y - %H:%M') + " (UTC-5/Lima)."
if len( id_errors ) :
print "ERRORS price24[] " , id_errors
t = datetime.now() - timedelta(hours=6)
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("""INSERT INTO c_figure ( id_c_criptodivisa_json, fechahora, date_str ) VALUES ( %s, %s, %s )""", ([id_c_criptodivisa_json, t, str_date ]))
conn.commit()
cur.close()
conn.close()
# LOAD RATES FROM EXTERNAL JSON
def get_json_rate () :
api_id = "xxx"
json_url = "https://openexchangerates.org/api/latest.json?app_id=" + api_id
jsond = json.loads (file_get_contents( json_url ))
return jsond
# INSERT RATES IN LOCAL BD
def set_rate_db (rates ) :
json_compress = zlib.compress ( json.dumps ( rates ) )
compression = 4
t = datetime.now() - timedelta(hours=6)
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("""INSERT INTO c_divisa_json (datetime, json_data, compression) VALUES (%s, %s, %s)""", ( [ t, json_compress , compression ]))
conn.commit()
cur.close()
conn.close()
# LOAD RATES FROM DB
def get_db_rate ( compression = 4 ) :
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("""SELECT id_c_divisa_json, datetime, json_data, compression FROM c_divisa_json WHERE compression = %s ORDER BY datetime DESC LIMIT 1""", ( [ compression, ]))
items = cur.fetchall()
cur.close()
conn.close()
item = items[0]
id_c_divisa_json = item[0]
datetime = item[1]
json_data = json.loads(zlib.decompress(item[2]))
compression = item[3]
rates = { 'id_c_divisa_json' : id_c_divisa_json, 'datetime' : datetime, 'json_data': json_data, 'compression': compression }
return rates
def get_db_rate_alternative ( compression = 4 ) :
divisas = get_db_divisas ()
rates = {}
id_c_divisa_alt = ""
datetime = ""
for divisa in divisas:
key_divisa = divisa[4]
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("""SELECT id_c_divisa_alt, divisa_symbol, price, datetime FROM c_divisa_alt WHERE divisa_symbol = %s ORDER BY datetime DESC LIMIT 1""", ( [ key_divisa, ]))
items = cur.fetchall()
cur.close()
conn.close()
item = items[0]
id_c_divisa_alt = item[0]
datetime = item[3]
rates[key_divisa] = item[2]
cambio = {}
cambio['rates'] = json.loads (json.dumps ( (rates)))
return cambio
# LOAD CRIPTODIVISAS PRICES FROM EXTERNAL JSON
def get_json_criptodivisas_price () :
criptodivisas = get_db_criptodivisas ()
divisas = get_db_divisas ()
divisas_json = get_db_divisas_json ()
divisas_rate = json.loads(zlib.decompress(divisas_json[0][2]))
criptodivisa_symbols = []
for criptodivisa in criptodivisas :
criptodivisa_symbols.append( criptodivisa[2])
criptodivisas_query = ','.join( criptodivisa_symbols )
divisa_symbols = []
for divisa in divisas :
divisa_symbols.append( divisa[4])
divisas_query = ','.join( divisa_symbols )
api_id = "xxx"
json_url = "https://min-api.cryptocompare.com/data/pricemulti?fsyms=" + criptodivisas_query + "&tsyms=" + divisas_query
prices = json.loads ( file_get_contents( json_url ))
return prices
# INSERT CRIPTODIVISAS PRICES BBDD IN DB (MEMORY JSON ARRAY)
def set_db_price_criptodivisas ( precios, compression = 4 ) :
t = datetime.now() - timedelta(hours=6)
json_compress = zlib.compress ( json.dumps (precios ) )
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("""INSERT INTO c_criptodivisa_json (datetime, json_data, compression) VALUES (%s, %s, %s)""", ( [ t , json_compress , compression ]))
conn.commit()
cur.close()
conn.close()
def compare_criptodivisas ( horas = 24 ) :
precios24 = get_db_prices_criptodivisas_hours ()
criptodivisas = get_db_criptodivisas ()
divisas = get_db_divisas ()
values = {}
for criptodivisa in criptodivisas :
criptodivisa_symbol = criptodivisa[2]
values[criptodivisa_symbol.decode('utf-8')] = {}
for divisa in divisas :
divisa_symbol = divisa[2]
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')] = {}
precios = []
precio_open = precios24[0].get ('json_data')[criptodivisa_symbol][divisa_symbol]
precio_close = precios24[-1].get ('json_data')[criptodivisa_symbol][divisa_symbol]
for precio24 in precios24 :
data = precio24.get ('json_data')
precios.append ( data[criptodivisa_symbol][divisa_symbol] )
precio_max = max ( precios )
precio_min = min ( precios )
precio_diff = precio_max - precio_min
precio_diff_porcentaje = get_change_porcentage (precio_max, precio_min)
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio'] = {}
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_max'] = precio_max
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_min'] = precio_min
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_open'] = precio_open
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_close'] = precio_close
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_diff'] = precio_diff
values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_diff_porcentaje'] = precio_diff_porcentaje
criptodivisa_symbol = u'BTC'
divisa_symbol = u'EUR'
precio_max = values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_max']
precio_min = values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_min']
precio_open = values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_open']
precio_close = values[criptodivisa_symbol.decode('utf-8')][divisa_symbol.decode('utf-8')]['precio']['precio_close']
path_file = "/var/www/elpetro.pe/htdocs/wp-content/plugins/eb/graficos_compare/"
url_graf = path_file + "c" + str( time.time() ) + ".png"
width = 0.1 # the width of the bar
fig, ax = plt.subplots()
rects1 = ax.bar( .1 , precio_open, width,)
rects2 = ax.bar( .2 , precio_min, width,)
rects2 = ax.bar( .3 , precio_max, width,)
rects2 = ax.bar( .4 , precio_close, width,)
fig.savefig(url_graf, bbox_inches='tight', pad_inches = 0.25)
plt.close('all')
def debug_db_rate ( id_min = 0, id_max = 0, divisa = False) :
if id_min == 0 :
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("SELECT id_c_divisa_json FROM c_divisa_json ORDER BY datetime DESC LIMIT 1")
item = cur.fetchone()
if item is not None:
id_min = item[0]
cur.close()
conn.close()
if id_max == 0 :
id_max = id_min
conn = bbdd_con()
cur = conn.cursor()
cur.execute("set names utf8;")
cur.execute("""SELECT id_c_divisa_json, datetime, json_data, compression FROM c_divisa_json WHERE id_c_divisa_json>=%s AND id_c_divisa_json<=%s""", ( [ id_min , id_max ]))
items = cur.fetchall()
cur.close()
conn.close()
for item in items :
data = json.loads(zlib.decompress ( item[2] ))
if divisa :
data_val = data.get ('rates')[divisa.encode('utf-8')]
print item[0], item[1], "divisa " + divisa + ": " + str ( data_val ), item[3]
else :
print item[0], item[1], data, item[3]
# CALCULA PORCENTAJE
def get_change_porcentage (current, previous) :
if current == previous:
return 0
try:
return (((current - previous))/previous)*100.0
except ZeroDivisionError:
return 0
# ARGS PARSER
def main(argv):
parser = argparse.ArgumentParser()
parser.add_argument('-q', default="24", help="Query to process")
parser.add_argument('-min', default=0, type=int, help="Min id query")
parser.add_argument('-max', default=0, type=int, help="Max id query")
parser.add_argument('-divisa', default=False, help="Divisa symbol in query")
args = parser.parse_args()
if args.q == 'get_json_rate' :
rates = get_json_rate ()
return rates
if args.q == 'set_rate_db' :
rates = get_json_rate ()
set_rate_db ( rates )
if args.q == 'get_db_rate' :
rates = get_db_rate_alternative ()
return rates
if args.q == 'get_json_criptodivisas_price' :
prices = get_db_rate ()
return prices
if args.q == 'add_json_array_PTR' :
prices = get_json_criptodivisas_price ()
prices_ptr = add_json_array_PTR ( prices )
return prices_ptr
if args.q == 'set_db_price_criptodivisas' :
prices = get_json_criptodivisas_price ()
prices_ptr = add_json_array_PTR ( prices )
set_db_price_criptodivisas ( prices_ptr )
if args.q == 'last24' :
last24 ()
if args.q == 'history' :
history ()
if args.q == 'compare_criptodivisas' :
compare_criptodivisas ()
if args.q == 'debug_db_rate' :
id_min = args.min
id_max = args.max
divisa = args.divisa
debug_db_rate ( id_min, id_max, divisa)
if __name__ == "__main__":
main(sys.argv[1:])