In [1]:
from getpass import getpass
bd = getpass(prompt='Introduce la bd: ')
us = getpass(prompt='Introduce el usuario: ')
pw = getpass(prompt='Introduce la contraseña: ')
In [2]:
import pymysql
In [3]:
conn = pymysql.connect(host='localhost', port=3306, user=us, passwd=pw, db=bd)
cur = conn.cursor()
cur.execute("SELECT tipo_fecha, COUNT( * ) FROM pce_fecha GROUP BY tipo_fecha ")
Out[3]:
In [4]:
for row in cur:
print(row)
In [5]:
cur.execute("SELECT COUNT(DISTINCT id_licitacion) FROM pce_fecha ")
Out[5]:
In [6]:
for row in cur:
print(row)
In [7]:
%load_ext sql
In [8]:
%sql mysql+pymysql://{us}:{pw}@localhost/{bd}
Out[8]:
In [9]:
%sql SELECT tipo_fecha, year(fecha) as y, COUNT( * ) as num_exp FROM pce_fecha GROUP BY tipo_fecha, y
Out[9]:
In [10]:
%sql SELECT COUNT( DISTINCT `id_licitacion` ) FROM `pce_fecha`
Out[10]:
In [11]:
%sql SELECT count(*) from pce_expediente
Out[11]:
In [12]:
%sql SELECT tipo_documento, COUNT( * ) FROM pce_documento GROUP BY tipo_documento
Out[12]:
In [13]:
%%sql SELECT ex.id_ministerio, year(fe.fecha) as year, count(*) as num_exp FROM pce_expediente ex
LEFT JOIN pce_fecha fe ON (fe.id_licitacion=ex.id_licitacion)
WHERE fe.tipo_fecha = 'Adjudicación'
GROUP BY ex.id_ministerio, year;
Out[13]:
In [14]:
%sql SELECT count(*) from pce_expediente
Out[14]:
In [15]:
result = %sql SELECT ex.id_ministerio, year(fe.fecha) as year, count(*) as num_exp FROM pce_expediente ex LEFT JOIN pce_fecha fe ON (fe.id_licitacion=ex.id_licitacion) WHERE fe.tipo_fecha = 'Adjudicación' GROUP BY ex.id_ministerio, year;
In [16]:
%matplotlib inline
In [17]:
result.bar()
Out[17]:
In [18]:
type(result)
Out[18]:
In [21]:
result_min = %sql SELECT mini.nombre_corto, count(*) as num_exp FROM pce_expediente ex LEFT JOIN pce_ministerio mini ON (mini.id_ministerio=ex.id_ministerio) GROUP BY mini.nombre_corto;
In [22]:
result_min.bar()
Out[22]:
In [26]:
%%sql SELECT ex.id_ministerio, year(fe.fecha) as year, sum(ex.importe_base)/count(*) as imp_adxexp FROM pce_expediente ex
LEFT JOIN pce_fecha fe ON (fe.id_licitacion=ex.id_licitacion)
WHERE fe.tipo_fecha = 'Adjudicación'
GROUP BY ex.id_ministerio, year;
Out[26]:
In [28]:
importes = %%sql SELECT ex.id_ministerio, sum(ex.importe_base) as imp_lic FROM pce_expediente ex GROUP BY ex.id_ministerio;
In [29]:
importes.bar()
Out[29]:
In [31]:
%sql select * from pce_ministerio
Out[31]:
In [ ]: