Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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')
Add Comment
Please, Sign In to add comment