|
|
|
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
import logging
|
|
from pathlib import Path
|
|
import re
|
|
from sqlalchemy import create_engine, text
|
|
|
|
# -------------------- Configuración de conexión --------------------
|
|
hostname_psql = "localhost"
|
|
username_psql = "postgres"
|
|
password_psql = "post3008"
|
|
database_psql = "postgres"
|
|
|
|
# ------------------------------- Logging -------------------------------
|
|
logging.basicConfig(
|
|
filename="huella_telecom_load.log",
|
|
filemode="a",
|
|
format="%(asctime)s %(levelname)s %(message)s",
|
|
datefmt="%Y-%m-%d %H:%M:%S",
|
|
level=logging.INFO,
|
|
)
|
|
|
|
# --------------- Util: regex desde config.txt ---------------
|
|
def cargar_regex_desde_config(ruta: str = "config.txt") -> str:
|
|
"""
|
|
Lee términos desde config.txt:
|
|
- Líneas vacías o que comienzan con '#' se ignoran.
|
|
- Si una línea comienza con 'regex:', el resto se toma como expresión regular.
|
|
- En caso contrario, la línea se trata como literal y se escapa.
|
|
Devuelve un patrón único que OR-ea todos los términos/regex.
|
|
"""
|
|
path = Path(ruta)
|
|
if not path.is_file():
|
|
raise FileNotFoundError(f"No se encontró el archivo de configuración: {ruta}")
|
|
|
|
patrones = []
|
|
with path.open("r", encoding="utf-8") as f:
|
|
for linea in f:
|
|
s = linea.strip()
|
|
if not s or s.startswith("#"):
|
|
continue
|
|
if s.startswith("regex:"):
|
|
patrones.append(s[len("regex:"):]) # usar la regex tal cual
|
|
else:
|
|
patrones.append(re.escape(s)) # tratar como literal
|
|
|
|
if not patrones:
|
|
raise ValueError("config.txt no contiene términos válidos (una palabra por línea).")
|
|
|
|
return "|".join(patrones)
|
|
|
|
REGEX = cargar_regex_desde_config("config.txt")
|
|
|
|
# -------- Helper: construir el UNION ALL (lógica integrada) --------
|
|
def _quote_ident(name: str) -> str:
|
|
return '"' + name.replace('"', '""') + '"'
|
|
|
|
def _quote_literal(val: str) -> str:
|
|
return "'" + val.replace("'", "''") + "'"
|
|
|
|
def build_union_sql(conn) -> str:
|
|
rows = conn.execute(text("""
|
|
SELECT p.proy_name
|
|
FROM giscloud.proyectos p
|
|
WHERE p.proy_name IS NOT NULL
|
|
AND (p.proy_name LIKE 'gpon%' OR p.proy_name LIKE 'lineales%' OR p.proy_name LIKE 'postes%')
|
|
AND to_regclass(format('giscloud.%I', p.proy_name)) IS NOT NULL
|
|
""")).fetchall()
|
|
|
|
parts = []
|
|
for (proy_name,) in rows:
|
|
parts.append(f"""
|
|
SELECT
|
|
{_quote_literal(proy_name)}::text AS proy_name,
|
|
fid, "_created", "_modified",
|
|
comentario::text AS comentario,
|
|
geom::geometry(Point,4326) AS geom
|
|
FROM giscloud.{_quote_ident(proy_name)}
|
|
""".strip())
|
|
|
|
if not parts:
|
|
return "SELECT NULL::text AS proy_name WHERE false"
|
|
|
|
return " UNION ALL ".join(parts)
|
|
|
|
# --------------------- SQL: DDL de la tabla destino ---------------------
|
|
DDL = """
|
|
CREATE TABLE IF NOT EXISTS giscloud.huella_telecom (
|
|
proy_name text NOT NULL,
|
|
fid integer,
|
|
_created timestamp without time zone,
|
|
_modified timestamp without time zone,
|
|
comentario text,
|
|
ageom text, -- WKT
|
|
|
|
-- Desde cm.municipio
|
|
partido text,
|
|
provincia text,
|
|
tli text,
|
|
gpon1 text,
|
|
gpon2 text,
|
|
gpon3 text,
|
|
tendidos text,
|
|
permisotli text,
|
|
permisogpo text,
|
|
permisomun text,
|
|
entidad text,
|
|
objeto text,
|
|
fna text,
|
|
gna text,
|
|
sag text,
|
|
fdc text,
|
|
in1 text,
|
|
shape_star double precision,
|
|
shape_stle double precision,
|
|
|
|
hubname text,
|
|
hubdist double precision,
|
|
geom geometry(Point,4326)
|
|
);
|
|
|
|
/* Asegura la columna si la tabla ya existía */
|
|
ALTER TABLE giscloud.huella_telecom
|
|
ADD COLUMN IF NOT EXISTS upload_date text;
|
|
|
|
-- Índices recomendados
|
|
CREATE INDEX IF NOT EXISTS idx_huella_telecom_geom ON giscloud.huella_telecom USING GIST (geom);
|
|
CREATE INDEX IF NOT EXISTS idx_huella_telecom_proy_fid ON giscloud.huella_telecom (proy_name, fid);
|
|
-- Permisos (mantengo los existentes)
|
|
GRANT SELECT ON TABLE giscloud.huella_telecom TO om_read, jibanez;
|
|
GRANT USAGE ON SCHEMA giscloud TO om_read, jibanez;
|
|
"""
|
|
|
|
# ------------- INSERT (plantilla; inyecta UNION en el JOIN) -------------
|
|
INSERT_SQL_TEMPLATE = """
|
|
WITH eventos_filtrados AS (
|
|
SELECT *
|
|
FROM giscloud.huella_comentario(:p_regex) -- proy_name, fid, _created, _modified, comentario, geom
|
|
)
|
|
INSERT INTO giscloud.huella_telecom (
|
|
proy_name, fid, _created, _modified,
|
|
comentario, ageom,
|
|
partido, provincia,
|
|
tli, gpon1, gpon2, gpon3, tendidos,
|
|
permisotli, permisogpo, permisomun, entidad, objeto,
|
|
fna, gna, sag, fdc, in1, shape_star, shape_stle,
|
|
hubname, hubdist,
|
|
geom, upload_date
|
|
)
|
|
SELECT
|
|
e.proy_name, e.fid, e._created, e._modified,
|
|
e.comentario,
|
|
ST_AsText(e.geom) AS ageom,
|
|
m.nam AS partido,
|
|
m.provincia,
|
|
m.tli, m.gpon1, m.gpon2, m.gpon3, m.tendidos,
|
|
m.permisotli, m.permisogpo, m.permisomun, m.entidad, m.objeto,
|
|
m.fna, m.gna, m.sag, m.fdc, m.in1, m.shape_star, m.shape_stle,
|
|
m.hubname, m.hubdist,
|
|
e.geom AS geom, to_char(CURRENT_DATE, 'MM-DD-YYYY') AS upload_date
|
|
FROM eventos_filtrados e
|
|
LEFT JOIN (
|
|
{UNION_SQL}
|
|
) t
|
|
ON t.fid = e.fid AND t.proy_name = e.proy_name
|
|
LEFT JOIN cm.municipio m
|
|
ON ST_Covers(m.geom, e.geom)
|
|
WHERE e.geom IS NOT NULL;
|
|
"""
|
|
|
|
# ---------------------------------- Runner ----------------------------------
|
|
def main():
|
|
logging.info("Inicio carga giscloud.huella_telecom ")
|
|
engine = create_engine(
|
|
f"postgresql://{username_psql}:{password_psql}@{hostname_psql}:5432/{database_psql}"
|
|
)
|
|
with engine.begin() as conn:
|
|
# 0) Construir el UNION ALL inline
|
|
union_sql = build_union_sql(conn)
|
|
|
|
# 1) Crear tabla + índices + permisos
|
|
conn.execute(text(DDL))
|
|
|
|
# 2) Limpiar contenido actual
|
|
conn.execute(text("DELETE FROM giscloud.huella_telecom;"))
|
|
|
|
# 3) Insertar datos (inyectando el UNION ALL)
|
|
insert_sql = INSERT_SQL_TEMPLATE.format(UNION_SQL=union_sql)
|
|
conn.execute(text(insert_sql), {"p_regex": REGEX})
|
|
# --------- Bloque de logueo agregado (fuera del with) ----------
|
|
def log_final_en_postgres():
|
|
cnx = engine.raw_connection()
|
|
cursor = cnx.cursor()
|
|
cursor.execute("""
|
|
INSERT INTO z_procesos_python.scripts_ejecutados (nombre_script, ultima_ejecucion)
|
|
VALUES ('HuellaTelecom', CURRENT_TIMESTAMP AT TIME ZONE 'America/Argentina/Buenos_Aires');
|
|
""")
|
|
cnx.commit()
|
|
cursor.close()
|
|
cnx.close()
|
|
log_final_en_postgres()
|
|
logging.info("Carga finalizada OK")
|
|
|
|
if __name__ == "__main__":
|
|
try:
|
|
main()
|
|
except Exception as e:
|
|
logging.exception(f"Fallo en carga: {e}")
|
|
raise
|
