book.py

from db import get_connection

def create_catalog(name):
    cursor = get_connection().cursor()
    cursor.execute('''
    insert into catalog(name) values(%s)''',(name,))
    connection.commit()
    connection.close()
    print(f"The Category \"{name}\" has been Created")

def create_book(title, cat, auth, year):
    cursor = get_connection().cursor()
    cursor.execute('''
    insert into book(title, catalog_id, author_id, published_year) values(%s,%s,%s,%s)''',
                   (title, cat, auth, year,))
    connection.commit()
    connection.close()
    print(f"The Book \"{title}\" has been Created")

def get_cat():
    cursor = get_connection().cursor()
    cursor.execute('''
    select * from catalog''')
    data = cursor.fetchall()
    print(' --- ID | CATEGORY NAME --- ')
    for d in data:
        print(f"      {d[0]} | {d[1]}")

def get_books():
    cursor = get_connection().cursor()
    cursor.execute('''
    select b.id, b.title, c.name, a.name, b.published_year from book b
    inner join catalog c on c.id = b.catalog_id
    inner join author a on a.id = b.author_id''')
    data = cursor.fetchall()
    print('BOOKS')
    print(' --- ID | TITLE | GENRE | AUTHOR | YEAR --- ')
    for d in data:
        print(f"      {d[0]} | {d[1]} | {d[2]} | {d[3]} | {d[4]}")

def count_catalog():
    cursor = get_connection().cursor()
    cursor.execute('''
    select c.name, count(b.catalog_id) from book b
    inner join catalog c on c.id = b.catalog_id
    group by c.name, b.catalog_id''')
    data = cursor.fetchall()
    print(' --- NAME | QUANTITY --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")

def find_oldest_book():
    cursor = get_connection().cursor()
    cursor.execute('''
    select title, published_year from book ORDER BY published_year ASC LIMIT 1''')
    data = cursor.fetchall()
    print(' --- NAME | YEAR --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")

def find_newest_book():
    cursor = get_connection().cursor()
    cursor.execute('''
    select title, published_year from book ORDER BY published_year DESC LIMIT 1''')
    data = cursor.fetchall()
    print(' --- NAME | YEAR --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")

def books_over_2000():
    cursor = get_connection().cursor()
    cursor.execute('''select title from book where published_year > 2000''')
    data = cursor.fetchall()
    print(' --- BOOKS PUBLISHED OVER 2000 --- ')
    for d in data:
        print(d[0])

def find_oldest_book_of_catalog():
    cursor = get_connection().cursor()
    cursor.execute('''
    select c.name, min(b.published_year) from book b 
    join catalog c on b.catalog_id = c.id  group by c.name''')
    data = cursor.fetchall()
    print(' --- NAME | YEAR --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")

def find_newest_book_of_catalog():
    cursor = get_connection().cursor()
    cursor.execute('''
    select c.name, max(b.published_year) from book b     
    join catalog c on b.catalog_id = c.id group by c.name''')
    data = cursor.fetchall()
    print(' --- NAME | YEAR --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")

def get_catalogs_without_book():
    cursor = get_connection().cursor()
    cursor.execute('''
    select c.id, c.name from catalog c
    left join book b on c.id = b.catalog_id where b.id is null''')
    data = cursor.fetchall()
    print(' --- ID | NAME --- ')
    for d in data:
        print(f"      {d[0]} | {d[1]}")

def book_quantity_in_each_country():
    cursor = get_connection().cursor()
    cursor.execute('''
    select a.country, count(b.author_id) from author a
    inner join book b on b.author_id = a.id
    group by a.country''')
    data = cursor.fetchall()
    print(' --- COUNTRY | QUANTITY --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")

def get_specific_letter(letter):
    cursor = get_connection().cursor()
    cursor.execute('''
    select title from book where title ilike %s''',('%'+letter+'%',))
    data = cursor.fetchall()
    print(f" --- \"{letter}\" FOUND IN --- ")
    for d in data:
        print(d[0])

def avg_year_of_each_catalog():
    cursor = get_connection().cursor()
    cursor.execute('''
    select c.name, avg(b.published_year) from catalog c 
    inner join book b on b.catalog_id = c.id group by c.name
    ''')
    data = cursor.fetchall()
    print(' --- NAME | YEAR --- ')
    for d in data:
        print(f"{d[0]} | {round(d[1])}")

def get_3_newest_books():
    cursor = get_connection().cursor()
    cursor.execute('''
    select title, published_year from book order by published_year desc limit 3''')
    data = cursor.fetchall()
    print(' --- TITLE | YEAR --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")

def get_3_oldest_books():
    cursor = get_connection().cursor()
    cursor.execute('''
    select title, published_year from book order by published_year asc limit 3''')
    data = cursor.fetchall()
    print(' --- TITLE | YEAR --- ')
    for d in data:
        print(f"{d[0]} | {d[1]}")