cep_centralized_script.py

import psycopg2
from tqdm import tqdm

db_config = {
    'dbname': 'levante_web',
    # 'user': 'seu_usuario',
    # 'password': 'sua_senha',
    'host': 'localhost',
    'port': '5432'
}

conn = psycopg2.connect(**db_config)
cur = conn.cursor()

create_table_query = """
CREATE TABLE IF NOT EXISTS public.ceps (
    zip_code VARCHAR PRIMARY KEY,
    street VARCHAR,
    complement VARCHAR,
    neighborhood VARCHAR,
    city VARCHAR,
    state VARCHAR,
    latitude DECIMAL,
    longitude DECIMAL
);
"""

cur.execute(create_table_query)
conn.commit()

select_query = """
SELECT 
    qe.cep AS zip_code, 
    TRIM(BOTH ' ' FROM COALESCE(qe.tipo_logradouro || ' ', '') || qe.logradouro) AS street, 
    TRIM(BOTH ' ' FROM COALESCE(qe.local || ' ', '') || qe.complemento) AS complement,
    qb.bairro AS neighborhood, 
    qc.cidade AS city, 
    qc.uf AS state, 
    NULLIF(qg.latitude, '-')::DECIMAL AS latitude, 
    NULLIF(qg.longitude, '-')::DECIMAL AS longitude
FROM public.qualocep_endereco qe
LEFT JOIN public.qualocep_bairro qb ON qe.id_bairro = qb.id_bairro
LEFT JOIN public.qualocep_cidade qc ON qe.id_cidade = qc.id_cidade
LEFT JOIN public.qualocep_geo qg ON qe.cep = qg.cep;
"""

cur.execute(select_query)
rows = cur.fetchall()

insert_query = """
INSERT INTO public.ceps (zip_code, street, complement, neighborhood, city, state, latitude, longitude)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (zip_code) DO NOTHING;
"""

for row in tqdm(rows, desc="Inserting data"):
    cur.execute(insert_query, row)
conn.commit()

cur.close()
conn.close()

print("Data consolidated and inserted into the 'ceps' table successfully.")