Dengue Alerta

In [1]:

    

from matplotlib import pyplot as plt
from eralchemy import render_er
from IPython.display import display 
from PIL import Image
from datetime import datetime, date, timedelta

import os
import matplotlib as mpl
import numpy as np
import pandas as pd
import psycopg2
import pytz
import sys
import sqlalchemy as sqla


    

In [2]:

    

dir_root = os.path.dirname(os.getcwd())
dir_app = os.path.join(
    dir_root, 'AlertaDengue'
)

sys.path.insert(0, dir_app)
from AlertaDengue import settings
from dados import dbdata


    

In [3]:

    

# connection
db_dsn = 'postgresql://{}:{}@{}/{}'.format(
    settings.PSQL_USER,
    settings.PSQL_PASSWORD,
    settings.PSQL_HOST,
    settings.PSQL_DB)
engine = sqla.create_engine(db_dsn)

# connection info_dengue
db_dsn2 = 'postgresql://{}:{}@{}/{}'.format(
    settings.PSQL_USER,
    settings.PSQL_PASSWORD,
    settings.PSQL_HOST,
    'infodengue')
engine_info_dengue = sqla.create_engine(db_dsn2)


    

In [4]:

    

# scheme
pd.read_sql('SELECT nspname FROM pg_catalog.pg_namespace;', engine)

# Dengue_global
# Municipio


    

    
Out[4]:






  

    

      

      
nspname
    
  
  

    

      
0
      
pg_toast
    
    

      
1
      
pg_temp_1
    
    

      
2
      
pg_toast_temp_1
    
    

      
3
      
pg_catalog
    
    

      
4
      
information_schema
    
    

      
5
      
Dengue_global
    
    

      
6
      
Municipio
    
    

      
7
      
public
    
  

In [5]:

    

# Schema: Dengue_global

sql = '''
SELECT table_name
FROM information_schema.tables
WHERE table_schema='Dengue_global'
ORDER BY table_schema,table_name;
'''

display(pd.read_sql(sql, engine))

render_er(db_dsn, '/tmp/img.png', schema='Dengue_global')
Image.open('/tmp/img.png')


    

    







  

    

      

      
table_name
    
  
  

    

      
0
      
CID10
    
    

      
1
      
estado
    
    

      
2
      
Municipio
    
    

      
3
      
regional_saude
    
  








    
Out[5]:

In [6]:

    

sql = 'SELECT * from "Dengue_global"."Municipio"'
pd.read_sql(sql, engine).head()


    

    
Out[6]:






  

    

      

      
geocodigo
      
nome
      
geojson
      
populacao
      
uf
    
  
  

    

      
0
      
2204303
      
Fronteiras
      
{"id": null, "geometry": {"coordinates": [[[-4...
      
11411
      
Piauí
    
    

      
1
      
2501807
      
Bayeux
      
{"id": null, "geometry": {"coordinates": [[[-3...
      
95677
      
Paraíba
    
    

      
2
      
2905008
      
Caculé
      
{"id": null, "geometry": {"coordinates": [[[-4...
      
23392
      
Bahia
    
    

      
3
      
3105608
      
Barbacena
      
{"id": null, "geometry": {"coordinates": [[[-4...
      
133972
      
Minas Gerais
    
    

      
4
      
1100015
      
Alta Floresta D'Oeste
      
{"id": null, "geometry": {"coordinates": [[[-6...
      
25652
      
Rondônia
    
  

In [7]:

    

# Schema: Municipio

sql = '''
SELECT table_name
FROM information_schema.tables
WHERE table_schema='Municipio'
ORDER BY table_schema,table_name;
'''

display(pd.read_sql(sql, engine))

render_er(db_dsn, '/tmp/img.png', schema='Municipio')
Image.open('/tmp/img.png')


    

    







  

    

      

      
table_name
    
  
  

    

      
0
      
alerta_mrj
    
    

      
1
      
Bairro
    
    

      
2
      
Clima_cemaden
    
    

      
3
      
Clima_Satelite
    
    

      
4
      
Clima_wu
    
    

      
5
      
Estacao_cemaden
    
    

      
6
      
Estacao_wu
    
    

      
7
      
Historico_alerta
    
    

      
8
      
Localidade
    
    

      
9
      
Notificacao
    
    

      
10
      
Ovitrampa
    
    

      
11
      
Tweet
    
  








    
Out[7]:

In [8]:

    

# Schema: public

sql = '''
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
ORDER BY table_schema,table_name;
'''

display(pd.read_sql(sql, engine_info_dengue))

render_er(db_dsn2, '/tmp/img.png', schema='public')
Image.open('/tmp/img.png')


    

    







  

    

      

      
table_name
    
  
  

    

      
0
      
auth_group
    
    

      
1
      
auth_group_permissions
    
    

      
2
      
auth_permission
    
    

      
3
      
auth_user
    
    

      
4
      
auth_user_groups
    
    

      
5
      
auth_user_user_permissions
    
    

      
6
      
dbf_dbf
    
    

      
7
      
dbf_dbfchunkedupload
    
    

      
8
      
Dengue_2010
    
    

      
9
      
Dengue_2011
    
    

      
10
      
Dengue_2012
    
    

      
11
      
Dengue_2013
    
    

      
12
      
DengueConfirmados_2013
    
    

      
13
      
django_admin_log
    
    

      
14
      
django_content_type
    
    

      
15
      
django_migrations
    
    

      
16
      
django_session
    
    

      
17
      
geography_columns
    
    

      
18
      
geometry_columns
    
    

      
19
      
raster_columns
    
    

      
20
      
raster_overviews
    
    

      
21
      
spatial_ref_sys
    
  








    




/home/xmn/anaconda3/envs/AlertaDengue/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py:2439: SAWarning: Did not recognize type 'geometry' of column 'geom'
  (attype, name))






    
Out[8]: