import psycopg2, os

fields_dict = {}

with open('../docfields.txt') as file:

    lines = file.readlines()

    for line in lines:

        names = line.split(' -')

        if len(names) > 1:
            names = names[:len(names) - 1]

            names = ''.join(names).split(', ')

        for name in names:

            fields_dict[name] = names[0]

con = psycopg2.connect(host='localhost', database='datasus', user='postgres', password='')

cur = con.cursor()

try:

    cur.execute('select * from "datasus_table"')

except Exception as e:

    print('You need to create "datasus_table" in database before executing this script...')

    print('Just run script.sql file...')

    exit(1)

files = os.listdir('../data_files/')

for file in files:

    name = file.split('.')[0]

    sql = 'select * from "' + name + '"'

    cur.execute(sql)
    recset = cur.fetchall()

    fields = cur.description
    size = len(cur.description)

    for rec in recset:

        insert_qry = 'insert into "datasus_table" ('

        for i in range(0, size):

            insert_qry += '"' + fields_dict[fields[i].name] + '"' + (') values (' if i == size - 1 else ',')

        for i in range(0, len(rec)):

            insert_qry += ('\'' + (str(rec[i]) + '\'') if rec[i] else 'null') + (')' if i == len(rec) - 1 else ',')

        print('Consulta: ' + insert_qry)

        cur_insert = con.cursor()

        cur_insert.execute(insert_qry)

        con.commit()

        cur_insert.close()

cur.close()
con.close()