def get_region(search_term): session = dbconnect() try: region_instance = session.query(Region).filter(Region.region_name == search_term).one() return jsonify(region_instance.id), 200 except: return "Region doesn't exist in database", 400
def getStatus(boatName):
session = dbconnect()
status = session.query(BoatLog).filter(
BoatLog.boatName == boatName).order_by(BoatLog.time.desc()).first()
status = status.__dict__
status.pop("_sa_instance_state")
return status
def getTrack(boatName):
session = dbconnect()
logs = session.query(BoatLog).filter(BoatLog.boatName == boatName).all()
print(logs)
output = []
for log in logs:
output.append([log.lat, log.lon])
return output
def get_species(search_term):
session = dbconnect()
return_list = []
print(search_term)
for row in session.query(Species).filter(
Species.scientific_name == search_term).all():
row_dict = row.__dict__
row_dict.pop("_sa_instance_state")
return_list.append(row_dict)
return jsonify(return_list)
def add_park(): session = dbconnect() request_dict = request.get_json() try: # Check if state (parent of park) is existing so api doesn't add park without state (which will only be added with its parent Region) state_instance = session.query(State).filter(State.id == request_dict["state_id"]).one() except exc.IntegrityError: return "State does not exist", 400 # Add the data to the queue q.enqueue(add_park_from_queue, request_dict) return "OK", 200
def cool():
session = dbconnect()
specimens = session.query(Specimen).all()
# Serialisation starts here
row_list = []
for specimen in specimens:
row_dict = {}
row_dict["name"] = specimen.name
row_dict["birth_date_time"] = specimen.birth_date_time
row_list.append(row_dict)
return jsonify(row_list)
def getOrAddUser(user_input):
session = dbconnect()
try:
user = session.query(User).filter(
User.email == user_input["email"]).one()
except:
user = User()
user.name = user_input["name"]
user.email = user_input["email"]
session.close()
return user
def add_region():
session = dbconnect()
request_dict = request.get_json()
try:
region_instance = Region()
region_instance.region_name = request_dict["Region"]
session.add(region_instance)
session.commit()
return jsonify(region_instance.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def add_genus():
session = dbconnect()
request_dict = request.get_json()
try:
genus_instance = Genus()
genus_instance.scientific_name = request_dict["scientific_name"]
session.add(genus_instance)
session.commit()
return jsonify(genus_instance.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def add_state():
session = dbconnect()
request_dict = request.get_json()
try:
state_instance = State()
state_instance.state_name = request_dict["State"]
session.add(state_instance)
session.commit()
return jsonify(state_instance.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def add_genus():
session = dbconnect()
request_dict = request.get_json()
try:
genus_instance = session.query(Genus).filter(
Genus.scientific_name == request_dict["scientific_name"]).one()
return "Genus already exists", 400
except:
genus_instance = Genus()
genus_instance.scientific_name = request_dict["scientific_name"]
session.add(genus_instance)
session.commit()
return "ok"
def get_park(search_term):
session = dbconnect()
return_list = []
if search_term == "all":
for row in session.query(Park).all():
row_dict = row.__dict__
row_dict.pop("_sa_instance_state")
return_list.append(row_dict)
else:
for row in session.query(Park).filter(Park.park_name == search_term).all():
row_dict = row.__dict__
row_dict.pop("_sa_instance_state")
return_list.append(row_dict)
return jsonify(return_list)
def get_species(search_term):
session = dbconnect()
return_list = []
if search_term == "all":
for row in session.query(Species).all():
row_dict = row.__dict__
row_dict.pop("_sa_instance_state")
return_list.append(row_dict)
else:
for row in session.query(Species).filter(Species.id == search_term).all():
row_dict = row.__dict__
row_dict.pop("_sa_instance_state")
return_list.append(row_dict)
return jsonify(return_list)
def add_species():
session = dbconnect()
request_dict = request.get_json()
try:
genus_instance = session.query(Genus).filter(
Genus.scientific_name == request_dict["genus"]
["scientific_name"]).one()
except:
return "Genus does not exist, please add it", 400
species = Species()
species.scientific_name = request_dict["scientific_name"]
species.common_name = request_dict["common_name"]
species.genus_id = genus_instance.id
session.add(species)
session.commit()
return "ok"
def add_specimen():
session = dbconnect()
request_dict = request.get_json()
try:
species = session.query(Species).filter(
Species.scientific_name == request_dict["species"]
["scientific_name"]).one(
) #fix the database table so that there is only one
except:
return "Species does not exist, please add it", 400
specimen = Specimen()
specimen.name = request_dict["name"]
specimen.birth_date_time = request_dict["birth_date_time"]
specimen.species_id = species.id
session.add(specimen)
session.commit()
return "ok"
def add_state():
session = dbconnect()
request_dict = request.get_json()
try:
region_instance = session.query(Region).filter(Region.id == request_dict["region_id"]).one()
except:
return "Region does not exist, please add it", 400
try:
state_instance = State()
state_instance.state_name = request_dict["State"]
session.add(state_instance)
session.commit()
return jsonify(state_instance.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def add_specimen():
session = dbconnect()
request_dict = request.get_json()
try:
species = session.query(Species).filter(Species.id == request_dict["species"]["id"]).one()
except:
return "Species does not exist, please add it", 400
try:
specimen = Specimen()
specimen.name = request_dict["name"]
specimen.birth_date_time = request_dict["birth_date_time"]
specimen.species_id = species.id
session.add(specimen)
session.commit()
return jsonify(specimen.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def add_species():
session = dbconnect()
request_dict = request.get_json()
try:
genus_instance = session.query(Genus).filter(Genus.id == request_dict["genus"]["id"]).one()
except:
return "Genus does not exist, please add it", 400
try:
species = Species()
species.scientific_name = request_dict["scientific_name"]
species.common_name = request_dict["common_name"]
species.genus_id = genus_instance.id
session.add(species)
session.commit()
return jsonify(species.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def add_station():
session = dbconnect()
request_dict = request.get_json()
try:
# ["Region"]["id"] is then called in csv_import.py within the dict
region_instance = session.query(Region).filter(
Region.id == request_dict["Region"]["id"]).one()
except:
return "Region does not exist, please add it", 400
try:
station = Station()
station.station_name = request_dict["Station Name"]
station.total_1819 = request_dict["1819 Entries & Exits"]
station.region_id = region_instance.id
session.add(station)
session.commit()
return jsonify(station.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
def addBoat(boat_input):
# Get or create user
user = getOrAddUser(boat_input)
# Database
session = dbconnect()
boat = Boat()
boat.name = boat_input["boat"]["name"]
boat.started = boat_input["boat"]["started"]
boat.boatType = boat_input["boat"]["boatType"]
boat.desiredCourse = boat_input["boat"]["desiredCourse"]
boat.isActive = boat_input["boat"]["isActive"]
boat.boatFlags = boat_input["boat"]["boatFlags"]
boat.race = addBoatRace(session, boat_input["boat"]["race"])
boat.user = user
session.add(boat)
session.commit()
session.flush()
# Core
addBoatinCore(boat.name, boat_input["boat"]["race"]["startLat"],
boat_input["boat"]["race"]["startLon"], boat.boatType,
boat.boatFlags, boat.id)
return boat.id
def add_park_from_queue(request_dict):
session = dbconnect()
try:
state_instance = session.query(State).filter(
State.id == request_dict["state_id"]).one()
except:
# wrong. Not sure why this was wrong before? Json stuff only
return "State does not exist, please add it", 400
try:
park = Park()
park.park_name = request_dict["Park"]
park.state = state_instance
session.add(park)
session.commit()
# removed json below because this is for flask
return park.id
except exc.IntegrityError:
session.rollback()
# wrong
return "already exists", 400
def get_genus_and_species_and_specimen(search_term):
if request.method == 'GET':
return_list = []
session = dbconnect()
for genus_instance, species_instance, specimen_instance in session.query(
Genus, Species,
Specimen).filter(Genus.id == search_term,
Genus.id == Species.genus_id,
Species.id == Specimen.species_id).all():
row = {}
row["genus_id"] = genus_instance.id
row["genus_scientific_name"] = genus_instance.scientific_name
row["species_id"] = species_instance.id
row["species_scientific_name"] = species_instance.scientific_name
row["specimen_id"] = specimen_instance.id
row["specimen_name"] = specimen_instance.name
return_list.append(row)
return jsonify(return_list)
else:
return "Unsupported Method"
def add_county():
session = dbconnect()
request_dict = request.get_json()
try:
state_instance = session.query(State).filter(
State.id == request_dict["state_id"]).one()
except:
return "State does not exist, please add it", 400
try:
county = County()
county.county_name = data_input["County"]
county.majority_white = data_input[
"Ethnicities.White Alone, not Hispanic or Latino"]
county.state = state_instance
session.add(county)
session.commit()
return jsonify(county.id)
except exc.IntegrityError:
session.rollback()
return "already exists", 400
state = State()
state.state_name = data_input["State"]
session.add(state)
try:
county = County()
county.county_name = data_input["County"]
county.majority_white = data_input["Ethnicities.White Alone, not Hispanic or Latino"]
county.state = state
session.add(county)
session.commit()
except exc.IntegrityError:
session.rollback()
def import_data(filename):
return_list = []
with open(filename, newline='') as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
return_list.append(dict(row))
return return_list
session=dbconnect()
imported_counties = import_data("county_demographics.csv")
for row in imported_counties:
addData(session, row)
# inputs for addData are (session, data_input). The order assures that row is taken as the data_input, not necessary to assign elsewhere
