queries.py

# from "Python 101" pg. 351
import sqlite3


def get_cursor():
    conn = sqlite3.connect("library.db")
    return conn.cursor()


def select_all_records_by_author(cursor, author):
    sql = "SELECT * FROM books WHERE author=?"
    cursor.execute(sql, [author])
    print("Results:")
    book_info = cursor.fetchall()  # or use fetchone()
    book_count = cursor.rowcount
    print(f"book_count = {book_count}")
    return book_count, book_info


def select_all_books_by_author(cursor, author):
    # https://stackoverflow.com/questions/44962932/how-to-use-rowcount-in-mysql-using-python
    sql = "SELECT title FROM books WHERE author=?"
    book_count = cursor.execute(sql, [author]).rowcount
    book_info = cursor.execute(sql, [author])
    print(f"book_count = {book_count}")
    print(f"book_info  = {book_info}")
    """
    TODO: return {None, 0} if rowcount method = 0, indicating author has no books in table
    exit(1)
    if book_count == 0:
        print("No results from query.")
    else:
    """
    print("Query results:")
    print(book_info)  # or use fetchone()

    if book_count is None:
        return {0, None}  # author didn't write any books in table
    else:
        return {book_count, book_info}  # count of books author wrote, plus their info


    """
    print(f"\nAll books by author {author}:\n")
    for row in cursor.execute("SELECT rowid, * FROM books ORDER BY author"):
        print(row[1])  # return book title
        result = cursor.execute(sql, [author])
    # https://stackoverflow.com/questions/44471077/python-sqlite-is-there-any-way-to-distinguish-between-an-empty-result-of-a-qu
    else:
        print("No records found.")
    """


def select_using_like(cursor, text):
    print("\nLIKE query results:\n")
    sql = f"""
    SELECT * FROM books
    WHERE title LIKE '{text}%'"""
    cursor.execute(sql)
    print(cursor.fetchall())


def show_books_by_author(author):
    # returns book_count and tuple of books written, book_info:
    book_count, book_info = select_all_books_by_author(cursor, author)
    if book_count == -1:
        print(f"No books written by author {author}.")
    else:
        for name in book_info:
            print(name[1])  # show book name


if __name__ == '__main__':
    cursor = get_cursor()
    select_all_books_by_author(cursor, 'Mike Driscoll')
    select_using_like(cursor, text='Python')

    # junk I wrote:
    show_books_by_author("Mike Driscoll")
    # test a query that definitely doesn't exist - should return None for "No records found."
    show_books_by_author('Ryan Sherwood')