def blast():
"""
Geeft een pagina weer waar je blast kunt toevoegen aan de database
:return: De html pagina met blast toevoegen aan de database
"""
if request.method == "GET":
blast_version = request.args.get("blast", "None")
header = request.args.get("header", "None")
hit_id = request.args.get("hit_id", "None")
acc = request.args.get("acc", "None")
organisme = request.args.get("organisme", "None")
ident = request.args.get("ident", "None")
# kijkt of de ident aan de eisen voeldoet en of het een getal is
try:
ident = int(ident)
if ident < 40:
return render_template("blast.html",
reactie="Te lage identity")
else:
ident = str(ident)
except ValueError:
return render_template("blast.html",
reactie="Geen geldige identity")
# kijkt of de evalue aan de eisen voeldoet en of het een getal is
evalue = request.args.get("evalue", "None")
try:
evalue = float(evalue)
if evalue >= 0.00001:
return render_template("blast.html", reactie="Te hoge e-value")
else:
evalue = str(evalue)
except ValueError:
return render_template("blast.html",
reactie="Geen geldige e-value")
defen = request.args.get("defenitie", "None")
qseq = request.args.get("qseq", "None")
if blast_version == "None" or header == "None" or hit_id == "None" \
or acc == "None" or organisme == "None" or organisme == "None" \
or ident == "None" or evalue == "None" or defen == "None" \
or qseq == "None":
# print("if dus minimaal een is None")
print(blast_version, header, hit_id)
return render_template("blast.html",
reactie="Het invoeren is mislukt")
else:
print("Alles heeft een waarde")
connector(blast_version, header, hit_id, acc, organisme, ident,
evalue, defen, qseq)
return render_template(
"blast.html", reactie="De Blast resultaten zijn toegevoegd!")
def get_transforms(username, imagename):
# Open db connection
print("Loading user %s's images ..." % (username))
result = False
cnx = connector()
cursor = cnx.cursor()
imagename = imagename[:-1]
#Retreive userid From users Table
userid = get_userid(username)
# Retrieve image_name From images Table
cursor.execute(
"SELECT orig,redblueshift,overexposed,grayscale FROM images WHERE userid = %s && orig= '%s'"
% (userid, re.escape(imagename)))
transforms = cursor.fetchall()
# Close db connection
cursor.close()
cnx.close()
# Create a list that is compliant with HTML code
newlist = []
newlist2 = []
for orig, redblueshift, overexposed, grayscale in transforms:
newlist.append(orig)
newlist.append(redblueshift)
newlist.append(overexposed)
newlist.append(grayscale)
for image in newlist:
newlist2.append(image)
return newlist2
def write_team_extras(team_info, team_n):
"""
writing an additional information about each team from an external API - thesportsdb.com/api.php
:param team_info: a dictionary with the desired external data
:param team_n: team name that the external data is belong to
"""
my_db = connector()
cur = my_db.cursor()
team_des = (unicodedata.normalize('NFD',
team_info['strDescriptionEN']).encode(
'ascii', 'ignore'))
if team_info['strStadiumDescription'] is not None:
stad_des = (unicodedata.normalize(
'NFD',
team_info['strStadiumDescription']).encode('ascii', 'ignore'))
else:
stad_des = None
cur.execute(
"INSERT INTO teams_extras (team_id, id_Team, team_name, team_name_short, alternate_team_name,"
"formed_year, stadium_name, stadium_pic_url, stadium_description, stadium_location, stadium_capacity,"
"team_website, team_facebook, team_twitter, team_instagram, team_description)"
"VALUES ((SELECT team_id FROM teams WHERE team_name='" + team_n +
"' LIMIT 1), %s, %s, %s, %s, %s, %s, %s,"
"%s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE id_Team = id_Team",
(team_info['idTeam'], team_info['strTeam'], team_info['strTeamShort'],
team_info['strAlternate'], team_info['intFormedYear'],
team_info['strStadium'], team_info['strStadiumThumb'], stad_des,
team_info['strStadiumLocation'], team_info['intStadiumCapacity'],
team_info['strWebsite'], team_info['strFacebook'],
team_info['strTwitter'], team_info['strInstagram'], team_des))
my_db.commit()
cur.close()
def check_and_delete(league_name):
"""
this function checks if the league already exists in the database, if so deletes it.
:param league_name: getting the league name from user
"""
my_db = connector()
cur = my_db.cursor()
cur.execute("SELECT league_id FROM leagues WHERE league_name ='" +
league_name + "'")
league_id = cur.fetchall()
if len(league_id) != 0:
cur.execute("SELECT team_id FROM teams WHERE league_id=" +
str(league_id[0][0]))
team_ids = cur.fetchall()
if len(team_ids) > 0:
for team_id in team_ids:
cur.execute("DELETE FROM players WHERE team_id = " +
str(team_id[0]))
cur.execute("DELETE FROM managers WHERE team_id = " +
str(team_id[0]))
cur.execute("DELETE FROM teams_extras WHERE team_id = " +
str(team_id[0]))
my_db.commit()
cur.execute("DELETE FROM teams WHERE league_id = " +
str(league_id[0][0]))
my_db.commit()
cur.execute("DELETE FROM leagues WHERE league_id = " +
str(league_id[0][0]))
my_db.commit()
def image_exists(username, imagename):
# Open db connection
print("Looking for image %s ..." % (imagename))
cnx = connector()
cursor = cnx.cursor()
# Retreive userid From users Table
userid = get_userid(username)
# Retrieve image From images Table
cursor.execute(
"SELECT imagename FROM images WHERE userid = %s && imagename = '%s'" %
(userid, imagename))
image_list = cursor.fetchall()
if (len(image_list) == 0):
print("Image %s does not exist!" % (imagename))
return False
print("Image %s does exist!" % (imagename))
# Close db connection
cursor.close()
cnx.close()
return True
def connection_factory(db_type, **credentials):
if db_type == ('mysql'):
connector = MySQLConnector
else:
raise ValueError(
'Connector for DB Type {} not implemented yet'.format(db_type))
return connector(**credentials)
def addStock():
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
if ((str(nameBox.get()) == '')):
attrList = {}
attrList[0] = str(nameLabel.get("1.0", 'end-1c'))
attrList[2] = str(priceText.get("1.0", 'end-1c'))
attrList[1] = str(qtyText.get("1.0", 'end-1c'))
attrList[3] = str(costprice.get("1.0", 'end-1c'))
DbUtils.insertIntoDB(str(categoty.get()), attrList, cursor)
connection.commit()
else:
quantity = 0
quantity = getcurrentQty()
quantity += int(qtyText.get("1.0", 'end-1c'))
querry = "UPDATE " + str(categoty.get(
)) + " SET `qty` =" + str(quantity) + " WHERE `" + str(
categoty.get()) + "`.`Name`= '" + str(nameBox.get()) + "'"
cursor.execute(querry)
connection.commit()
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
formatted_date = Fetch.getFormattedDate()
if (str(nameLabel.get("1.0", 'end-1c')) == ''):
name = str(nameBox.get())
else:
name = str(nameLabel.get("1.0", 'end-1c'))
attrList = {}
attrList[0] = str(categoty.get())
attrList[1] = name
attrList[2] = str(qtyText.get("1.0", 'end-1c'))
attrList[3] = str(costprice.get("1.0", 'end-1c'))
attrList[4] = str(priceText.get("1.0", 'end-1c'))
attrList[5] = formatted_date
attrList[6] = int(qtyText.get("1.0", 'end-1c')) * int(
costprice.get("1.0", 'end-1c'))
DbUtils.insertIntoDB('newstock', attrList, cursor)
connection.commit()
messagebox.showinfo(
"", "The item has been successfully added to the stock")
newStock.destroy()
def update_mydb(message):
try:
conn = mysql.connector()
cur = conn.cursor()
cur.execute(message)
cur.commit()
conn.close()
except:
print 'update live_indexpage failure'
def cursor(self):
"""Return cursor"""
global connector
if self._connection is None:
if connector is None:
raise DateBaseError('Connector is not initialized.')
self._connection = connector()
logging.info('open connection <%s>...' % hex(id(self._connection)))
return self._connection.cursor()
def run_op_code(project_name: str, op_code: str) -> defaultdict:
db = connector(project_name)
cursor = db.cursor()
cursor = cursor.execute(op_code, multi=True)
res = dict()
for records in cursor:
records = records.fetchall()
res = dict(records)
return defaultdict(lambda: None, res)
def add_image(username, imagename, image_url):
# Get information about image and user
userid = get_userid(username)
image_orig = image_url
# Open db connection
#print("Uploading image %s ..." % (imagename))
result = False
cnx = connector()
cursor = cnx.cursor()
# Determine If Image Exists
# if (image_exists(username, imagename)):
# # Close db connection
# cursor.close()
# cnx.close()
# return result
# Insert filename to images table
try:
cursor.execute(
"INSERT INTO images (userid,imagename,orig,redblueshift,grayscale,overexposed) VALUES (%d,'%s','%s','NULL','NULL','NULL')"
% (userid, imagename, image_orig))
cnx.commit()
result = True
except:
flash("Upload image failed.")
cnx.rollback()
# Split the image name into rawname and extension
(rawname, ext) = os.path.splitext(imagename)
update_prefix = "UPDATE images SET "
update_suffix = " WHERE imagename = '%s'" % (imagename)
update_middle = ""
## Update row with paths to each transform
for transform in IMAGE_TRANSFORMS:
transformed_image = config.AWS_URL + username + "/" + rawname + "_" + transform + ext
update_middle += " %s = '%s'," % (transform,
re.escape(transformed_image))
print("UPDATE COMMAND")
update_command = update_prefix + update_middle[:-1] + update_suffix
print(update_command)
try:
cursor.execute(update_command)
cnx.commit()
result = True
except:
cnx.rollback()
# Close db connection
cursor.close()
cnx.close()
def loadNames(event):
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
cursor.execute(
"USE archa"
) #this function is called to display products under a category
querry = " select Name from " + str(categoty.get())
cursor.execute(querry)
subTables = cursor.fetchall()
nameBox['values'] = subTables
def add_user(username, password):
# Determine if User meets requirements
if (len(username) < 8):
flash("Username must be atleast 8 characters long.")
return False
# Determine if Password meets requirements
if (len(password) < 8):
flash("Password must be atleast 8 characters long.")
return False
# Open db Connection
print("Checking if username %s is available..." % (username))
result = False
cnx = connector()
cursor = cnx.cursor()
# Retrieve Username Availability
cursor.execute("SELECT username FROM users WHERE username = '******'" %
(username))
matching_users = cursor.fetchall()
if (len(matching_users) == 1):
flash("Username %s is unavailable." % (username))
elif (len(matching_users) > 1):
flash(
"More than 1 user with the same username:'******'. Something bad happened!"
% (username))
else:
print("Username Available.\nAdding Username: %s" % (username))
# Encrypt New Password
passsalt = uuid.uuid4().hex
hash_object = hashlib.sha1(
password.encode('utf-8') + passsalt.encode('utf-8'))
passhash = hash_object.hexdigest()
# Add New User
try:
cursor.execute(
"INSERT INTO users (username, passhash, passsalt) VALUES ('%s','%s','%s')"
% (username, passhash, passsalt))
cnx.commit()
result = True
except:
cnx.rollback()
# Close db connection
cursor.close()
cnx.close()
return result
def delPupper(IDs):
cnx = connector()
mycursor = cnx.cursor()
sql = "DELETE FROM pupper where id=%s"
idlist = [str(IDs)]
mycursor.execute(sql, idlist)
# if mycursor.lastrowid:
print("adopt successful")
# else:
# print('last insert id not found')
cnx.commit()
def write_league(league_info: list):
"""
writing the league info to the database
:param league_info: a list with the league name and the number of teams in this league
"""
my_db = connector()
cur = my_db.cursor()
cur.execute(
"INSERT INTO leagues (league_name, number_of_teams) VALUES (%s, %s)"
"ON DUPLICATE KEY UPDATE league_id=league_id",
(league_info[0], league_info[1]))
my_db.commit()
cur.close()
def get_components(project_name: str, release_date: datetime):
lower_date_bound = release_date.strftime('%Y-%m-%d %H:%M:%S')
upper_date_bound = (release_date +
relativedelta(months=6)).strftime('%Y-%m-%d %H:%M:%S')
op_code = f'''
select distinct(Component) from Commit
where CommitterDate between '{ lower_date_bound }' and '{ upper_date_bound }'
'''
db = connector(project_name)
cursor = db.cursor()
cursor.execute(op_code)
res = cursor.fetchall()
return [record[0] for record in res]
def get_results(message):
try:
conn = mysql.connector()
cur = conn.cursor()
cur.execute(message)
results = cur.fetchall()
conn.close()
except:
return None
if results != () and len(result[0])==5:
results = results[0]
ruturn(each[0],each[1],each[2],each[3],each[4])
else:
return None
def getFields(tableName): #returns all fields in the table
sqlConnector = connector() #get the connector to the db
connection = sqlConnector.getConnector()
cursor = connection.cursor()
querry = "SELECT * FROM "
querry = querry + str(tableName)
cursor.execute(querry)
field_names = {}
column = 0
num_fields = len(cursor.description)
for d in cursor.description:
field_names[column] = d[0]
column += 1
continue
return field_names
def get_filter():
"""Deze functie zorgt ervoor dat er een zoekwoord opgegeven kan
worden en dat de descriptions met het zoekwoord getoond worden.
:return: het HTML bestand met de filter en data
"""
if request.method == "POST":
ftr = request.form.get("filter", "")
conn = connector()
ftr_data = use_filter(conn, ftr)
return render_template("Afvinkopdracht 3 HTML.html", filter=ftr,
data=ftr_data)
else:
return render_template("Afvinkopdracht 3 HTML.html", filter="",
data="")
def write_teams(teams_info: list, lg_name):
"""
writing a team info to the database
:param teams_info: the team name, number of players in the team
:param lg_name: the league which the teams belong to - foreign key
"""
my_db = connector()
cur = my_db.cursor()
cur.execute(
"INSERT INTO teams (team_name, number_of_players, league_id) VALUES "
"(%s, %s, (SELECT league_id FROM leagues WHERE league_name='" +
lg_name + "'))"
"""ON DUPLICATE KEY UPDATE team_id=team_id""",
(teams_info[0], teams_info[1]))
my_db.commit()
cur.close()
def readBreed():
cnx = connector()
mycursor = cnx.cursor()
sql = "SELECT * FROM breed"
mycursor.execute(sql)
readData = mycursor.fetchall()
breedList = []
for item in readData:
breedList.append(item)
for items in breedList:
BreedObj = m.Breed(items[1], items[2], items[3], items[0])
BreedObj.display()
cnx.close()
mycursor.close()
def get_userid(username):
# Open db connection
print("Looking for user %s ..." % (username))
cnx = connector()
cursor = cnx.cursor()
# Retreive id from users table
cursor.execute("SELECT id FROM users WHERE username = '******'" % (username))
matching_users = cursor.fetchall()
for row in matching_users:
userid = row[0]
# Close db connection
cursor.close()
cnx.close()
return userid
def readByIdPupper():
cnx = connector()
mycursor = cnx.cursor()
sql = "SELECT * FROM pupper"
mycursor.execute(sql)
readData = mycursor.fetchall()
breedList = []
for item in readData:
breedList.append(item)
for items in breedList:
pupperObj = m.Pupper(items[0], items[1], items[2], items[3], items[4],
items[5], items[6])
pupperObj.display()
cnx.close()
def readByIdBreed(IDs):
cnx = connector()
mycursor = cnx.cursor()
sql = "SELECT * FROM breed WHERE id = %s"
idlist = [str(IDs)]
mycursor.execute(sql, idlist)
readData = mycursor.fetchall()
breedList = []
for item in readData:
breedList.append(item)
for items in breedList:
breedObj = m.Breed(items[0], items[1], items[2], items[3])
breedObj.display()
cnx.close()
def readByIdPupper(IDs):
cnx = connector()
mycursor = cnx.cursor()
sql = "SELECT * FROM pupper p join breed b on p.breed_id = b.id where p.id=%s"
idlist = [str(IDs)]
mycursor.execute(sql, idlist)
readData = mycursor.fetchall()
breedList = []
for item in readData:
breedList.append(item)
for items in breedList:
ppObj = m.Pupper(items[1], items[2], items[3], items[4], items[5], items[6], items[7], items[0])
ppObj.display()
cnx.close()
mycursor.close()
def addPupper(pupperObj):
cnx = connector()
mycursor = cnx.cursor()
try:
sql = "INSERT INTO pupper (name, sex, weight, height, color, date_of_birth, breed_id) values (%s,%s,%s,%s,%s,%s,%s)"
newRow = (pupperObj.name, pupperObj.sex, pupperObj.weight, pupperObj.height, pupperObj.color, pupperObj.date_of_birth,pupperObj.breed_id)
mycursor.execute(sql,newRow)
if mycursor.lastrowid:
print('last insert id', mycursor.lastrowid)
else:
print('last insert id not found')
cnx.commit()
finally:
mycursor.close()
cnx.close()
def login_user(username, password):
# Open db connection
#print("Attempting to log in as %s..." % (username))
cnx = connector()
cursor = cnx.cursor()
# Retrieve User Information
cursor.execute(
"SELECT passhash, passsalt FROM users WHERE username = '******'" %
(username))
matching_users = cursor.fetchall()
# Close db connection
cursor.close()
cnx.close()
# Verify Credentials
if (len(matching_users) == 0):
flash("User %s does not exist" % (username))
return False
elif (len(matching_users) > 1):
flash(
"More than 1 user with the same username:'******'. Something bad happened!"
% (username))
return False
else:
#print("Verifying Credentials...")
# Recreate Hashed Password
for row in matching_users:
passhash = row[0]
passsalt = row[1]
hash_object = hashlib.sha1(
password.encode('utf-8') + passsalt.encode('utf-8'))
newhash = hash_object.hexdigest()
if (passhash == newhash):
#print("User %s authenticated!" % (username))
return True
else:
flash("Password is incorrect!")
return False
def findPuppersByName(name):
cnx = connector()
mycursor = cnx.cursor()
sql = "SELECT * FROM pupper where name=%s"
nameList = [name]
mycursor.execute(sql, nameList)
findName = mycursor.fetchall()
pupperList = []
for item in findName:
pupperList.append(item)
for items in pupperList:
ppObj = m.Pupper(items[1], items[2], items[3], items[4], items[5], items[6], items[7], items[0])
ppObj.display()
cnx.close()
mycursor.close()
def addBreed(breedObj):
cnx = connector()
mycursor = cnx.cursor()
try:
sql = "INSERT INTO breed (name, temperament, coat) values (%s,%s,%s)"
newRow = (breedObj.name, breedObj.temperament, breedObj.coat)
mycursor.execute(sql, newRow)
if mycursor.lastrowid:
print('last insert id', mycursor.lastrowid)
else:
print('last insert id not found')
cnx.commit()
finally:
mycursor.close()
cnx.close()
def write_players(players_info: list, team_n):
"""
writing a list of players to the database
:param players_info: a list of dictionaries, each dict stands for a player with several data fields.
:param team_n: the name of the team which the players belong to
"""
my_db = connector()
cur = my_db.cursor()
for player in players_info:
cur.execute(
"INSERT INTO players (team_id, player_name, nationality, birth_date, height_cm, prefd_foot,"
"position, shirt_num)"
"VALUES ((SELECT team_id FROM teams WHERE team_name='" + team_n +
"' LIMIT 1),%s, %s, %s, %s, %s, %s, %s)"
"ON DUPLICATE KEY UPDATE player_id=player_id",
(player['name'], player['nationality'], player['birth_date'],
player['height'], player['prefd_foot'], player['position'],
player['shirt_num']))
my_db.commit()
cur.close()
def selPupperMult(sex, weight):
cnx = connector()
mycursor = cnx.cursor()
sql = "SELECT * FROM pupper where sex=%s and weight<%s"
nameList = [sex, weight]
mycursor.execute(sql, nameList)
findName = mycursor.fetchall()
pupperList = []
for item in findName:
pupperList.append(item)
for items in pupperList:
ppObj = m.Pupper(items[1], items[2], items[3], items[4], items[5], items[6], items[7], items[0])
ppObj.display()
cnx.close()
mycursor.close()
def get_imagelist(username):
# Open db connection
print("Loading user %s's images ..." % (username))
result = False
cnx = connector()
cursor = cnx.cursor()
# Retreive userid From users Table
userid = get_userid(username)
# Retrieve image_name From images Table
cursor.execute("SELECT orig FROM images WHERE userid = %s" % (userid))
image_list = cursor.fetchall()
# Close db connection
cursor.close()
cnx.close()
newlist = []
for images in image_list:
newlist.append(images[0])
return newlist
handler.setFormatter(formatter)
logger.addHandler(handler)
logger.setLevel(logging.INFO)
try:
db_conn = mysql.connector.connect(host = 'localhost', user = '******', password = '******')
db_cursor = db_conn.cursor()
except Exception as e:
print(e)
print("Fail to connect to server")
logger.error("Fail to connect to mysql server: " + str(e))
exit(0)
logger.info("Connect Established")
s = connector()
print("Init Finished")
logger.info("Init Finished")
while True:
logger.debug("New Round")
receive_queue = s.receive()
# print("Recv: " + str(receive_queue))
logger.debug("receive queue: len " + str(len(receive_queue)))
send_queue = dbconn.query_send(db_cursor)
#print("Send: " + str(send_queue))
logger.debug("send queue: len " + str(len(send_queue)))
if receive_queue:
#Received Message
return "'" + value + "'"
elif (column_index == 3): # definition
return "'" + value + "'"
elif (column_index == 4): # context
return "'" + value + "'"
elif (column_index == 5): # rating
return str(value)
elif (column_index == 6): # french_name
return "'" + value + "'"
elif (column_index == 7): # french_definition
return "'" + value + "'"
elif (column_index == 8): # french_context
return "'" + value + "'"
#Always start with the following two lines
connector = connector()
connector.initConnection()
table = words_table(connector)
key = {}
key["name"] = "dog"
key["pos"] = "animal"
table.view_full_table()
table.find_row(key)
table.view_full_table()
#table.insert_row(key)
#Once a query is done, results are stored in get_cursor(). We MUST read the results before moving on
for (result) in table.get_cursor():
