def get_postcode(postcodeText):
sql = "SELECT postcode FROM location WHERE replace(postcode, ' ', '') LIKE %s LIMIT %s"
val = ('%' + postcodeText.replace(" ", "") + '%', 100)
print(val)
mycursor.execute(sql, val)
try:
rows = mycursor.fetchall()
if len(rows) > 0:
recordDictionary = {}
num = 0
for row in rows:
recordDictionary[num] = row[0]
num = num + 1
message = "Total number of rows is: " + str(mycursor.rowcount)
returnVars = jsonify(postcodeSearch=postcodeText,
message=message,
records=recordDictionary)
status = 200
else:
message = "Total number of rows is: " + str(mycursor.rowcount)
returnVars = jsonify(postcodeSearch=postcodeText, message=message)
status = 404
except conn.Error as error:
message = "Failed to get records from MySQL table: {}".format(error)
returnVars = jsonify(postcodeSearch=postcodeText, message=message)
status = 404
return returnVars, status
def get_floodRisk(postcode):
sql = "SELECT postcode, latitude, longitude, floodRisk, riskDate FROM location WHERE replace(postcode, ' ', '') = %s LIMIT %s"
val = (postcode.replace(" ", ""), 1)
print(val)
mycursor.execute(sql, val)
try:
rows = mycursor.fetchall()
results = len(rows)
if results > 0:
row = rows[0]
postcode, latitude, longitude, floodRisk, riskDate = row[0], row[
1], row[2], row[3], row[4]
message = "Total number of rows is: " + str(mycursor.rowcount)
returnVars = jsonify(postcode=postcode,
message=message,
latitude=latitude,
longitude=longitude,
floodRisk=floodRisk,
riskDate=riskDate)
status = 200
else:
message = "Total number of rows is: " + str(mycursor.rowcount)
returnVars = jsonify(postcode=postcode, message=message)
status = 404
except conn.Error as error:
message = "Failed to get record from MySQL table: {}".format(error)
returnVars = jsonify(postcode=postcode, message=message)
status = 404
return returnVars, status
def azure():
logging.info("Launching azure page: start")
mycursor.execute("SELECT * FROM Courses WHERE CATEGORY LIKE 'Azure'")
output = mycursor.fetchall()
logging.info("Launching azure page: end")
return render_template("Azure.html", len= len(output), result= output)
def post(self):
args = parser.parse_args()
sql = "INSERT INTO TraineeList (Firstname, Lastname ,Age) VALUES (%s, %s,%s)"
val = (args['Firstname'], args['Lastname'], args['Age'])
if all(arg is not None for arg in val):
mycursor.execute(sql, val)
mydb.commit()
else:
abort(400, Error="Some values of parameters are empty")
return "Created", 201
def javascript():
logging.info("Launching javascript page: start")
mycursor.execute("SELECT * FROM Courses WHERE CATEGORY LIKE 'JavaScript';")
result = mycursor.fetchall()
print('request=', result)
logging.info("Launching javascript page: end")
return render_template("javascript.html", len = len(result), results=result)
def patch(self, trainee_id):
args = parser.parse_args()
# update only if arg value is not null
for param in args:
if args[param] is not None:
sql = 'UPDATE TraineeList SET {0} = "' "{1}" '" WHERE ID={2}'.format(
param, args[param], trainee_id)
mycursor.execute(sql)
mydb.commit()
return "1 record affected", 201
def delete(self, trainee_id):
if id_exist(trainee_id):
sql = '{0} {1}'.format("DELETE FROM TraineeList WHERE ID =",
trainee_id)
mycursor.execute(sql)
mydb.commit()
else:
abort(404,
message="Trainee with the specified ID {} doesn't exist".
format(trainee_id))
return '{0}'.format("1 record deleted"), 200
def get(self):
mycursor.execute("SELECT * FROM TraineeList")
result = mycursor.fetchall()
if len(result) == 0:
abort(404, Error="Table is empty")
# JSONify output
result = [
dict(zip([key[0] for key in mycursor.description], row))
for row in result
]
return result, 200
def home():
logging.info("Launching homepage: start")
if request.method == "POST":
my_title= request.form.get("title")
my_link= request.form.get("video_link")
my_category= request.form.get("category")
print("MES DATAS", my_link, my_title, my_category)
mycursor.execute("INSERT INTO Courses (CATEGORY, TITLE, LINK) VALUES (%s,%s,%s) ",(my_category, my_title, my_link) )
database.commit()
logging.info("Launching homepage: end")
return render_template("index.html")
def get(self, trainee_id):
# Check if exists
if id_exist(trainee_id):
sql = 'SELECT * FROM TraineeList WHERE ID= {0}'.format(trainee_id)
mycursor.execute(sql)
result = mycursor.fetchall()
mydb.commit()
# JSONify output
result = [
dict(zip([key[0] for key in mycursor.description], row))
for row in result
]
else:
Prometheus_client.Metrics.bad_request.labels(
'get', '/trainees/<id>').inc()
abort(404,
message="Trainee with the specified ID {} doesn't exist".
format(trainee_id))
Prometheus_client.Metrics.request_by_id.labels(
'get', '/trainees/' + str(trainee_id)).inc()
return result, 200
async def myteam(ctx):
user_id = ctx.author.id
query = "SELECT userID FROM Users"
mycursor.execute(query)
records = mycursor.fetchall()
id_exists = False
for record in records:
if user_id == record[0]:
id_exists = True
if not id_exists:
await ctx.send("Please enter a team name:")
name_response = await client.wait_for('message')
team_name = name_response.content
query = "INSERT INTO Users (userID, team_name) VALUES (%s, %s)"
values = (user_id, team_name)
mycursor.execute(query, values)
mydb.commit()
# Display Team Name
query1 = "SELECT team_name FROM Users WHERE userID = " + str(user_id)
mycursor.execute(query1)
records1 = mycursor.fetchall() # fetchone gives records variable above for some reason
await ctx.send("============" + records1[0][0].upper() + "============")
query2 = "SELECT position, name, team FROM User_Roster WHERE userID = " + str(user_id)
mycursor.execute(query2)
records2 = mycursor.fetchall()
for record in records2:
await ctx.send(record[0] + "-" + record[1] + "-" + record[2])
if records2 == []:
await ctx.send("Your team has no players.")
await ctx.send("What would you like to do? Please enter one of the following:\n"
"• ADD - Add a player to your team\n"
"• REMOVE - Remove a player from your team\n")
action_response = await client.wait_for('message', check=myteam_check)
action = action_response.content.upper()
if action == "ADD":
await ctx.send("What is the player's position? Please enter one of the following:\n"
"• QB\n"
"• RB\n"
"• WR\n"
"• TE\n"
"• K\n"
"• DST\n")
position_response = await client.wait_for('message', check=position_check)
position = position_response.content.upper()
await ctx.send("What is the player's full name? (You must omit numbers and suffixes from a player's name. For example, Patrick Mahomes II -> Patrick Mahomes and Michael Pittman Jr. -> Michael Pittman)")
name_response = await client.wait_for('message', check=not_bot_check)
player_name = name_response.content.lower()
# Making sure this is a valid Player
r = requests.get('https://www.fantasypros.com/nfl/reports/leaders/?year=2020')
soup = bs4.BeautifulSoup(r.text, features="html.parser")
is_displayed = False
p_add_name = None
p_add_team = None
p_add_position = None
for tr in soup.find_all('tr')[1:]:
tds = tr.find_all('td')
com_player_name = tds[1].text.lower()
if " iii" in com_player_name:
com_player_name = com_player_name.replace(" iii", "")
elif " ii" in com_player_name:
com_player_name = com_player_name.replace(" ii", "")
elif " jr." in com_player_name:
com_player_name = com_player_name.replace(" jr.", "")
if com_player_name == player_name and position.upper() == tds[3].text:
await ctx.send("VALID PLAYER")
p_add_name = tds[1].text
p_add_team = tds[2].text
p_add_position = tds[3].text
is_displayed = True
if not is_displayed:
await ctx.send("No player exists with that name and position. Please "
"make sure that the full name of the player is typed in "
"correctly and the corresponding position is correct.")
return
# Check if player already on team
query3 = "SELECT position, name, team FROM User_Roster WHERE userID = " + str(user_id)
mycursor.execute(query3)
records3 = mycursor.fetchall()
player = (p_add_position, p_add_name, p_add_team)
player_on_team = False
for record in records3:
if player == record:
player_on_team = True
if player_on_team:
await ctx.send("Error. This player is already on your team.")
return
query = "INSERT INTO User_Roster (userID, position, name, team) VALUES (%s, %s, %s, %s)"
values1 = (user_id, p_add_position, p_add_name, p_add_team)
mycursor.execute(query, values1)
mydb.commit()
await ctx.send("Player added successfully.")
else:
await ctx.send("What is the player's position? Please enter one of the following:\n"
"• QB\n"
"• RB\n"
"• WR\n"
"• TE\n"
"• K\n"
"• DST\n")
position_response = await client.wait_for('message', check=position_check)
position = position_response.content.upper()
await ctx.send("What is the player's full name? (You must omit numbers and suffixes from a player's name. For example, Patrick Mahomes II -> Patrick Mahomes and Michael Pittman Jr. -> Michael Pittman)")
name_response = await client.wait_for('message', check=not_bot_check)
player_name = name_response.content.lower()
# Making sure this is a valid Player
r = requests.get('https://www.fantasypros.com/nfl/reports/leaders/?year=2020')
soup = bs4.BeautifulSoup(r.text, features="html.parser")
is_displayed = False
p_add_name = None
p_add_team = None
p_add_position = None
for tr in soup.find_all('tr')[1:]:
tds = tr.find_all('td')
com_player_name = tds[1].text.lower()
if " iii" in com_player_name:
com_player_name = com_player_name.replace(" iii", "")
elif " ii" in com_player_name:
com_player_name = com_player_name.replace(" ii", "")
elif " jr." in com_player_name:
com_player_name = com_player_name.replace(" jr.", "")
if com_player_name == player_name and position.upper() == tds[3].text:
await ctx.send("VALID PLAYER")
p_add_name = tds[1].text
p_add_team = tds[2].text
p_add_position = tds[3].text
is_displayed = True
if not is_displayed:
await ctx.send("No player exists with that name and position. Please "
"make sure that the full name of the player is typed in "
"correctly and the corresponding position is correct.")
return
# Check if player on team
query3 = "SELECT position, name, team FROM User_Roster WHERE userID = " + str(user_id)
mycursor.execute(query3)
records3 = mycursor.fetchall()
player = (p_add_position, p_add_name, p_add_team)
player_on_team = False
for record in records3:
if player == record:
player_on_team = True
if not player_on_team:
await ctx.send("Error. This player is not on your team.")
return
# THIS FORMAT WILL NOT WORK
# query4 = "DELETE FROM User_Roster WHERE userID = " + str(user_id) + \
# " AND position = " + p_add_position + \
# " AND name = " + p_add_name + " AND team = " + p_add_team
query4 = "DELETE FROM User_Roster WHERE userID = %s and position = %s " \
"and name = %s and team = %s"
values = (str(user_id), p_add_position, p_add_name, p_add_team)
mycursor.execute(query4, values)
mydb.commit()
await ctx.send("Player deleted successfully.")
#print( sum(1 for line in f) )
#parse csv
with open(fname, 'r') as fp:
reader = csv.reader(fp, delimiter=",")
for row in reader:
content = list(row[i] for i in included_cols)
postcode = content[0]
floodRisk = content[1]
if content[2] == '\\N':
riskDate = ''
else:
riskDate = content[2]
latitude = content[3]
longitude = content[4]
#if riskLevel != 'None': #gets any at risk of flooding
#print(content)
print(content)
#mycursor.execute("INSERT INTO location (postcode, latitude, longitude, floodRisk, riskDate) VALUES ('"+postcode+"', '"+latitude+"', '"+longitude+"', '"+floodRisk+"', '"+riskDate+"') ")
#print("INSERT INTO location (postcode, latitude, longitude, floodRisk, riskDate) VALUES ('"+postcode+"', '"+latitude+"', '"+longitude+"', '"+floodRisk+"', '"+riskDate+"') ")
sql = "INSERT INTO location (postcode, latitude, longitude, floodRisk, riskDate) VALUES (%s, %s, %s, %s, %s)"
val = (postcode, latitude, longitude, floodRisk, riskDate)
mycursor.execute(sql, val)
myconnection.commit()
print("Inserted " + postcode)
#attribution required
#Contains Environment Agency data licensed under the Open Government Licence v3.0 (http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/).