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():