getaddress.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Sep 30 09:46:16 2025

@author: perkins
"""

import psycopg2
import pandas as pd

# ---- DB CONNECTION ----
conn = psycopg2.connect(
    dbname="refm_db",
    user="paulo_issler",
    password="issler86",
    host="192.168.0.125",
    port="5432"
)

# ---- SQL QUERY ----
# Pull relevant fields from both tables
query = """
WITH t2024 AS (
    SELECT dpid, fipscode, propertyfullstreetaddress, '2024'::text as source
    FROM _bk_assessor.asmt_ct
),
t2025 AS (
    SELECT dpid, fipscode, propertyfullstreetaddress, '2025'::text as source
    FROM _bk_assessor_2025.asmt_ct
),
paired AS (
    SELECT 
        COALESCE(t2024.dpid, t2025.dpid) AS dpid,
        COALESCE(t2024.fipscode, t2025.fipscode) AS fipscode,
        t2024.propertyfullstreetaddress AS addr_2024,
        t2025.propertyfullstreetaddress AS addr_2025
    FROM t2024
    FULL OUTER JOIN t2025 USING (dpid, fipscode)
)
SELECT 
    p.dpid,
    p.fipscode,
    t2024.source,
    t2024.propertyfullstreetaddress AS address
FROM paired p
JOIN t2024 ON p.dpid = t2024.dpid AND p.fipscode = t2024.fipscode
WHERE p.addr_2024 IS DISTINCT FROM p.addr_2025

UNION ALL

SELECT 
    p.dpid,
    p.fipscode,
    t2025.source,
    t2025.propertyfullstreetaddress AS address
FROM paired p
JOIN t2025 ON p.dpid = t2025.dpid AND p.fipscode = t2025.fipscode
WHERE p.addr_2024 IS DISTINCT FROM p.addr_2025
ORDER BY fipscode, dpid, source;
"""

# ---- LOAD INTO PANDAS ----
df = pd.read_sql(query, conn)

# ---- WRITE TO CSV ----
df.to_csv("address_differences.csv", index=False)

print("Done! Output written to address_differences.csv")

conn.close()