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