# coding: utf-8

# In[ ]:


#!/usr/bin/python


# In[ ]:


import requests
from sqlalchemy.sql import func
import pandas as pd
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
import numpy as np


# In[ ]:


engine = create_engine("sqlite:////home/jovyan/m3-01orm-ferag/flujoinmigracion.db")

df = pd.read_csv(sys.argv[1], parse_dates=True, sep=';', encoding = 'utf-8')
#reemplazo los valores NaN por ceros y quito los puntos que separan los millares en las cifras
df['Total']=df['Total'].str.replace('.', '')
df = df.replace(np.nan,0)


# In[ ]:


df.to_sql(con=engine, name='flujoinmigracion', if_exists='replace')
res = engine.execute("SELECT sql FROM sqlite_master WHERE name = 'flujoinmigracion'")
for e in res:
    create_table = e[0]
create_table
new_create_table = 'CREATE TABLE flujoinmigracion (\n\t"index" BIGINT PRIMARY KEY, \n\t"Provincias" TEXT, \n\t"PaisNacimiento" TEXT, \n\t"Periodo" BIGINT, \n\t"Total" BIGINT\n)'
engine.execute("ALTER TABLE flujoinmigracion RENAME TO old_flujoinmigracion;")
engine.execute(new_create_table)
engine.execute("INSERT INTO flujoinmigracion SELECT * FROM old_flujoinmigracion")
Base = automap_base()
# engine, suppose it has many tables
engine = create_engine("sqlite:////home/jovyan/m3-01orm-ferag/flujoinmigracion.db")

# reflect the tables
Base.prepare(engine, reflect=True)
for e in Base.classes:
    print(e)
Flujo = Base.classes.flujoinmigracion
session = Session(engine)


# In[ ]:


rset = session.query(Flujo.PaisNacimiento.label("PaisNacimiento"),Flujo.Total.label("Total_inmigraciones")).filter(Flujo.Provincias.like('%Cantabria%'),Flujo.Periodo=="2020",Flujo.PaisNacimiento!="Total").order_by(Flujo.Total.desc())
rset = list(rset)
col1 = [i[0] for i in rset]
col2 = pd.to_numeric([i[1] for i in rset])
immigrants_per_country = pd.DataFrame(
    {'PaisNacimiento': col1,
     'Total_inmigraciones': col2,
    })

immigrants_per_country = immigrants_per_country.set_index(['PaisNacimiento']) #Indice para mostrar en el histograma
immigrants_per_country

#We use order_per_customer.head(15).plot.bar(); to show only the 15 first
immigrants_per_country.head(15).plot.bar();

plt.legend(['Inmigrantes por paises que llegan a Cantabria en 2020'],loc='upper left')
plt.xlabel('País de nacimiento')
plt.ylabel('Total de inmigrantes')

fig_size = plt.rcParams["figure.figsize"]

# Set figure width to 12 and height to 9
fig_size[0] = 12
fig_size[1] = 9
plt.rcParams["figure.figsize"] = fig_size

plt.show()