def invoice_range_insert(invoice_code, start_num, end_num, total, use_flag, create_time): global conn global cursor query = "INSERT INTO invoice_range(invoice_code, start_num, end_num, total, create_time) VALUES(%s,%s,%s,%s,%s) " args = (invoice_code, start_num, end_num, total, use_flag, create_time) # use_flag: # 0 - unused # 1 - in use # 2 - used try: conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Error: conn.rollback() log.exception('Error occurred inserting to invoice_range table') finally: cursor.close() conn.close()
def storeDB(date, time, device_id, file_type, count, filedatetime): query = "INSERT INTO palert(date, time, device_id, type, count, filedatetime) VALUES(%s, %s, %s, %s, %s, %s)" args = (date, time, device_id, file_type, count, filedatetime, ) palert_id = 0 db_config = config.db_config conn = MySQLConnection(**db_config) try: cursor = conn.cursor() cursor.execute(query, args) if cursor.lastrowid: print('last insert id', cursor.lastrowid) palert_id = int(cursor.lastrowid or 0) else: raise Exception('last insert id not found') conn.commit() except Exception as exception: print('Exception: %s' % exception) conn.rollback() finally: cursor.close() return palert_id
def insertDeviceInfo(device_status, device_id, datetime): query = "INSERT INTO devices_status (status, device_id, created_at) VALUES (%s, %s, %s)" args = ( device_status, device_id, datetime, ) try: db_config = config.db_config conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() return True except Exception as exception: functions.log('Exception: ' + exception) conn.rollback() return False finally: cursor.close() conn.close()
def create_card(self, args): try: conn = MySQLConnection(**self.__creds) cursor = conn.cursor() result = cursor.callproc('Create_Card', (args['passport'], 0, 0)) if result[1] > 0: conn.commit() else: conn.rollback() response = result[1] > 0 message = result[2] cursor.close() conn.close() return response, message except Error as e: response = False message = 'Error: {0} at {1}'.format(str(e), 'ProcessingInterface.create_card()') return response, message except Exception as ex: response = False message = 'Exception: {0} at {1}'.format(str(ex), 'ProcessingInterface.create_card()') return response, message
def storeDB(date, device_id, count, status): query = "INSERT INTO iso(date, device_id, count, status) VALUES(%s, %s, %s, %s)" args = (date, device_id, count, status) iso_id = 0 try: db_config = config.db_config conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) if cursor.lastrowid: print('last insert id', cursor.lastrowid) iso_id = int(cursor.lastrowid or 0) else: raise Exception('last insert id not found') conn.commit() except Exception as exception: print('Exception: %s' % exception) conn.rollback() finally: cursor.close() conn.close() return iso_id
def invoice_range_insert(invoice_code, start_num, end_num, total, use_flag, create_time): global conn global cursor query = "INSERT INTO invoice_range(invoice_code, start_num, end_num, total, create_time) VALUES(%s,%s,%s,%s,%s) " args = (invoice_code, start_num, end_num, total, use_flag, create_time) # use_flag: # 0 - unused # 1 - in use # 2 - used try: conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) if cursor.lastrowid: print('last insert id', cursor.lastrowid) # todo: change to logging else: print('last insert id not found') # todo: change to logging conn.commit() except Error as error: conn.rollback() print(error) # todo: change to logging finally: cursor.close() conn.close()
def insert(self, sql_values): sql = 'INSERT INTO MOVIES (' + ', '.join( [str(k) for k in self.__dict__.keys()] ) + ') VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' url = urlparse(os.environ['CLEARDB_DATABASE_URL']) conn = MySQLConnection( host=url.hostname, port=url.port, user=url.username, password=url.password, database=url.path[1:], ) conn.ping(reconnect=True) cur = conn.cursor() try: cur.executemany(sql, sql_values) conn.commit() except Exception as e: conn.rollback() raise finally: cur.close() conn.close()
def connect(): """ Connect to MySQL database """ try: print('Connecting to MySQL database...') conn = MySQLConnection(host='localhost', database='billing_simple', user='******', password='******') # Подготовка объекта cursor с помощью метода cursor() cursor =conn.cursor() # Удалите таблицу, если она уже существует с помощью метода execute() cursor.execute("DROP TABLE IF EXISTS list_book") # Создайте таблицу согласно требованию sql = """CREATE TABLE list_book( id INT, ISBN FLOAT, Name CHAR(40), page_num INT, pub_year INT )""" cursor.execute(sql) # Подготовьте запрос SQL для вставки записи в базу данных. sql = """INSERT INTO library_data_book_has_author(id, ISBN, Name, page_num, pub_year) VALUES (109,6990901198455,"Foes Anderew Wresiveshg",642,1957)""" #frame.to_sql(name='billing_simple', con=conn, if_exists = 'append', index=False) try: # Выполните команду SQL cursor.execute(sql) # Зафиксировать изменения в базе данных conn.commit() except: # Откат в случае ошибки conn.rollback() if conn.is_connected(): print('connection established.') else: print('connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def save(nome, dosagem, tipoDosagem, tipo): qry = "INSERT INTO medicamentos (nomeMedicamento, dosagemMedicamento, tipoDosagem, tipoDosagemMedicamento )" \ "VALUES (%s, %s, %s, %s)" vals = (nome, dosagem, tipoDosagem, tipo) try: config = read_db_config() conn = MySQLConnection(**config) cursor = conn.cursor() cursor.execute(qry, vals) conn.commit() except Error as error: print(error) conn.rollback() finally: cursor.close() conn.close()
def insert_email(recipient, cc, subject, body): query = "INSERT INTO email_queue(recipient, cc, subject, body) VALUES(%s, %s, %s, %s)" args = ( recipient, cc, subject, body, ) db_config = config.db_config conn = MySQLConnection(**db_config) try: cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Exception as exception: print('Exception: %s' % exception) conn.rollback() finally: cursor.close()
def insert_heartbeat(request, bus_id, response_encrypted, response_decrypted, result, log_time): """ Insert values to the heartbeat_monitor table. :param request: encrypted request data :param bus_id: :param response_encrypted: encrypted response data :param response_decrypted: decrypted response data :param result: 0 - failure (i.e. server returned content code other than 200). 1- success (server returned content code 200) :param log_time: :return: None """ global conn global cursor query = "INSERT INTO heartbeat_monitor(request, bus_id, response_encrypted, response_decrypted, result, " \ "log_time) VALUES(%s,%s,%s,%s,%s,%s) " args = (request, bus_id, response_encrypted, response_decrypted, result, log_time) try: conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) if cursor.lastrowid: log.info('last insert id', cursor.lastrowid) else: pass conn.commit() except Error as error: conn.rollback() log.exception("Exception occurred") finally: cursor.close() conn.close()
def sendEmail(message, fullpath): to = config.receiver_email cc = config.engineer_email subject = "System Parsing Error" message = "<p>The error has been occurred during parsing</p><p>" + \ message+"</p><p>"+fullpath+"</p>" query = "INSERT INTO email(`to`, `cc`, `subject`, `body`) VALUES(%s, %s, %s, %s)" args = (to, cc, subject, message) try: db_config = config.db_config conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Exception as exception: print('Exception: %s' % exception) conn.rollback() finally: cursor.close() conn.close()
def delete(cls): sql = 'DELETE FROM MOVIES' url = urlparse(os.environ['CLEARDB_DATABASE_URL']) conn = MySQLConnection( host=url.hostname, port=url.port, user=url.username, password=url.password, database=url.path[1:], ) conn.ping(reconnect=True) cur = conn.cursor() try: cur.execute(sql) conn.commit() except Exception as e: conn.rollback() raise finally: cur.close() conn.close()
class PhotoShopDatabase(): def __init__(self, user='******', password='******', host='localhost', database='test'): try: self.conn = MySQLConnection(user=user, password=password, host=host, database=database) if not self.conn.is_connected(): print("Database connection failed") else: print("Connected to Database") except Error as error: print(error) def disconnect(self): self.conn.close() def iter_row(self, cursor, size=10): while True: rows = cursor.fetchmany(size) if not rows: break for row in rows: yield row # Customer Menu def list_customers(self): try: cursor = self.conn.cursor() query = """ SELECT * FROM Customer; """ cursor.execute(query) print("*RESULTS*") for row in self.iter_row(cursor, 10): print(row) print("*RESULTS*") except Error as e: print("list_customrer failed") print(e) finally: cursor.close() def list_customers_that_spent_more_than_hundred(self): try: cursor = self.conn.cursor() query = """ SELECT * FROM Photographer; """ cursor.execute(query) print("*RESULTS*") for row in self.iter_row(cursor, 10): print(row) print("*RESULTS*") except Error as e: print("list_photographers failed") print(e) finally: cursor.close() # Photographer Menu def list_photographers(self): try: cursor = self.conn.cursor() query = """ SELECT * FROM Photographer; """ cursor.execute(query) print("*RESULTS*") for row in self.iter_row(cursor, 10): print(row) print("*RESULTS*") except Error as e: print("list_photographers failed") print(e) finally: cursor.close() # Transaction Menu # Model Menu def list_models(self): try: cursor = self.conn.cursor() query = """ SELECT * FROM MODEL; """ cursor.execute(query) print("*RESULTS*") for row in self.iter_row(cursor, 10): print(row) print("*RESULTS*") except Error as e: print("list_models failed") print(e) finally: cursor.close() # Compute Sales Menu # Photo Menu def list_all_photos(self): try: cursor = self.conn.cursor() query = """ SELECT * FROM Photo; """ cursor.execute(query) print("*RESULTS*") for row in self.iter_row(cursor, 10): print(row) print("*RESULTS*") except Error as e: print("list_all_photos failed") print(e) finally: cursor.close() def list_photos_bought(self): try: cursor = self.conn.cursor() query = """ SELECT * FROM Photo WHERE TransID IS NOT NULL;""" cursor.execute(query) print("*RESULTS*") for row in self.iter_row(cursor, 10): print(row) print("*RESULTS*") except Error as e: print("list_all_photos failed") print(e) finally: cursor.close() def list_photos_not_bought(self): try: cursor = self.conn.cursor() query = """ SELECT * FROM Photo WHERE TransID IS NULL; """ cursor.execute(query) print("*RESULTS*") for row in self.iter_row(cursor, 10): print(row) print("*RESULTS*") except Error as e: print("list_all_photos failed") print(e) finally: cursor.close() def delete_photo(self, photo_id): try: cursor = self.conn.cursor() query = """ DELETE FROM Photo WHERE PhotoID = %s; """ cursor.execute(query, (photo_id, )) self.conn.commit() print("*RESULTS*") print("Photo deleted") print("*RESULTS*") except Error as e: print("delete_photo failed") print(e) finally: cursor.close() def execute_query(self, query, args): try: cursor = self.conn.cursor() cursor.execute(query, args) self.conn.commit() print("Added to Database") except Error as e: self.conn.rollback() print(e) finally: cursor.close() def insert_photo(self, speed, film, f_stop, color_bw, resolution, price, date, trans_id, pname, pbdate): query = """INSERT INTO `Photo`(Speed, Film, F-Stop, Color/B&W, Resolution, Price, Date, TransID, PName, PBDate) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""" args = (speed, film, f_stop, color_bw, resolution, price, date, trans_id, pname, pbdate) self.execute_query(query, args) def insert_landscape(self, photo_id, place, country): query = """INSERT INTO `Landscape`(PhotoID, Place, Country) VALUES(%s, %s, %s);""" args = (photo_id, place, country) self.execute_query(query, args) def insert_location(self, place, country, description): query = """INSERT INTO `Location`(Place, Country, Description) VALUES(%s, %s, %s)""" args = (place, country, description) self.execute_query(query, args) def insert_abstract(self, photo_id, comment): query = """INSERT INTO `Abstract`(PhotoID, Comment) VALUES(%s, %s)""" args = (photo_id, comment) self.execute_query(query, args) def insert_models(self, photo_id, mname, mbdate, agency): query = """INSERT INTO `Models`(PhotoID, MName, MBDate, Agency) VALUES(%s, %s, %s, %s);""" args = (photo_id, mname, mbdate, agency) self.execute_query(query, args) def insert_model(self, mname, mbdate, mbio, msex): query = """INSERT INTO `Model`(MName, MBDate, MBio, MSex) VALUES(%s, %s, %s, %s);""" args = (mname, mbdate, mbio, msex) self.execute_query(query, args) def insert_photographer(self, pname, pbdate, pbio, paddress, pnationality): query = """INSERT INTO `Photographer`(PName, PBDate, PBio, PAddress, PNationality) VALUES(%s, %s, %s, %s, %s);""" args = (pname, pbdate, pbio, paddress, pnationality) self.execute_query(query, args) def insert_influences(self, epname, epbdate, rpname, rpbdate): query = """INSERT INTO `Influences`(EPName, EPBDate, RPName, RPBDate) VALUES(%s, %s, %s, %s);""" args = (epname, epbdate, rpname, rpbdate) self.execute_query(query, args) def insert_transaction(self, trans_id, tdate, card_no, card_type, card_exp_date, total_amount, login_name): query = """INSERT INTO `Transaction`(TransID, TDate, CardNo, CardType, CardExpDate, TotalAmount, LoginName) VALUES(%s, %s, %s, %s, %s, %s, %s)""" args = (trans_id, tdate, card_no, card_type, card_exp_date, total_amount, login_name) self.execute_query(query, args) def insert_customer(self, login_name, password, cname, ctype, billing_address, str1, str2, city, state, zip): query = """INSERT INTO `Customer`(LoginName, Password, CName, CType, BillingAddress, Str1, Str2, City, State, Zip) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""" args = (login_name, password, cname, ctype, billing_address, str1, str2, city, state, zip) self.execute_query(query, args)
class DatabaseHandler: module_dir = os.path.dirname(__file__) def __init__(self, forceDBToBeUsed: str = ""): self._forcedDB = forceDBToBeUsed self.conn = MySQLConnection() def __del__(self): print("destruct DatabaseHandler") if self.conn.is_connected(): self.disconnect() ################################################################################### def getFinalzeGameTime(self, division_id: int, round_number: int, round_swissdrawGames: int = 1) -> int: if division_id is None or division_id < 0: raise ValueError("round_number must not be None or division_id") if round_number is None or round_number < 0: raise ValueError("round_number must not be None or negative") if self.conn.is_connected(): round_id = self._getRoundId(division_id, round_number) query = "SELECT round_fixnextgametime FROM round WHERE round.round_number = %s AND round.round_swissdrawGames = %s AND round.division_id = %s LIMIT 1" args = ( round_number, round_swissdrawGames, division_id, ) cursor = self.conn.cursor(dictionary=True) cursor.execute(query, args) row = cursor.fetchone() getFinalzeGameTime = row["round_fixnextgametime"] else: raise NoDatabaseConnection() return getFinalzeGameTime def getRoundNumberToBeOptimized( self, divisionId: int, roundStates: List[GameState] = (RoundState.PREDICTION, RoundState.UNKNOWN) ) -> int: if divisionId is None or divisionId < 0: raise ValueError("divisionId must not be None or negative") round_number = None if self.conn.is_connected(): # Find slotsof next round # Either find predicted round or find lowest round number of slots without games # get round number of predicted round format_strings = ','.join(['\'%s\''] * len(roundStates)) # query = "SELECT round.round_id AS round_id \ # FROM round \ # WHERE round_grouporder = (SELECT MIN(round.round_grouporder) FROM round) \ # AND round.division_id = " + str(divisionId) + " AND round.round_state IN (%s) \ # ORDER BY round.round_number ASC LIMIT 1" % format_strings #FIXME roundStateStringList = "" for x in range(len(roundStates)): if x == 0: roundStateStringList += "%s" else: roundStateStringList += ",%s" query = "SELECT round.round_number AS round_number "\ "FROM round "\ "WHERE round_grouporder = "\ "(SELECT MIN(round.round_grouporder) "\ "FROM round "\ "WHERE round.round_state IN (" + roundStateStringList + ")) "\ "AND round.division_id = %s AND round.round_state IN (" + roundStateStringList + ") "\ "AND round_swissdrawGames = 1 " \ "ORDER BY round.round_number ASC LIMIT 1" #print(query) roundStateArgs = () for x in roundStates: roundStateArgs += (x, ) args = roundStateArgs + (divisionId, ) + roundStateArgs try: cursor = self.conn.cursor(dictionary=True) cursor.execute(query, args) row = cursor.fetchone() if row is not None: round_number = row["round_number"] except Error as e: print(e) finally: cursor.close() else: raise NoDatabaseConnection() return round_number ################################################################################### def getListOfSlotsOfUpcomingRound(self, divisionId: int) -> List[Slot]: if divisionId is None or divisionId < 0: raise ValueError("divisionId must not be None nor negative") round_number = 1000000000 slots = [] swissdraw_game = 1 if self.conn.is_connected(): #Find slotsof next round #Either find predicted round or find lowest round number of slots without games #get round number of predicted round query = "SELECT round.round_number AS round_number, round.round_state AS round_state \ FROM slot \ INNER JOIN round ON round.round_id = slot.round_id \ WHERE round.division_id = %s AND round.round_swissdrawGames = %s\ ORDER BY round.round_number ASC" args = ( divisionId, swissdraw_game, ) try: cursor = self.conn.cursor(dictionary=True) cursor.execute(query, args) row = cursor.fetchone() while row is not None: if row["round_state"] != RoundState.FINAL_PREDICTION and row[ "round_state"] != RoundState.PUBLISHED: round_number = row["round_number"] if row[ "round_number"] < round_number else round_number if round_number < 0: raise ValueError("round_number was " + str(round_number) + " but should have been >= 0") row = cursor.fetchone() except Error as e: print(e) # Query to get Slots From slot query = "SELECT slot.slot_start AS start, slot.slot_end AS end, slot.location_id, slot.slot_id, \ outer_round.round_number AS round_number \ FROM slot \ INNER JOIN round outer_round ON outer_round.round_id = slot.round_id \ WHERE outer_round.division_id = %s \ AND outer_round.round_number = %s \ AND outer_round.round_swissdrawGames = %s" args = ( divisionId, round_number, swissdraw_game, ) try: cursor = self.conn.cursor(dictionary=True) cursor.execute(query, args) row = cursor.fetchone() while row is not None: slot = Slot(row["start"], row["end"], row["location_id"], row["slot_id"], row["round_number"]) slots.append(slot) row = cursor.fetchone() except Error as e: print(e) finally: cursor.close() else: raise NoDatabaseConnection() return slots ################################################################################### def getListOfGames(self, divisionId: int, gameStates: List[GameState], locationId: int = None) -> List[Game]: if divisionId == None or divisionId < 0: raise ValueError("divisionId must not be None nor negative") if locationId == None: locationId = -1 if len(gameStates) <= 0: gameStates = [ GameState.NOT_YET_STARTED, GameState.COMPLETED, GameState.RUNNING ] games = [] if self.conn.is_connected(): gameStateStringList = "" for x in range(len(gameStates)): if x == 0: gameStateStringList += "%s" else: gameStateStringList += ",%s" gameStateArgs = () for gameState in gameStates: gameStateArgs += (gameState, ) format_strings = ','.join(['\'%s\''] * len(gameStates)) query = "SELECT team1.team_name AS team1_name, team1.team_acronym AS team1_acronym, team1.team_id AS team1_id, team1.team_seed AS team1_seed, "\ "team2.team_name AS team2_name, team2.team_acronym AS team2_acronym, team2.team_id AS team2_id, team2.team_seed AS team2_seed, "\ "matchup.matchup_id AS matchup_id, matchup.matchup_team1_timeouts AS team1_timeouts, "\ "matchup.matchup_team1_score AS team1_score, matchup.matchup_team2_timeouts AS team2_timeouts, "\ "matchup.matchup_team2_score AS team2_score, slot.slot_id AS slot_id, "\ "slot.slot_start AS slot_start, slot.slot_end AS slot_end, slot.location_id AS location_id, "\ "round.round_number AS round_number, game.game_id AS game_id "\ "FROM game "\ "INNER JOIN matchup ON game.matchup_id = matchup.matchup_id "\ "INNER JOIN team AS team1 ON matchup.matchup_team1_id = team1.team_id "\ "INNER JOIN team AS team2 ON matchup.matchup_team2_id = team2.team_id "\ "INNER JOIN slot ON game.slot_id = slot.slot_id "\ "INNER JOIN round ON slot.round_id = round.round_id "\ "WHERE game.game_state IN ("+gameStateStringList+") "\ "AND round.division_id = %s" args = gameStateArgs + (divisionId, ) #print(query) if locationId >= 0: query += " AND slot.location_id = %s" args += (locationId, ) try: cursor = self.conn.cursor(dictionary=True) cursor.execute(query, args) row = cursor.fetchone() while row is not None: #print(row) team1 = Team(row["team1_name"], row["team1_acronym"], row["team1_id"], row["team1_seed"]) team2 = Team(row["team2_name"], row["team2_acronym"], row["team2_id"], row["team2_seed"]) matchup = MatchUp(team1, team2, row["matchup_id"]) slot = Slot(row["slot_start"], row["slot_end"], row["location_id"], row["slot_id"], row["round_number"]) result = Result(row["matchup_id"], row["team1_score"], row["team2_score"], row["team1_timeouts"], row["team2_timeouts"]) game = Game(matchup, result, slot, row["game_id"]) games.append(game) row = cursor.fetchone() except Error as e: print(e) finally: cursor.close() else: raise NoDatabaseConnection() return games ###################################################################################### def getListOfAllTeams(self, divisionId: int) -> List[Team]: if divisionId is None or divisionId < 0: raise ValueError("divisionId must not be None nor negative") teams = [] if self.conn.is_connected(): query = "SELECT team_id, team_name, team_acronym, team_seed FROM team " query += "WHERE division_id = %s " args = (divisionId, ) try: cursor = self.conn.cursor(dictionary=True) if args is None: cursor.execute(query) else: cursor.execute(query, args) row = cursor.fetchone() while row is not None: team = Team(row["team_name"], row["team_acronym"], row["team_id"], row["team_seed"]) teams.append(team) row = cursor.fetchone() except Error as e: print(e) finally: cursor.close() else: raise NoDatabaseConnection() return teams def getSwissDrawDivisions(self) -> List[Division]: divisions = [] if self.conn.is_connected(): query = "SELECT division_id, division_name, division_acronym FROM division WHERE division_optimized = 1" try: cursor = self.conn.cursor(dictionary=True) cursor.execute(query) row = cursor.fetchone() while row is not None: division = Division(row["division_id"], row["division_name"], row["division_acronym"]) divisions.append(division) row = cursor.fetchone() except Error as e: print(e) finally: cursor.close() else: raise NoDatabaseConnection() return divisions def getListOfLocations(self) -> List[Location]: locations = [] if self.conn.is_connected(): query = "SELECT location_id, location_name, location_description, location_color FROM location" try: cursor = self.conn.cursor(dictionary=True) cursor.execute(query) row = cursor.fetchone() while row is not None: #print(row) location = Location(row["location_id"], row["location_name"], row["location_description"], row["location_color"]) locations.append(location) row = cursor.fetchone() except Error as e: print(e) finally: cursor.close() else: raise NoDatabaseConnection() return locations def insertNextGame(self, game: Game, gamestate: GameState, debug: int = 0) -> bool: status = True matchupQuery = "INSERT INTO matchup(matchup_team1_id ,matchup_team2_id) " \ "VALUES(%s,%s)" #gameQuery = "INSERT INTO game (matchup_id ,slot_id, game_state) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE game_state =%s" gameQuery = "INSERT INTO game (matchup_id ,slot_id, game_state) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE game_state =%s" try: self.conn.autocommit = False cursor = self.conn.cursor() matchupArgs = (game.matchup.first.teamId, game.matchup.second.teamId) cursor.execute(matchupQuery, matchupArgs) gameArgs = ( cursor.lastrowid, game.slot.slotId, gamestate, gamestate, ) cursor.execute(gameQuery, gameArgs) #if cursor.lastrowid: # print('last insert id', cursor.lastrowid) #else: # print('last insert id not found') if debug == 0: self.conn.commit() else: print("Rollback") self.conn.rollback() except Error as error: self.conn.rollback() status = False print(error) finally: cursor.close() return status def insertNextGames(self, games: [Game], gamestate: GameState, debug: int = 0) -> bool: status = True savecounter = 0 matchupQuery = "INSERT INTO matchup(matchup_team1_id ,matchup_team2_id) " \ "VALUES(%s,%s)" # gameQuery = "REPLACE INTO game(matchup_id ,slot_id, game_state) " \ # "VALUES(%s,%s,%s) " gameQuery = "INSERT INTO game (matchup_id ,slot_id, game_state) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE game_state =%s" try: for game in games: self.conn.autocommit = False cursor = self.conn.cursor() matchupArgs = (game.matchup.first.teamId, game.matchup.second.teamId) cursor.execute(matchupQuery, matchupArgs) if not cursor.lastrowid: raise ValueError("no last inserted id found") gameArgs = ( cursor.lastrowid, game.slot.slotId, gamestate, ) gameArgs = ( cursor.lastrowid, game.slot.slotId, gamestate, gamestate, ) cursor.execute(gameQuery, gameArgs) savecounter += 1 if debug == 0: self.conn.commit() else: print(savecounter) print("Rollback") self.conn.rollback() except Error as error: self.conn.rollback() status = False print(error) finally: if self.conn.is_connected(): cursor.close() return status def insertRanking(self, ranked_teamlist: [Team], round_number: int, divisionId: int, debug: int = 0): if ranked_teamlist is None or len(ranked_teamlist) <= 0: raise ValueError("ranked_teamlist must not be None nor negative") if round_number is None or round_number < 0: raise ValueError("round_number must not be None nor negative") status = True if self.conn.is_connected(): round_id = self._getRoundId(divisionId, round_number) #queryRanking = "REPLACE INTO ranking (team_id ,ranking_rank, round_id, division_id) " \ # "VALUES(%s, %s, %s, %s) " queryRanking = "INSERT INTO ranking (team_id ,ranking_rank, round_id, division_id) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE ranking_rank=%s" rankCounter = 1 try: for team in ranked_teamlist: self.conn.autocommit = False cursor = self.conn.cursor() argsRanking = (team.teamId, rankCounter, round_id, divisionId, rankCounter) cursor.execute(queryRanking, argsRanking) rankCounter += 1 if debug == 0: self.conn.commit() else: print(rankCounter) print("Rollback") self.conn.rollback() except Error as error: self.conn.rollback() status = False raise error finally: if self.conn.is_connected(): cursor.close() else: raise NoDatabaseConnection() return status def setRoundState(self, round_number: int, division_id: int, round_state: RoundState, debug: int = 0) -> None: if round_number is None or round_number < 0: raise ValueError("round_number must not be None or negative") if self.conn.is_connected(): round_id = self._getRoundId(division_id, round_number) query = "UPDATE round "\ "SET round_state = %s "\ "WHERE round_id = %s" try: cursor = self.conn.cursor(dictionary=True) args = (round_state, round_id) cursor.execute(query, args) if debug == 0: self.conn.commit() else: print("Rollback") self.conn.rollback() except Error as error: self.conn.rollback() raise error finally: if self.conn.is_connected(): cursor.close() else: raise NoDatabaseConnection() def setResult(self, matchup_id: int, resultA: int, resultB: int, debug: int = 0) -> None: if matchup_id is None or matchup_id < 0: raise ValueError("matchup_id must not be None or negative") if resultA is None or resultA < 0: raise ValueError("resultA must not be None or negative") if resultB is None or resultB < 0: raise ValueError("resultB must not be None or negative") if not self.conn.is_connected(): raise NoDatabaseConnection try: query = "UPDATE matchup SET matchup_team1_score = %s, matchup_team2_score = %s WHERE matchup_id = %s" args = (resultA, resultB, matchup_id) cursor = self.conn.cursor() cursor.execute(query, args) if debug == 0: self.conn.commit() except Error as error: self.conn.rollback() raise error finally: if self.conn.is_connected(): cursor.close() def setGameState(self, game_id: int, game_state: int, debug: int = 0) -> None: if game_id is None or game_id < 0: raise ValueError("game_id must not be None or negative") if game_state != GameState.NOT_YET_STARTED and game_state != GameState.RUNNING and game_state != GameState.COMPLETED: raise ValueError("supplied game_state (" + str(game_state) + ") is not a known GameState") try: query = "UPDATE game SET game_state = %s WHERE game_id = %s" args = (game_state, game_id) cursor = self.conn.cursor() cursor.execute(query, args) if debug == 0: self.conn.commit() except Error as error: self.conn.rollback() raise error finally: if self.conn.is_connected(): cursor.close() ####################################################################################### def connect(self) -> None: """ Connect to MySQL database """ #read config from file# db_config = self.read_db_config() if self._forcedDB != "": db_config["database"] = self._forcedDB try: conn = MySQLConnection(**db_config, charset='utf8') print('Connecting to MySQL database...') if conn.is_connected(): self.conn = conn print('Database connection esteblished.') else: print('Database connection failed.') except Error as e: print('Database connection failed.') print(e) finally: print("connection attampt done") def disconnect(self) -> bool: """ Disconnect from MySQL database """ if self.conn.is_connected(): self.conn.close() print('Database connection closed.') return True else: print('No active database connection to be closed.') return False @staticmethod def read_db_config(filename=os.path.join(module_dir, 'includes/config.ini'), section='mysql') -> dict: """ Read database configuration file and return a dictionary object :param filename: name of the configuration file :param section: section of database configuration :return: a dictionary of database parameters """ # create parser and read ini configuration file parser = ConfigParser() parser.read(filename) # get section, default to mysql db = {} if parser.has_section(section): items = parser.items(section) for item in items: db[item[0]] = item[1] else: raise Exception('{0} not found in the {1} file'.format( section, filename)) return db def _getRoundId(self, division_id: int, round_number: int): if round_number is None or round_number < 0: raise ValueError("round_number must not be None nor negative") if self.conn.is_connected(): queryRoundId = "SELECT round_id FROM round WHERE round.round_number = %s AND round.division_id = %s LIMIT 1" try: cursor = self.conn.cursor(dictionary=True) argsRoundId = (round_number, division_id) cursor.execute(queryRoundId, argsRoundId) row = cursor.fetchone() round_id = row["round_id"] except Error as error: self.conn.rollback() raise error finally: if self.conn.is_connected(): cursor.close() else: raise NoDatabaseConnection() return round_id
class DatabaseDAO: def __init__(self, user): self.connection_object = None config = self.__read_db_config()[user] if config is not None: try: self.connection_object = MySQLConnection( host=config["hostname"], user=config["username"], passwd=config["password"], db=config["database"], auth_plugin="mysql_native_password", ) if self.connection_object.is_connected(): print("Connection: Successful.") else: print("Connection: Failure.") except Error as err: print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) else: print("No configuration data.") def __enter__(self): return self def __read_db_config(self, filename="dbconfig.json"): config_data = None with open(filename, "r") as file: config_data = load(file) return config_data def populate_parking_lot(self, arg): if self.connection_object is None: print("No connection") return if type(arg) is not tuple: print("Invalid input, must be a tuple") return select_parking_lot_capacity = """SELECT capacity_all, capacity_charging from ParkingLot WHERE name = %s""" insert_parking_spaces = """ INSERT INTO ParkingSpace(space_id, lot_id, space_type, sensor_id, is_occupied, hourly_tariff) VALUES (%s, (SELECT lot_id FROM ParkingLot LIMIT 1), %s, %s, 0, %s) """ insert_parking_lot = """INSERT INTO `ParkingLot`(`lot_id`, `name`, `location`, `capacity_all`, `capacity_charging`) VALUES (UUID_TO_BIN(UUID()), %s,"Nettelbosje 2, 9747 AD Groningen", 60, 10)""" cursor = self.connection_object.cursor(named_tuple=True) try: cursor.execute(insert_parking_lot, arg) cursor.execute(select_parking_lot_capacity, arg) row = cursor.fetchone() capacity_all = row.capacity_all capacity_charging = row.capacity_charging ParkingSpace = namedtuple( "ParkingSpace", "space_id space_type sensor_id hourly_tariff") for i in range(capacity_all): if i < capacity_all - capacity_charging: space_data = ParkingSpace( space_id=i, space_type="non_charging", sensor_id=i, hourly_tariff=1.20, ) cursor.execute(insert_parking_spaces, space_data) else: space_data = ParkingSpace( space_id=i, space_type="charging", sensor_id=i, hourly_tariff=1.32, ) cursor.execute(insert_parking_spaces, space_data) print("Executed") self.connection_object.commit() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def register_new_car(self, args): if self.connection_object is None: print("No connection") return """ The parameter - args, must be a tuple which contains the necessary information for registration of a Car: args[0] - license_plate args[1] - owner_id args[2] - brand_name args[3] - fuel_type """ if type(args) is not tuple: print("Invalid input, must be a tuple") return cursor = self.connection_object.cursor() duplication_check_query = """SELECT EXISTS(SELECT license_plate FROM Car WHERE license_plate =%s)""" insert_query = """INSERT INTO Car (license_plate, owner_id, brand_name, fuel_type) VALUES(%s, %s, %s, %s)""" try: cursor.execute(duplication_check_query, (args[0], )) if all(cursor.fetchone()): print( "Warning: There is an already registered car with the entered license plate." ) cursor.close() else: cursor.execute(insert_query, args) print( cursor.rowcount, "row/rows inserted successfully into Car table.", ) self.connection_object.commit() cursor.close() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def register_new_car_owner(self, args): if self.connection_object is None: print("No connection") return """ The parameter - args, must be a tuple which contains the necessary information for registration of a Car Owner: args[0] - owner_id args[1] - customer_type args[2] - student_employee_code args[3] - discount_rate args[4] - first_name args[5] - surname args[6] - tel_number args[7] - email args[8] - payment_method """ if type(args) is not tuple: print("Invalid input, must be a tuple") return cursor = self.connection_object.cursor() insert_query = """INSERT INTO CarOwner (owner_id, customer_type, student_employee_code, discount_rate, first_name, surname, tel_number, email, payment_method) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)""" try: cursor.execute(insert_query, args) print( cursor.rowcount, "row/rows inserted successfully into CarOwner table.", ) self.connection_object.commit() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def create_new_car_record(self, args): if self.connection_object is None: print("No connection") return """ The parameter - args, must be a tuple which contains the necessary information for a Car Record: args[0] - record_id args[1] - license_plate args[2] - space_id args[3] - check_in """ if type(args) is not tuple: print("Invalid input, must be a tuple") return cursor = self.connection_object.cursor() insert_query = """INSERT INTO CarRecord (record_id, license_plate, space_id, check_in, is_paid) VALUES(%s, %s, %s, %s, 0)""" try: cursor.execute(insert_query, args) print( cursor.rowcount, "row/rows inserted successfully into CarRecord table.", ) self.connection_object.commit() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def occupy_parking_space(self, space_id): if self.connection_object is None: print("No connection") return arg = (space_id, ) cursor = self.connection_object.cursor() update_query = ( """UPDATE ParkingSpace SET is_occupied = 1 WHERE space_id = %s""") try: cursor.execute(update_query, arg) self.connection_object.commit() print("Succesfully occupied parking space") except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def get_parking_spaces_per_lot(self, is_occupied_field=0): if self.connection_object is None: print("No connection") return cursor = self.connection_object.cursor(named_tuple=True) select_query = """SELECT name, COUNT(IF(space_type = 'non_charging', 1, NULL)) 'non_charging', COUNT(IF(space_type = 'charging', 1, NULL)) 'charging' FROM ( SELECT pl.lot_id, pl.name, pl.location, pl.capacity_all, pl.capacity_charging, ps.space_id, ps.space_type, ps.is_occupied FROM ParkingLot pl INNER JOIN ParkingSpace ps ON pl.lot_id = ps.lot_id) pl_ps WHERE pl_ps.is_occupied = %s GROUP BY name """ try: cursor.execute(select_query, (is_occupied_field, )) rows = cursor.fetchall() result = [] for index, row in enumerate(rows): if is_occupied_field == 0: print( "Parking Lot - %s, has %d non-charging and %d charging, free spaces" % (row.name, row.non_charging, row.charging)) else: print( "Parking Lot - %s, %d non-charging and %d charging are occupied" % (row.name, row.non_charging, row.charging)) result.insert(index, [row.name, row.non_charging, row.charging]) return result except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def get_random_parking_space(self, fuel_type): if self.connection_object is None: print("No connection") return free_spaces = None if fuel_type == "Electric": arg = ("charging", ) else: arg = ("non_charging", ) cursor = self.connection_object.cursor() get_random_space_query = """SELECT space_id FROM ParkingSpace WHERE is_occupied = 0 AND space_type = %s""" try: cursor.execute(get_random_space_query, arg) free_spaces = cursor.fetchall() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() return choice(free_spaces) def get_currently_parked_cars(self, with_owner=False): if self.connection_object is None: print("No connection") return checked_in_records = None cursor = self.connection_object.cursor() if with_owner: select_query = """SELECT rc.license_plate, rc.brand_name, o.first_name, o.surname, rc.space_id, rc.check_in, o.discount_rate FROM ( SELECT r.license_plate, c.brand_name, c.owner_id, r.space_id, r.check_in FROM CarRecord r INNER JOIN Car c ON r.license_plate = c.license_plate AND r.check_out is NULL ) rc LEFT JOIN CarOwner o USING(owner_id)""" else: select_query = """ SELECT r.license_plate, c.brand_name, c.fuel_type FROM CarRecord r INNER JOIN Car c ON r.license_plate = c.license_plate AND r.check_out is NULL """ try: cursor.execute(select_query) checked_in_records = cursor.fetchall() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() return checked_in_records def check_out_car(self, license_plate, space_id, check_out, discount_rate): if self.connection_object is None: print("No connection") return cursor = self.connection_object.cursor() update_parking_space_query = ( """UPDATE ParkingSpace SET is_occupied = 0 WHERE space_id = %s""") update_car_record_query = ( """UPDATE CarRecord SET check_out = %s WHERE license_plate = %s""") update_discount_query = """UPDATE CarOwner SET discount_rate = %s WHERE owner_id = (SELECT owner_id FROM Car WHERE license_plate = %s)""" try: cursor.execute(update_parking_space_query, (space_id, )) cursor.execute(update_car_record_query, (check_out, license_plate)) cursor.execute(update_discount_query, (discount_rate, license_plate)) self.connection_object.commit() cursor.close() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def get_user_invoice(self, args): if self.connection_object is None: print("No connection") return """ The parameter - args, must be a tuple which contains the necessary information: args[0] - owner_id args[1] - month """ if type(args) is not tuple: print("Invalid input, must be a tuple") return owner_id = args[0] cursor = self.connection_object.cursor(named_tuple=True) select_query = """SELECT license_plate, check_in, check_out, total_time, total_time * ps.hourly_tariff AS parking_cost, is_paid FROM ( SELECT co.license_plate, r.check_in, r.check_out, ROUND( ( TIME_TO_SEC( TIMEDIFF(r.check_out, r.check_in) ) / 3600 ), 2 ) AS total_time, r.is_paid, r.space_id FROM ( SELECT Car.license_plate, CarOwner.owner_id, CarOwner.first_name, CarOwner.surname, CarOwner.student_employee_code, CarOwner.discount_rate, CarOwner.payment_method FROM Car INNER JOIN CarOwner ON Car.owner_id = CarOwner.owner_id AND CarOwner.owner_id = %s ) co INNER JOIN CarRecord r ON co.license_plate = r.license_plate AND MONTH(r.check_in) = %s ) cor INNER JOIN ParkingSpace ps USING(space_id)""" try: cursor.execute(select_query, args) rows = cursor.fetchall() result = [] print("Invoice for owner: %s" % (owner_id)) for index, row in enumerate(rows): print( "License Plate: %s, Check-in: %s, Check-out: %s, Total time: %4.2f, Cost: %4.2f" % ( row.license_plate, row.check_in, row.check_out, row.total_time, row.parking_cost, )) result.insert( index, [ owner_id, row.license_plate, row.check_in, row.check_out, row.total_time, row.parking_cost, ], ) return result except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def get_parking_spaces(self): if self.connection_object is None: print("No connection") return parking_spaces = None cursor = self.connection_object.cursor() select_query = ( """SELECT space_id, is_occupied, space_type FROM ParkingSpace""") try: cursor.execute(select_query) parking_spaces = cursor.fetchall() except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() return parking_spaces def sensor_alert(self): if self.connection_object is None: print("No connection") return cursor = self.connection_object.cursor(named_tuple=True) select_query = """SELECT COUNT(IF(is_occupied = 1, 1, NULL)) 'detected', COUNT(IF(check_out IS NULL, 1, NULL)) 'parked' FROM ( SELECT r.check_out, ps.is_occupied FROM CarRecord r INNER JOIN ParkingSpace ps USING(space_id)) r_ps """ try: cursor.execute(select_query) data = cursor.fetchone() if data.detected != data.parked: return True else: return False except Error as err: self.connection_object.rollback() print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def get_number_cars_group_by(self, grouped_by): if self.connection_object is None: print("No connection") return """ The parameter - grouped_by, must specify whether the function should return the amount of parked card per Hour, Day, Week, or Year: grouped_by = ["hour", "day", "week", "year"] """ if grouped_by == "hour": select_query = """SELECT YEAR(check_in) AS year, MONTH(check_in) AS month, DAY(check_in) AS day, HOUR(check_in) AS hour, COUNT(*) AS n_cars FROM CarRecord GROUP BY year, month, day, hour ORDER BY year, month, day, hour ASC""" elif grouped_by == "day": select_query = """SELECT YEAR(check_in) AS year, MONTH(check_in) AS month, DAY(check_in) AS day, COUNT(*) AS n_cars FROM CarRecord GROUP BY year, month, day ORDER BY year, month, day ASC""" elif grouped_by == "month": select_query = """SELECT YEAR(check_in) AS year, MONTH(check_in) AS month, COUNT(*) AS n_cars FROM CarRecord GROUP BY year, month ORDER BY year, month ASC""" elif grouped_by == "year": select_query = """SELECT YEAR(check_in) AS year, COUNT(*) AS n_cars FROM CarRecord GROUP BY year ORDER BY year ASC""" cursor = self.connection_object.cursor(named_tuple=True) try: cursor.execute(select_query) rows = cursor.fetchall() return rows except Error as err: print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def get_cars_date_range(self, start_date, end_date): if self.connection_object is None: print("No connection") return select_query = """SELECT c.owner_id, c.license_plate, c.brand_name, c.fuel_type FROM Car c INNER JOIN CarRecord r ON c.license_plate = r.license_plate AND r.check_in >= %s AND r.check_in <= %s""" cursor = self.connection_object.cursor(named_tuple=True) args = (start_date, end_date) try: cursor.execute(select_query, args) rows = cursor.fetchall() return rows except Error as err: print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def get_unpaid_records(self): if self.connection_object is None: print("No connection") return select_query = """SELECT * FROM CarRecord WHERE is_paid = 0""" cursor = self.connection_object.cursor(named_tuple=True) try: cursor.execute(select_query) rows = cursor.fetchall() return rows except Error as err: print("Error Code:", err.errno) print("SQLSTATE:", err.sqlstate) print("Message:", err.msg) finally: cursor.close() def close(self): if (self.connection_object is not None and self.connection_object.is_connected): self.connection_object.close() self.connection_object = None print("Connection: Closed") def __exit__(self, exc_type, exc_value, traceback): if (self.connection_object is not None and self.connection_object.is_connected): self.connection_object.close() self.connection_object = None print("Connection: Closed")
def __desconectar(conexao: MySQL.MySQLConnection, rollback=False): if rollback: conexao.rollback() else: conexao.commit() conexao.close()