DB_DEMO



In [1]:
from sqlalchemy import create_engine, text
from AlertaDengue.settings import (
    PSQL_DB, PSQL_HOST, PSQL_PASSWORD, PSQL_USER
)

import pandas as pd
import os

In [2]:
tb_no_data = [
    'Notificacao', 'historico_casos', 'Tweet'
]

In [3]:
schemas = ['Dengue_global', 'forecast', 'Municipio']

sql_show_tables = '''
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_schema IN (%s)
ORDER BY table_schema,table_name;
''' % ','.join(["'%s'" % s for s in schemas])

In [4]:
engine = create_engine("postgresql://{}:{}@{}/{}".format(
    PSQL_USER,
    PSQL_PASSWORD,
    PSQL_HOST,
    PSQL_DB
))

In [5]:
with engine.connect() as conn:
    df = pd.read_sql(sql_show_tables, con=conn)
    print(df)


     table_schema                  table_name
0   Dengue_global                       CID10
1   Dengue_global                      estado
2   Dengue_global                   Municipio
3   Dengue_global              regional_saude
4        forecast                  auth_group
5        forecast      auth_group_permissions
6        forecast             auth_permission
7        forecast                   auth_user
8        forecast            auth_user_groups
9        forecast  auth_user_user_permissions
10       forecast            django_admin_log
11       forecast         django_content_type
12       forecast           django_migrations
13       forecast              django_session
14       forecast              forecast_cases
15       forecast               forecast_city
16       forecast              forecast_model
17      Municipio                  alerta_mrj
18      Municipio             alerta_mrj_chik
19      Municipio             alerta_mrj_zika
20      Municipio                      Bairro
21      Municipio               Clima_cemaden
22      Municipio              Clima_Satelite
23      Municipio                    Clima_wu
24      Municipio             Estacao_cemaden
25      Municipio                  Estacao_wu
26      Municipio            Historico_alerta
27      Municipio       Historico_alerta_chik
28      Municipio       Historico_alerta_zika
29      Municipio             historico_casos
30      Municipio                  Localidade
31      Municipio                 Notificacao
32      Municipio                   Ovitrampa
33      Municipio                       Tweet

In [6]:
os.environ['PSQL_DB'] = PSQL_DB
os.environ['PSQL_DB_DEMO'] = 'ALERTADEMO'

In [7]:
!pg_dump -s -O -x $PSQL_DB > /tmp/alertademo.sql

In [8]:
!dropdb $PSQL_DB_DEMO

In [9]:
!createdb $PSQL_DB_DEMO

In [10]:
!psql -d $PSQL_DB_DEMO < /tmp/alertademo.sql


SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
CREATE TABLE
COMMENT
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
COMMENT
CREATE TABLE
COMMENT
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
COMMENT
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
COMMENT
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
COMMENT
CREATE TABLE
COMMENT
CREATE TABLE
COMMENT
CREATE TABLE
COMMENT
CREATE SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
COMMENT
CREATE TABLE
COMMENT
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
COMMENT
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE VIEW
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

In [11]:
engine_demo = create_engine("postgresql://{}:{}@{}/{}".format(
    PSQL_USER,
    PSQL_PASSWORD,
    PSQL_HOST,
    'ALERTADEMO'
))

In [12]:
dt_types = [
    'timestamp with time zone', 'date', 'timestamp without time zone'
]

log = ''

In [13]:
with engine_demo.connect() as conn_demo:
    for schema in schemas:
        conn_demo.execute('CREATE SCHEMA IF NOT EXISTS "%s";' % schema)
        df_schema = df[df.table_schema == schema]
        
        with engine.connect() as conn:
            for i, row in df_schema.iterrows():
                if row.table_name in tb_no_data:
                    continue
                    
                sql = '''
                SELECT *
                FROM information_schema.columns
                WHERE table_schema = '%s'
                  AND table_name   = '%s'
                ''' % (row.table_schema, row.table_name)

                # treat tables with date/time fields
                df_tb = pd.read_sql(sql, con=conn)
                df_dt = df_tb[df_tb.data_type.isin(dt_types)]

                sql_where = ''
                if not df_dt.empty:
                    sql_where = ' WHERE '
                    sql_where += ' AND '.join([
                        "\"%s\" > (NOW() - INTERVAL '2 year')" % field 
                        for field in df_dt.column_name
                    ])
                
                sql = 'SELECT * FROM "%s"."%s" ' % (row.table_schema, row.table_name)
                sql += sql_where
                sql += ' LIMIT 10000;'
                    
                df_tb = pd.read_sql(sql, con=conn)
                
                print(row.table_schema, row.table_name, df_tb.shape, end=' ... ')
                try:
                    df_tb.to_sql(
                        row.table_name, con=conn_demo, 
                        schema=row.table_schema, if_exists='append', index=False
                    )
                    print('OK')
                except Exception as e:
                    log += str(e)
                    print('FAILED')


Dengue_global CID10 (8349, 2) ... OK
Dengue_global estado (27, 5) ... OK
Dengue_global Municipio (5570, 5) ... OK
Dengue_global regional_saude (1610, 9) ... OK
forecast auth_group (0, 2) ... OK
forecast auth_group_permissions (0, 3) ... OK
forecast auth_permission (39, 4) ... FAILED
forecast auth_user (0, 11) ... OK
forecast auth_user_groups (0, 3) ... OK
forecast auth_user_user_permissions (0, 3) ... OK
forecast django_admin_log (0, 8) ... OK
forecast django_content_type (13, 3) ... OK
forecast django_migrations (23, 4) ... FAILED
forecast django_session (0, 3) ... OK
forecast forecast_cases (0, 8) ... OK
forecast forecast_city (0, 4) ... OK
forecast forecast_model (0, 5) ... OK
Municipio alerta_mrj (1010, 14) ... OK
Municipio alerta_mrj_chik (1010, 14) ... OK
Municipio alerta_mrj_zika (0, 14) ... OK
Municipio Bairro (184, 3) ... FAILED
Municipio Clima_cemaden (10000, 5) ... OK
Municipio Clima_Satelite (0, 9) ... OK
Municipio Clima_wu (10000, 12) ... OK
Municipio Estacao_cemaden (645, 6) ... OK
Municipio Estacao_wu (177, 5) ... OK
Municipio Historico_alerta (10000, 14) ... OK
Municipio Historico_alerta_chik (93, 14) ... OK
Municipio Historico_alerta_zika (0, 14) ... OK
Municipio Localidade (10, 6) ... OK
Municipio Ovitrampa (0, 9) ... OK

In [15]:
!pg_dump -O -x $PSQL_DB_DEMO > /tmp/alertademo_data.sql