|
#!/usr/bin/env python3
|
|
"""
|
|
Backfill de sys_created_by en sn.case
|
|
|
|
- Busca registros con sys_created_by IS NULL
|
|
- Consulta ServiceNow por number
|
|
- Actualiza PostgreSQL solo si SN devuelve valor
|
|
"""
|
|
|
|
import requests
|
|
import psycopg2
|
|
import logging
|
|
import urllib3
|
|
|
|
# ================= CONFIG =================
|
|
|
|
# ServiceNow
|
|
SN_BASE_URL = "https://atowerarprod.service-now.com/api/now/table/sn_customerservice_case"
|
|
SN_USER = "plataformas.atc"
|
|
SN_PASS = "fiberatc1"
|
|
|
|
# PostgreSQL
|
|
PG_CONN = {
|
|
"host": "localhost",
|
|
"database": "postgres",
|
|
"user": "postgres",
|
|
"password": "post3008"
|
|
}
|
|
|
|
# ================= LOGGING =================
|
|
logging.basicConfig(
|
|
level=logging.INFO,
|
|
format="%(asctime)s %(levelname)s %(message)s"
|
|
)
|
|
|
|
# Desactivar warning SSL (mismo criterio que loaders)
|
|
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
|
|
|
|
# ================= FUNCTIONS =================
|
|
|
|
def get_cases_with_null_sys_created_by(conn):
|
|
sql = """
|
|
SELECT number
|
|
FROM sn."case"
|
|
WHERE sys_created_by IS NULL
|
|
AND number IS NOT NULL;
|
|
"""
|
|
with conn.cursor() as cur:
|
|
cur.execute(sql)
|
|
return [r[0] for r in cur.fetchall()]
|
|
|
|
|
|
def fetch_sys_created_by_from_sn(number):
|
|
params = {
|
|
"sysparm_query": f"number={number}",
|
|
"sysparm_fields": "number,sys_created_by",
|
|
"sysparm_display_value": "true",
|
|
"sysparm_exclude_reference_link": "true"
|
|
}
|
|
|
|
resp = requests.get(
|
|
SN_BASE_URL,
|
|
auth=(SN_USER, SN_PASS),
|
|
params=params,
|
|
verify=False,
|
|
timeout=30
|
|
)
|
|
|
|
resp.raise_for_status()
|
|
data = resp.json().get("result", [])
|
|
|
|
if not data:
|
|
return None
|
|
|
|
return data[0].get("sys_created_by")
|
|
|
|
|
|
def update_sys_created_by(conn, number, sys_created_by):
|
|
sql = """
|
|
UPDATE sn."case"
|
|
SET sys_created_by = %s
|
|
WHERE number = %s
|
|
AND sys_created_by IS NULL;
|
|
"""
|
|
with conn.cursor() as cur:
|
|
cur.execute(sql, (sys_created_by, number))
|
|
|
|
|
|
# ================= MAIN =================
|
|
|
|
def main():
|
|
conn = psycopg2.connect(**PG_CONN)
|
|
conn.autocommit = False
|
|
|
|
try:
|
|
numbers = get_cases_with_null_sys_created_by(conn)
|
|
logging.info(f"Casos a reparar: {len(numbers)}")
|
|
|
|
for number in numbers:
|
|
try:
|
|
sys_created_by = fetch_sys_created_by_from_sn(number)
|
|
|
|
if sys_created_by:
|
|
update_sys_created_by(conn, number, sys_created_by)
|
|
conn.commit()
|
|
logging.info(f"{number} -> sys_created_by actualizado ({sys_created_by})")
|
|
else:
|
|
conn.rollback()
|
|
logging.warning(f"{number} -> SN no devolvió sys_created_by")
|
|
|
|
except Exception as e:
|
|
conn.rollback()
|
|
logging.error(f"{number} -> error: {e}")
|
|
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|
