In [1]:

    

from sqlalchemy import create_engine
from AlertaDengue import settings

import pandas as pd


    

In [2]:

    

db_conf = {
    'u': settings.PSQL_USER,
    'h': settings.PSQL_HOST,
    'p': settings.PSQL_PASSWORD,
    'n': settings.PSQL_DB,
}


    

In [3]:

    

dsn = 'postgresql://%(u)s:%(p)s@%(h)s/%(n)s' % db_conf
engine = create_engine(dsn)


    

In [4]:

    

SQL = ''
sql = ''
df = pd.read_csv('../../alerta_dengue_files/regional-saude.csv', na_values='NA')
for i, row in df.iterrows():
    sql += '''
    UPDATE "Dengue_global".regional_saude 
    SET varcli='%s', tcrit=%s, ucrit=%s
    WHERE municipio_geocodigo=%s;
    ''' % (row['varcli'], row['tcrit'], row['ucrit'], row['municipio_geocodigo'])
    
update_sql = sql.replace('nan', 'NULL')
print(update_sql[:1000], '...')


    

    




    UPDATE "Dengue_global".regional_saude 
    SET varcli='temp_min', tcrit=22.0, ucrit=NULL
    WHERE municipio_geocodigo=3300233;
    
    UPDATE "Dengue_global".regional_saude 
    SET varcli='temp_min', tcrit=22.0, ucrit=NULL
    WHERE municipio_geocodigo=3300159;
    
    UPDATE "Dengue_global".regional_saude 
    SET varcli='temp_min', tcrit=22.0, ucrit=NULL
    WHERE municipio_geocodigo=3300258;
    
    UPDATE "Dengue_global".regional_saude 
    SET varcli='temp_min', tcrit=22.0, ucrit=NULL
    WHERE municipio_geocodigo=3300407;
    
    UPDATE "Dengue_global".regional_saude 
    SET varcli='temp_min', tcrit=22.0, ucrit=NULL
    WHERE municipio_geocodigo=3300456;
    
    UPDATE "Dengue_global".regional_saude 
    SET varcli='temp_min', tcrit=22.0, ucrit=NULL
    WHERE municipio_geocodigo=3300506;
    
    UPDATE "Dengue_global".regional_saude 
    SET varcli='temp_min', tcrit=22.0, ucrit=NULL
    WHERE municipio_geocodigo=3300704;
    
    UPDATE "Dengue_global".regional_saud ...

In [5]:

    

with engine.connect() as conn:
    # remove duplicated register
    sql = 'DELETE FROM "Dengue_global".regional_saude WHERE id=1746;'
    conn.execute(sql)
    print('delete done.')
    
    SQL += sql
    
    # alter table
    try:
        sql = '''
        ALTER TABLE "Dengue_global".regional_saude 
        ADD CONSTRAINT regional_saude_uq_municipio_geocodigo UNIQUE (municipio_geocodigo)
        '''
        
        SQL += sql
        
        conn.execute(sql)
        print('unique added done.')
    except Exception as e:
        print(e)
        
    try:
        sql = '''
        ALTER TABLE "Dengue_global".regional_saude 
        ADD COLUMN varcli VARCHAR(10) NULL;
        '''
        
        SQL += sql
        
        conn.execute(sql)
        print('varcli added.')
    except Exception as e:
        print(e)
        
    try:
        sql = '''
        ALTER TABLE "Dengue_global".regional_saude 
        ADD COLUMN tcrit DOUBLE PRECISION NULL;
        '''
        
        SQL += sql
        
        conn.execute(sql)
        print('tcrit added.')
    except Exception as e:
        print(e)
        
    try:
        sql = '''
        ALTER TABLE "Dengue_global".regional_saude 
        ADD COLUMN ucrit DOUBLE PRECISION NULL;
        '''
        
        SQL += sql
        
        conn.execute(sql)
        print('ucrit added.')
    except Exception as e:
        print(e)
        
    try:
        SQL += update_sql
        
        conn.execute(update_sql)
        print('update done')
    except Exception as e:
        print(e)


    

    




delete done.
unique added done.
varcli added.
tcrit added.
ucrit added.
update done

In [6]:

    

with open('/tmp/dump.sql', 'w') as f:
    f.write(SQL)