def update_owner_name(owner_id):
"""
Updates the name of the specified owner
"""
try:
conn = create_connection()
except Exception as e:
return jsonify({'Error' : str(e)}),500
try:
payload = request.get_json()
new_name = payload.get('newName','')
if(not new_name):
return jsonify({'Error' : 'No new name provided'}),400
cur = conn.cursor()
cur.execute("SELECT * FROM owner WHERE id = (%s)",(owner_id,))
row = cur.fetchone()
if(not row):
return jsonify({'Error' : 'No owner for given id'}),404
cur.execute("UPDATE owner SET name = (%s) WHERE id = (%s)", (new_name,owner_id))
cur.close()
conn.commit()
conn.close()
return jsonify({'Status' : 'Owner name succesfully updated to ' + new_name})
except Exception as e:
conn.close()
return jsonify({'Error' : str(e)}),500
def get_all_properties():
"""
Returns details for all properties
"""
try:
conn = create_connection()
except Exception as e:
return jsonify({'Error' : str(e)}),500
try:
cur = conn.cursor()
cur.execute("SELECT * FROM property JOIN owner on property.ownerID = owner.id")
result = cur.fetchall()
property_list = []
for row in result:
prop = {
'propID' : row[0],
'geoID' : row[1],
'legalDescription' : row[2],
'situsAddress' :row[3],
'ownerName' : row[6]
}
property_list.append(prop)
cur.close()
conn.close()
return jsonify({
'propertyList' : property_list
}),200
except Exception as e:
return jsonify({'Error' : str(e)}),500
def get_property(property_id):
"""
Returns details for the specified property
"""
try:
conn = create_connection()
except Exception as e:
return jsonify({'Error' : str(e)}),500
try:
cur = conn.cursor()
cur.execute("SELECT * FROM property JOIN owner on\
property.ownerID = owner.id WHERE property.id = (%s)",(property_id,))
result = cur.fetchone()
if (not result):
return jsonify({'Error' : 'Invalid property id'}),404
prop = {
'propID' : result[0],
'geoID' : result[1],
'legalDescription' : result[2],
'situsAddress' :result[3],
'ownerName' : result[6]
}
cur.close()
conn.close()
return jsonify({
'propertyDetails' : prop
}),200
except Exception as e:
return jsonify({'Error' : str(e)}),500
def update_property_address(property_id):
"""
Update's the situs address of the specified property
"""
try:
conn = create_connection()
except Exception as e:
return jsonify({'Error' : str(e)}),500
try:
payload = request.get_json()
new_address = payload.get('newAddress','')
if(not new_address):
return jsonify({'Error' : 'No new address provided'}),400
cur = conn.cursor()
cur.execute("SELECT * FROM property WHERE id = (%s)",(property_id,))
row = cur.fetchone()
if(not row):
return jsonify({'Error' : 'No property for given id'})
#Just wanted to note that it's possible to construct a query
#in a more dynamic way, so you can update a variable number of fields .
#It wasn't necessary in this case so I went with the simpler way
cur.execute("UPDATE property SET situsAddress = (%s) WHERE id = (%s)", (new_address,property_id))
cur.close()
conn.commit()
conn.close()
return jsonify({
'Status' : 'Property ' + property_id + '\'s address succesfully updated'
}),200
except Exception as e:
conn.close()
return jsonify({'Error' : str(e)}),500
def setUpClass(cls):
"""Create the testing database
"""
cls.crawler = MlsCrawler(SF_70118_70125_70113_70130_70115)
cls.soup = cls.crawler.single_line_view()
conn = create_connection('config.cfg')
create_database(conn, schema='testleadmachine')
create_table(cls.crawler.conn, 'TESTLEADS', testcolumns)
def setUpClass(cls):
"""Create the testing database
"""
cls.crawler = MlsCrawler(MF_7TH_9TH_MARIGNY_BYWATER)
cls.soup = cls.crawler.single_line_view()
conn = create_connection('config.cfg')
create_database(conn, schema='testleadmachine')
create_table(cls.crawler.conn, 'TESTLEADS', testcolumns)
def delete_property(property_id):
"""
Deletes the specified property
"""
try:
conn = create_connection()
except Exception as e:
return jsonify({'Error' : str(e)}),500
try:
cur = conn.cursor()
cur.execute("SELECT * FROM property WHERE id = (%s)",(property_id,))
row = cur.fetchone()
if(not row):
return jsonify({'Error' : 'No property for given id'})
cur.execute("DELETE FROM property WHERE id = (%s)", (property_id,))
cur.close()
conn.commit()
conn.close()
return jsonify({'Status' : 'property ' + property_id + ' deleted'}),202
except Exception as e:
conn.close()
return jsonify({'Error' : str(e)}),500
with open('dataFile.csv') as csvfile:
readCSV = csv.reader(csvfile, delimiter=',')
columns = next(readCSV)
property_list = []
owners = set()
#Just going to assume that there would not be different owners with the
#same name for the sake of this project
for row in readCSV:
prop = []
for item in row:
prop.append(item.strip())
owners.add(row[3])
property_list.append(prop)
conn = create_connection()
"""
After writing code to read from the file and creating the tables in the db
Inserted owners into the owner table
"""
try:
cur = conn.cursor()
for owner in owners:
cur.execute("INSERT INTO owner (name) VALUES (%s);", (owner, ))
cur.close()
#conn.commit()
conn.close()
except Exception as e:
conn.close()
print(str(e))
"""