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)) """