Cleans census tract id's which have accidentally been converted to floats from strings.

clean-ids.py

import psycopg2

database = ''
host = 'localhost'
port = 5432
user = ''
password = ''

table = ''

conn = psycopg2.connect(dbname=database,
                        host=host,
                        port=port,
                        user=user,
                        password=password)

conn.autocommit = True
census_ct_id_cur = conn.cursor()
census_ct_id_cur.execute("SELECT geo_id FROM {table};".format(table=table))
for i in census_ct_id_cur.fetchall():
    old_ct = i[0]
    old_ct_split = i[0].split('.')
    if len(old_ct_split) > 1:
        old_decimals = old_ct_split[1]
        if len(old_decimals) == 1:
            new_decimals = "{}0".format(old_decimals)
        elif len(old_decimals) == 2:
            new_decimals = old_decimals
        else:
            print("Edge case, more than two decimal places...?")

    elif len(old_ct_split) == 1:
        new_decimals = '00'
    else:
        print("Edge case, more than one period in string...")

    old_whole_numbers = old_ct_split[0]
    new_whole_numbers = (
        '0' * (7 - int(len(old_whole_numbers)))) + old_whole_numbers

    new_ct_id = new_whole_numbers + '.' + new_decimals

    if new_ct_id != old_ct:
        update_query = """
        UPDATE {table}
        SET geo_id = '{new_ct_id}'
        WHERE geo_id = '{old_ct}'
        """.format(
            table=table,
            new_ct_id=new_ct_id,
            old_ct=old_ct
        )
        print(update_query)
        ct_update_cur = conn.cursor()
        ct_update_cur.execute(update_query)
        print('update query executed')