normaliseFloorspaceRecords.py

import psycopg2
import psycopg2.extras
import csv
import getpass

username = raw_input('username:')
pswd = getpass.getpass(prompt='Password:')
dbConnect = psycopg2.connect("dbname='ldd' user='" + username + "' host='localhost' password='" + pswd + "'")
cursor = dbConnect.cursor(cursor_factory=psycopg2.extras.DictCursor)

def sendSQLgetVal(sql, cursor):
  cursor.execute(sql)
  ans = cursor.fetchone()
  return ans

def sendSQLgetList(sql, cursor):
  cursor.execute(sql)
  ans = cursor.fetchall()
  return ans


def setupCodeDomain(cursor, con):
  sql = "SELECT count(domain_name) FROM app_ldd.ld_ref_code_domains WHERE domain_name='CA_PLANNING_USE_CLASS';"
  cdom = sendSQLgetVal(sql, cursor)
  if cdom[0] == 0:
    sql = "INSERT INTO app_ldd.ld_ref_code_domains (domain_name, domain_title, domain_type) VALUES ('CA_PLANNING_USE_CLASS', 'What the property may lawfully be used for', 'USER');"
    cursor.execute(sql)
    con.commit()
  else:
    print "Not entering domains because response = " + str(cdom)

def setupTables(cursor, con):
  tables =  [
              { "name": "app_ldd.ca_exist_non_res_floorspace",
                "fields": "id SERIAL PRIMARY KEY, permission_id BIGINT, planning_use_class_rc VARCHAR(30), floorspace INTEGER" },
              { "name": "app_ldd.ca_exist_non_res_accom",
                "fields": "id SERIAL PRIMARY KEY, permission_id BIGINT, planning_use_class_rc VARCHAR(30), accomtype VARCHAR(20), accom INTEGER" },
              { "name": "app_ldd.ca_prop_non_res_floorspace",
                "fields": "id SERIAL PRIMARY KEY, permission_id BIGINT, planning_use_class_rc VARCHAR(30), floorspace INTEGER, superseded_permission_id BIGINT, superseded_date DATE, completed_date DATE" },
              { "name": "app_ldd.ca_prop_non_res_accom",
                "fields": "id SERIAL PRIMARY KEY, permission_id BIGINT, planning_use_class_rc VARCHAR(30), accomtype VARCHAR(20), accom INTEGER, superseded_permission_id BIGINT, superseded_date DATE, completed_date DATE" }
            ]
  for t in tables:
    sql = "DROP TABLE IF EXISTS " + t["name"] + ";"
    cursor.execute(sql)
    sql = "CREATE TABLE " + t["name"] + "(" + t["fields"] + ");"
    cursor.execute(sql)
    con.commit()

def importCodesToDb(cursor, con, codefile):
  with open(codefile) as csvfile:
    reader = csv.DictReader(csvfile)
    seq = 10;
    for r in reader:
      sql = "SELECT count(code) FROM app_ldd.ld_ref_codes WHERE domain_name='CA_PLANNING_USE_CLASS' AND code='" + r['code'] + "';"
      c = sendSQLgetVal(sql, cursor)
      if c[0] == 0:
        sql = "INSERT INTO app_ldd.ld_ref_codes (domain_name, code, descr, obsolete_yn, sequence) VALUES ('CA_PLANNING_USE_CLASS', '" + r['code'] + "', '" + r['descr'] + "', 'N', " + str(seq) + ");"
        cursor.execute(sql)
        con.commit()
      seq += 10

def transferAllDataToNewTables(cursor, con, codefile):
  with open(codefile) as csvfile:
    reader = csv.DictReader(csvfile)
    for c in reader:
      if c['type'] == "floorspace" and c['exist_field'] != "":
        select = "SELECT permission_id, '" + c['code'] + "' as planclass, " + c['exist_field'] + " FROM app_ldd.ld_permissions WHERE " + c['exist_field'] + " IS NOT NULL" 
        sql = "INSERT INTO app_ldd.ca_exist_non_res_floorspace (permission_id, planning_use_class_rc, floorspace) " + select
        cursor.execute(sql)
        select = "SELECT permission_id, '" + c['code'] + "' as planclass, " + c['prop_field'] + ", " + c['superid'] + ", " 
        select += c['superdate'] + ", " + c['compdate'] + " FROM app_ldd.ld_permissions WHERE " + c['prop_field'] + " IS NOT NULL" 
        sql = "INSERT INTO app_ldd.ca_prop_non_res_floorspace (permission_id, planning_use_class_rc, floorspace, superseded_permission_id, superseded_date, completed_date) " + select
        cursor.execute(sql)
        con.commit()
      if c['type'] == "accom" and c['exist_field'] != "":
        select = "SELECT permission_id, '" + c['code'] + "' as planclass, '" + c['subtype'] + "' AS atype, " + c['exist_field'] + " FROM app_ldd.ld_permissions WHERE " + c['exist_field'] + " IS NOT NULL" 
        sql = "INSERT INTO app_ldd.ca_exist_non_res_accom (permission_id, planning_use_class_rc, accomtype, accom) " + select
        cursor.execute(sql)
        select = "SELECT permission_id, '" + c['code'] + "' as planclass, '" + c['subtype'] + "' AS atype, " + c['prop_field'] + ", " + c['superid'] + ", " 
        select += c['superdate'] + ", " + c['compdate'] + " FROM app_ldd.ld_permissions WHERE " + c['prop_field'] + " IS NOT NULL" 
        sql = "INSERT INTO app_ldd.ca_prop_non_res_accom (permission_id, planning_use_class_rc, accomtype, accom, superseded_permission_id, superseded_date, completed_date) " + select
        cursor.execute(sql)
        con.commit()
    
setupCodeDomain(cursor, dbConnect)
importCodesToDb(cursor, dbConnect, "codes.csv")
setupTables(cursor, dbConnect)
transferAllDataToNewTables(cursor, dbConnect, "codes.csv")
cursor.close()
dbConnect.close()