def import_code_violation():
csv_file = 'code_violation_cleaned.csv'
#816 is churchill
currentmuni = 816
codesource = 18
defaultch = 1
# db_conn = get_db_conn()
# cursor = db_conn.cursor()
# Using a hard-coded role description
sql_command = """
INSERT INTO public.codeelement(
elementid, codesource_sourceid, ordchapterno, ordchaptertitle,
ordsecnum, ordsectitle, ordsubsecnum, ordsubsectitle, ordtechnicaltext,
ordhumanfriendlytext, isactive, resourceurl, datecreated, guideentryid)
VALUES (DEFAULT, ?, ?, ?,
?, ?, ?, ?, ?,
?, ?, ?, ?, ?);
"""
# Read CSV file with original Access Data
with open(csv_file, 'r') as infile:
reader = csv_utils.UnicodeReader(infile, delimiter=CSV_DELIMITER)
# Ignore header
reader.next()
for row in reader:
if row[2] == currentmuni and row[3] != 'None' and row[4] != 'None':
rowdict = {}
rowdict['elementid'] = int(row[1]) + 1000
rowdict['codesource_sourceid'] = codesource
rawcodenum = row[3]
exp = re.compile(r'(\d+)[\.-](\d+[.-]?\d*)\s(.*)')
m = re.search(exp, rawcodenum)
print m.group(1)
print m.group(2)
print m.group(3)
def import_codeenfevent():
csv_file = 'codeEnfEvent.csv'
db_conn = get_db_conn()
cursor = db_conn.cursor()
# Using hard-coded event type ID: 18, for code enforcement letter type
sql_command = """
INSERT INTO codeenfevent
(eventID, eventDate, eventDescription, letterText,
codeOfficer_officerID, codeEnfCase_caseID, EventTyp_codeEnfEventTypeID)
VALUES (%(eventID)s, %(eventDate)s, %(eventDescription)s,
%(letterText)s, %(codeOfficer_officerID)s, %(codeEnfCase_caseID)s, 18)
"""
# Read CSV file with original Access Data
with open(csv_file, 'r') as infile:
reader = csv_utils.UnicodeReader(infile, delimiter=CSV_DELIMITER)
# Get header
header = reader.next()
# Sequence of eventid
get_event_id = itertools.count(start=1)
for row in reader:
# Build a record dict from row and header
record = dict(zip(header, row))
# Assign eventid
record['eventID'] = get_event_id.next()
# Insert the data to the Postgres table
cursor.execute(sql_command, record)
def import_propertyagent():
table_name = 'propertyAgent'
csv_file = 'propertyAgent_test.csv'
db_conn = get_db_conn()
cursor = db_conn.cursor()
# Using hard-coded event type ID: 18, for code enforcement letter type
sql_command = """
INSERT INTO public.person(
personid, persontype, muni_municode, fname, lname, jobtitle,
phonecell, phonehome, phonework, email, address_street, address_city,
address_state, address_zip, notes, lastupdated, expirydate, isactive,
isunder18, humanverifiedby)
VALUES (%(personid)s, CAST('LegacyAgent' AS persontype), %(municode)s, %(fname)s, %(lname)s, NULL,
NULL, NULL, %(phonework)s, %(email)s, %(address_street)s, %(address_city)s,
%(address_state)s, %(address_zip)s, %(notes)s, now(), NULL, TRUE,
FALSE, NULL);
"""
# Read CSV file with original Access Data
with open(csv_file, 'r') as infile:
reader = csv_utils.UnicodeReader(infile, delimiter=CSV_DELIMITER)
# Get header
header = reader.next()
# Sequence of eventid
get_personid = itertools.count(start=200)
for row in reader:
# Build a record dict from row and header
record = dict(zip(header, row))
if record['fname'] == None:
record['fname'] == 'none'
if record['lname'] == None:
record['lname'] == 'none'
record['personid'] = get_personid.next()
record['notes'] = 'person created from legacy table tblAgent with origin agentid of %s connected to property id %s' \
% (record['agentid'], record['propertyid'])
cursor.execute(sql_command, record)
try:
pgprop = fetch_pgpropertyid(record['parcelid'])
except Exception:
print('skipping person linking for %s' % (record['fname']))
continue
connect_person_to_property(pgprop, record['personid'])
def import_codeofficer():
csv_file = 'codeOfficer.csv'
db_conn = get_db_conn()
cursor = db_conn.cursor()
# Using a hard-coded role description
sql_command = """
INSERT INTO codeofficer
(officerid, firstname, lastname, roledescription)
VALUES (%s, %s, %s, 'Code enforcement officer');
"""
# Read CSV file with original Access Data
with open(csv_file, 'r') as infile:
reader = csv_utils.UnicodeReader(infile, delimiter=CSV_DELIMITER)
# Ignore header
reader.next()
for row in reader:
# Get code officer ID
officer_id = row[0]
# Split officer's name into first and last name
first_name, last_name = row[1].split(' ')
# Insert the data to the Postgres table
cursor.execute(sql_command, (officer_id, first_name, last_name))
def store_owner_as_property():
db_conn = get_db_conn()
cursor = db_ conn.cursor()
# Using hard-coded event type ID: 18, for code enforcement letter type
sql_command = """
INSERT INTO public.person(
personid, persontype, muni_municode, fname, lname, jobtitle,
phonecell, phonehome, phonework, email, address_street, address_city,
address_state, address_zip, notes, lastupdated, expirydate, isactive,
isunder18, "humanVerifiedby")
VALUES (DEFAULT, CAST( 'ownercntylookup' AS persontype), %s, ?, ?, ?,
?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?,
?, ?);
INSERT INTO codeenfevent
(eventID, eventDate, eventDescription, letterText,
codeOfficer_officerID, codeEnfCase_caseID, EventTyp_codeEnfEventTypeID)
VALUES (%(eventID)s, %(eventDate)s, %(eventDescription)s,
%(letterText)s, %(codeOfficer_officerID)s, %(codeEnfCase_caseID)s, 18)
"""
# Read CSV file with original Access Data
with open(csv_file, 'r') as infile:
reader = csv_utils.UnicodeReader(infile, delimiter=CSV_DELIMITER)
# Get header
header = reader.next()
# Sequence of eventid
get_event_id = itertools.count(start=1)
for row in reader:
# Build a record dict from row and header
record = dict(zip(header, row))
# Assign eventid
record['eventID'] = get_event_id.next()
# Insert the data to the Postgres table
cursor.execute(sql_command, record)
def import_code_elements():
print 'importing code violations'
csv_file = 'code_violation_cleaned.csv'
# csv_file = 'testviolations.csv'
#816 is churchill
currentmuni = 953
codesource = 26
defaultch = 0
db_conn = get_db_conn()
cursor = db_conn.cursor()
# Using a hard-coded role description
sql_command = """
INSERT INTO public.codeelement(
tb
elementid, codesource_sourceid, ordchapterno, ordchaptertitle,
ordsecnum, ordsectitle, ordsubsecnum, ordsubsectitle, ordtechnicaltext,
ordhumanfriendlytext, isactive, resourceurl, datecreated, guideentryid, notes, legacyid)
VALUES (DEFAULT, %(codesource_sourceid)s, %(ordchapterno)s, NULL,
%(ordsecnum)s, NULL, %(ordsubsecnum)s, %(ordsubsectitle)s, %(ordtechnicaltext)s,
NULL, CAST (%(isactive)s AS boolean), NULL, now(), NULL, %(notes)s, CAST(%(legacyid)s AS integer));
"""
rowdict = {}
# Read CSV file with original Access Data
with open(csv_file, 'r') as infile:
reader = csv_utils.UnicodeReader(infile, delimiter=CSV_DELIMITER)
# Ignore header
reader.next()
for row in reader:
muni = int(row[1])
if muni == currentmuni:
print '****************'
print row[0]
# rowdict['elementid'] = int(row[0]) + 1000
rowdict['codesource_sourceid'] = codesource
rowdict['ordchapterno'] = defaultch
rawcodenum = row[2]
exp = re.compile(r'(\d+)[\.-](\d+[\.-]?\w*[\.]?)\s+(.*)')
# this exp is specific for separating out churchill specific ordinances which only use a - delimiter
# exp = re.compile(r'(\d+).(\d+[\.-]?\w*[\.]?)\s+(.*)')
m = re.search(exp,rawcodenum)
if m:
rowdict['ordsecnum'] = m.group(1)
print rowdict['ordsecnum']
rowdict['ordsubsecnum'] = m.group(2)
print rowdict['ordsubsecnum']
rowdict['ordsubsectitle'] = m.group(3)
print rowdict['ordsubsectitle']
rowdict['ordtechnicaltext'] = row[3]
rowdict['isactive'] = row[4].upper()
rowdict['notes'] = 'pulled from legacy system on 16JUL18'
rowdict['legacyid'] = row[0]
# Insert the data to the Postgres table
cursor.execute(sql_command, rowdict)
db_conn.commit()
else:
logerrorviolation(row)
print "no match"
rowdict['ordsecnum'] = 'NULL'
rowdict['ordsubsecnum'] = 'NULL'
rowdict['ordsubsectitle'] = 'NULL'
