# import the module that supports SQLite databases
import sqlite3
# importing the closing() function
from contextlib import closing
import datetime as dt

class Book:
def __init__(self, title=None, year=None, b_id=None):
   """
         init a book object
   :param title: title of the book
   :param year: year of the book
   :param b_id: id of the book
   """
   self.title = title
   self.year = year
   self.b_id = b_id
   self.available = 0
   pass

def is_exist(self, conn) -> bool:
   """
         find the book in the library
   :param conn: connection to the database
   :return: true if the book is found
   """
   exist = False
   with closing(conn.cursor()) as c:
         query = '''select * from book where "title" = ? and "year" = ?'''
         c.execute(query, (self.title, int(self.year)))
         row = c.fetchall()
         if len(row) > 0:
            exist = True
            pass
         pass
   return exist
   pass

def write_to_database(self, conn):
   """
         write this object to database
   :param conn: connection to the database
   :return: true if it is written to the database
   """
   result = False
   try:
         if not self.is_exist(conn):
            with closing(conn.cursor()) as c:
               query = '''insert into "book" ("title", "year") values (?, ?)'''
               c.execute(query, (self.title, int(self.year)))
               conn.commit()
               result = True
               pass
            pass
         else:
            print('Book: {0}, Year: {1} had been in the library'.format(self.title, self.year))
            pass
         pass
   except Exception as e:
         print("Exception in _query: %s" % e)
         result = False
         pass
   return result
   pass

def get_id(self, conn):
   """
         get id of this object
   :param conn: connection to the database
   :return: id of the object
   """
   an_id = None
   with closing(conn.cursor()) as c:
         query = '''SELECT * FROM book WHERE title = ? and year = ?'''
         c.execute(query, (self.title, int(self.year)))
         a_book = c.fetchone()
         if a_book is not None:
            an_id = a_book['id']
            pass
         pass
   return an_id
   pass

def set_data_by_title_and_year(self, conn):
   """
         set up all attributes of this class by searching it in the database
         using its title and year
   :param conn: connection to the database
   """
   try:
         with closing(conn.cursor()) as c:
            query = '''SELECT * FROM book WHERE title = ? and year = ?'''
            c.execute(query, (self.title, int(self.year)))
            a_book = c.fetchone()
            if a_book is not None:
               self.b_id = a_book["id"]
               self.available = a_book["available"]
               pass
            pass
         pass
   except Exception as e:
         print("Exception in _query: %s" % e)
         result = False
         pass
   pass

def print(self):
   """
         print the object to screen
   """
   available = 'Yes'
   if self.available == 0:
         available = 'No'
   print('{: <4d}{: ^12s}{: >6d} {}'.format(self.b_id, available, self.year, self.title))
   pass

def set_data_by_id(self, conn):
   """
         set up all attributes of this class by searching it in the database
         using its id
   :param conn: connection to the database
   """
   try:
         with closing(conn.cursor()) as c:
            query = '''SELECT * FROM book WHERE id = ?'''
            c.execute(query, (int(self.b_id),))
            a_book = c.fetchone()
            if a_book is not None:
               self.available = a_book["available"]
               self.title = a_book["title"]
               self.year = a_book["year"]
               pass
            pass
         pass
   except Exception as e:
         print("Exception in _query 'set_data_by_id' : %s" % e)
         pass
   pass

def change_available_state(self, conn, new_state):
   """
         update available state of thsi object in the database
   :param conn: connection to the database
   :param new_state: the new state
   """
   try:
         with closing(conn.cursor()) as c:
            query = '''UPDATE book SET available = ? WHERE id = ?'''
            c.execute(query, (int(new_state), int(self.b_id)))
            conn.commit()
            pass
         pass
   except Exception as e:
         print("Exception in _query: %s" % e)
         pass
   pass

def rent_the_book(self, conn):
   """
         rent this book
   :param conn: connection to the database
   """
   self.set_data_by_id(conn)
   if self.available == 1:
         self.change_available_state(conn, 0)
         pass
   else:
         print("The book '{}' isn't available for renting".format(self.title))
         pass
   pass

def return_the_book(self, conn):
   """
         return this book
   :param conn: connection to the database
   """
   self.set_data_by_id(conn)
   if self.available == 0:
         self.change_available_state(conn, 1)
         pass
   else:
         print("The book '{}' hens't been rented ".format(self.title))
         pass
   pass

pass

class Author:
def __init__(self, name=""):
   """
         setup the author object
   :param name: name of the author
   """
   self.name = name
   pass

def is_exist(self, conn):
   """
         find the author in the database
   :param conn: connection to the database
   :return: true if the author is found
   """
   exist = True
   with closing(conn.cursor()) as c:
         query = '''select * from author where "name" = ?'''
         c.execute(query, (self.name,))
         if c.fetchone() is None:
            exist = False
            pass
         pass
   return exist
   pass

def write_to_database(self, conn):
   """
   write the object data to database
   :param conn: connection to the database
   :return: true if it is written
   """
   result = False
   try:
         if not self.is_exist(conn):
            with closing(conn.cursor()) as c:
               query = '''insert into author ("name") values (?)'''
               c.execute(query, (self.name,))
               conn.commit()
               pass
            pass
         else:
            print("Author: {0} has been in the library".format(self.name))
            pass
         result = True
         pass
   except Exception as e:
         print("Exception in _query: %s" % e)
         pass
   return result
   pass

def get_id(self, conn):
   """
         get id of the author
   :param conn: connection to the database
   :return: id of the author
   """
   an_id = None
   with closing(conn.cursor()) as c:
         query = '''SELECT * FROM author WHERE "name" = ?'''
         c.execute(query, (self.name,))
         an_author = c.fetchone()
         if an_author is not None:
            an_id = an_author['id']
            pass
         pass
   return an_id
   pass

pass

Select one of the project ideas below and develop a Python applicat...