Essay


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

Database


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]: