def find_by_id(self, id):
"""
Validate ID type is int
Returns Author if exists
:param id: int
:return: Author | None
"""
# Validate type
if not TypeUtils.is_type(id, var_type=int):
print(ErrorMsgUtils.type_error(id, var_type=int))
return
sql = f"SELECT * FROM AUTHOR WHERE ID = {id}"
# Execution
try:
self.cursor.execute(sql)
results = self.cursor.fetchone()
if not results:
print(
ErrorMsgUtils.does_not_exists(table_name='Author', var=id))
return
# Return results
return Author(id=results[0],
first_name=results[1],
last_name=results[2])
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def update_author(self, author):
"""
Validates author type is Author
:param author: Author
:return: Updated Author | None
"""
# Validate type
if not TypeUtils.is_type(author, var_type=Author):
print(ErrorMsgUtils.type_error(author, var_type=Author))
return
sql = f"UPDATE AUTHOR SET " \
f"FIRST_NAME = '{author.first_name}', " \
f"LAST_NAME = '{author.last_name}' " \
f"WHERE ID = {author.id}"
# Execution
try:
self.cursor.execute(sql)
self.db.commit()
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
return
else:
# Return updated results
return self.find_by_id(author.id)
def find_by_id(self, id):
"""
Validates ID type is int
Returns Book by ID if exists
:param id: int
:return: Book | None
"""
# Validates type
if not TypeUtils.is_type(id, var_type=int):
print(ErrorMsgUtils.type_error(id, var_type=int))
return
# Execution
sql = f"SELECT * FROM BOOKS WHERE ID = {id}"
try:
self.cursor.execute(sql)
results = self.cursor.fetchone()
if not results:
print(ErrorMsgUtils.does_not_exists(table_name='Book', var=id))
return None
# Return results
book_by_id = Book(
id=results[0],
name=results[1],
price=results[2],
author_id=results[3],
)
return book_by_id
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def remove_author(self, id):
"""
Validates id type is int
Validates existence before removing
Removing & returns the removed row
:param id:
:return:
"""
# Validates type
if not TypeUtils.is_type(id, var_type=int):
print(ErrorMsgUtils.type_error(id, var_type=int))
return
# Validate existence
author = self.find_by_id(id)
if author is None:
return
sql = f"DELETE FROM AUTHOR WHERE ID = {id}"
# Execution
try:
self.cursor.execute(sql)
self.db.commit()
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
else:
return author
def remove_book(self, id):
"""
Validates ID type is int
Validates existence before removing
Delete Book and returns it
:param id: int
:return: Deleted Book | None
"""
# Validates type
if not TypeUtils.is_type(id, var_type=int):
print(ErrorMsgUtils.type_error(id, var_type=int))
return
# Validates existence
book = self.find_by_id(id)
if not book:
return
# Execution
sql = f"DELETE FROM BOOKS WHERE ID = {id}"
try:
self.cursor.execute(sql)
self.db.commit()
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
else:
# Returns Deleted Book
print(self.cursor.rowcount, "was deleted.")
return book
def find_authors_books(self, author_id):
"""
Validates author_id type is int
Returns all authors books
:param author_id: int
:return: List of Books | None
"""
# Validates type
if not TypeUtils.is_type(author_id, var_type=int):
print(ErrorMsgUtils.type_error(author_id, var_type=int))
return
sql = f"SELECT BOOKS.* FROM AUTHOR, BOOKS " \
f"WHERE AUTHOR.ID = {author_id} AND BOOKS.AUTHOR_ID = {author_id}"
# Execution
try:
self.cursor.execute(sql)
results = self.cursor.fetchall()
# No Data
if not results:
print(ErrorMsgUtils.no_data_available())
return
# Return results
return results
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def find_by_name(self, name):
"""
Validates Name type is str
:param name: str
:return: Book | None
"""
# Validates type
if not TypeUtils.is_type(name, var_type=str):
print(ErrorMsgUtils.type_error(name, var_type=str))
return
# Execution
sql = f"SELECT * FROM BOOKS WHERE NAME = '{name}'"
try:
self.cursor.execute(sql)
results = self.cursor.fetchone()
if not results:
print(
ErrorMsgUtils.does_not_exists(table_name='Book', var=name))
return None
# Returns results
book_by_name = Book(id=results[0],
name=results[1],
price=results[2],
author_id=results[3])
return book_by_name
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def name(self, new_name):
# Validates type
if not TypeUtils.is_type(new_name, var_type=str):
print(ErrorMsgUtils.type_error(new_name, var_type=str))
return
# Validates legal value
if not new_name:
print(ErrorMsgUtils.illegal_value(new_name))
return
# Set value
self.__name = new_name
def id(self, new_id):
# Validates type
if not TypeUtils.is_type(new_id, var_type=int):
print(ErrorMsgUtils.type_error(new_id, var_type=int))
return
# Validate legal value
if new_id < 1:
print(ErrorMsgUtils.illegal_value(new_id))
return
# Set value
self.__id = new_id
def price(self, new_price):
# Validates type
if not TypeUtils.is_type_or(new_price, var_type1=int, var_type2=float):
print(ErrorMsgUtils.none_of_type(int, float, var=new_price))
return
# Validates legal value
if new_price <= 0:
print(ErrorMsgUtils.illegal_value(new_price))
return
# Set value
self.__price = new_price
def author_id(self, new_author):
# Validates type
if not TypeUtils.is_type(new_author, var_type=int):
print(ErrorMsgUtils.type_error(new_author, var_type=int))
return
# Set value
self.__author_id = new_author
def find_all(self):
"""
Returns all authors or none
:return: List of Authors | None
"""
sql = "SELECT * FROM AUTHOR"
try:
self.cursor.execute(sql)
results = self.cursor.fetchall()
# No Data case
if not results:
print(ErrorMsgUtils.no_data_available())
return
# Return results
return results
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def find_by_name(self, first_name, last_name):
"""
Validates first_name & last_name are str
Returns Author if exists
:param first_name: str
:param last_name: str
:return: Author | None
"""
# Validates type
if not TypeUtils.all_of_type(first_name, last_name, var_type=str):
print(ErrorMsgUtils.type_error(first_name, last_name,
var_type=str))
return
# Validate legal value
if not first_name:
print(ErrorMsgUtils.illegal_value(first_name))
if not last_name:
print(ErrorMsgUtils.illegal_value(last_name))
sql = f"SELECT * FROM AUTHOR WHERE FIRST_NAME = '{first_name}' AND LAST_NAME = '{last_name}'"
# Execution
try:
self.cursor.execute(sql)
results = self.cursor.fetchone()
if not results:
print(
ErrorMsgUtils.does_not_exists(
table_name='Author', var=f"{first_name}, {last_name}"))
return
# Return results
return Author(id=results[0],
first_name=results[1],
last_name=results[2])
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def create_book(self, name, price, author):
"""
Validates name & price & author type
Validates Book is not exists by name
Creates a new book into the BOOKS's table
:param name: str
:param price: int | float
:param author: Author
:return: Book | None
"""
# Validates name
if not TypeUtils.is_type(name, var_type=str):
print(ErrorMsgUtils.type_error(name, var_type=str))
return
# Validates price
if not TypeUtils.is_type_or(price, var_type1=int, var_type2=float):
print(ErrorMsgUtils.none_of_type(int, float, var=print()))
return
# Validates author
if not TypeUtils.is_type(author, var_type=Author):
print(ErrorMsgUtils.type_error(author, var_type=Author))
return
# Validates existence
book_to_check = self.find_by_name(name)
if book_to_check is not None:
print(ErrorMsgUtils.already_exists(name))
return
# Validates author existence
author_db = AuthorDBDAO(db=self.db)
author_by_id = author_db.find_by_id(author.id)
if author_by_id is None:
print(
ErrorMsgUtils.does_not_exists(table_name='Author',
var=author.id))
return
# SQL & Execution
sql = f"INSERT INTO BOOKS (NAME, PRICE, AUTHOR_ID) VALUES ('{name}', {price}, {author.id})"
try:
self.cursor.execute(sql)
self.db.commit()
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
# Return None
return None
else:
print(self.cursor.rowcount, "was inserted.")
print("ID: ", self.cursor.lastrowid)
# Return the Book
return Book(name=name,
price=price,
id=self.cursor.lastrowid,
author_id=author.id)
def create_author(self, first_name, last_name):
"""
Validates type first_name & last_name is str
Validates author does not exists by first & last name
:param first_name: str
:param last_name: str
:return: Author | None
"""
# Validates type
if not TypeUtils.all_of_type(first_name, last_name, var_type=str):
print(ErrorMsgUtils.type_error(first_name, last_name,
var_type=str))
return
# Validate existence
author_to_check = self.find_by_name(first_name, last_name)
if author_to_check is not None:
print(ErrorMsgUtils.already_exists(f"{first_name}-{last_name}"))
return
sql = f"INSERT INTO AUTHOR(FIRST_NAME, LAST_NAME) VALUES ('{first_name}', '{last_name}')"
# Execution
try:
self.cursor.execute(sql)
self.db.commit()
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
else:
print(self.cursor.rowcount, "was inserted.")
print("ID: ", self.cursor.lastrowid)
# Return the Book
return Author(first_name=first_name,
last_name=last_name,
id=self.cursor.lastrowid)
def update_book(self, book):
"""
Validates Book type is Book
:param book: Book
:return: Updated Book | None
"""
# Validates type
if not TypeUtils.is_type(book, var_type=Book):
print(ErrorMsgUtils.type_error(book, var_type=Book))
return
# Validate existence
book_to_update = self.find_by_id(book.id)
if book_to_update is None:
print(ErrorMsgUtils.does_not_exists(table_name='Book',
var=book.id))
return
# Execution
sql = f"UPDATE BOOKS SET NAME = '{book.name}', " \
f"PRICE = '{book.price}' " \
f"WHERE ID = {book.id}"
try:
self.cursor.execute(sql)
self.db.commit()
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
return None
else:
# Return updated Book
print(self.cursor.rowcount, "was updated.")
updated_book = self.find_by_id(book.id)
return updated_book
def find_all(self):
"""
Returns all Books
:param self:
:return: List of Books
"""
try:
# Execution
sql = "SELECT * FROM BOOKS"
self.cursor.execute(sql)
results = self.cursor.fetchall()
# No Data
if not results:
print(ErrorMsgUtils.no_data_available())
return
# Return results
return results
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def create_table(self):
"""
Create Books Table
:return:
"""
try:
self.cursor.execute("CREATE TABLE AUTHOR "
"(ID INT AUTO_INCREMENT PRIMARY KEY, "
"FIRST_NAME VARCHAR(255), "
"LAST_NAME VARCHAR(255)"
")")
# Success
print("Table Author was created successfully")
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))
def create_table(self):
"""
Create Books Table
:return:
"""
try:
self.cursor.execute("CREATE TABLE BOOKS "
"(ID INT AUTO_INCREMENT, "
"NAME VARCHAR(255), "
"PRICE DOUBLE, "
"AUTHOR_ID INT, "
"PRIMARY KEY (ID), "
"FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(ID)"
")")
# Success
print("Table Books was created successfully")
# Error
except mysql.connector.Error as error:
print(ErrorMsgUtils.display_error(error))