<file_sep>import sqlite3
import datetime
def create_table():
    sqliteConnection = sqlite3.connect('book.db',detect_types=sqlite3.PARSE_DECLTYPES |sqlite3.PARSE_COLNAMES,timeout=1)
    cursor = sqliteConnection.cursor()
    print("Connected to SQLite")
    sqlite_create_table_query = '''CREATE TABLE transaction_details (
                                       bookid text,
                                       author TEXT NOT NULL,
                                       searchdate timestamp);'''

    cursor = sqliteConnection.cursor()
    cursor.execute(sqlite_create_table_query)
    
def search_book_name():
    sqliteConnection = sqlite3.connect('book.db',detect_types=sqlite3.PARSE_DECLTYPES |sqlite3.PARSE_COLNAMES,timeout=1)
    name=input("Enter book name ")
    cursor=sqliteConnection.execute("select bookid,bookname,author_name from book_details where bookname=?",(name,))
    data=cursor.fetchall()
    if len(data)==0:
        print('No Record found')
    else:
        print('Book Id \t Book Name \t\t Author Name ')
        for row in data:
            author_id=row[2]
            book_id=row[0]
            print(row[0],"\t\t",row[1],"\t\t",row[2])
        sqlite_insert_with_param = """INSERT INTO 'transaction_details'
                          ('bookid','author','searchdate') 
                          VALUES (?, ?, ?);"""
        cursor1 = sqliteConnection.cursor()
        data_tuple = (book_id,author_id,datetime.datetime.now().date())
        cursor1.execute(sqlite_insert_with_param, data_tuple)
        sqliteConnection.commit() 
    
    
def search_book_by_ids():
    sqliteConnection = sqlite3.connect('book.db',detect_types=sqlite3.PARSE_DECLTYPES |sqlite3.PARSE_COLNAMES,timeout=1)
    book_id=input("Enter book id (enter upper case ")
    cursor=sqliteConnection.execute("select bookid,bookname,author_name from book_details where bookid=?",(book_id,))
    data=cursor.fetchall()
    if len(data)==0:
        print('No Record found')
    else:
        print('Book Id \t Book Name \t\t Author Name ')
        for row in data:
            print(row[0],"\t\t",row[1],"\t\t",row[2])
        for row in data:
            author_id=row[2]
            book_id=row[0]
            print(row[0],"\t\t",row[1],"\t\t",row[2])
        sqlite_insert_with_param = """INSERT INTO 'transaction_details'
                          ('bookid','author','searchdate') 
                          VALUES (?, ?, ?);"""
        cursor1 = sqliteConnection.cursor()
        data_tuple = (book_id,author_id,datetime.datetime.now().date())
        cursor1.execute(sqlite_insert_with_param, data_tuple)
        sqliteConnection.commit()
def no_of_books():
    conn = sqlite3.connect('book.db',detect_types=sqlite3.PARSE_DECLTYPES |sqlite3.PARSE_COLNAMES,timeout=1)
    cursor=conn.execute("select count(bookid) from transaction_details where searchdate=?",(datetime.datetime.now().date(),))
    
    data=cursor.fetchall()
    print("Total number of books searched today ")
    for p in data:
        print(p[0])
def max_author_search():
    author=[]
    conn = sqlite3.connect('book.db',detect_types=sqlite3.PARSE_DECLTYPES |sqlite3.PARSE_COLNAMES,timeout=1)
    cursor=conn.execute("select author,count(author) as a  from transaction_details where searchdate=? group by author order by a desc ",(datetime.datetime.now().date(),))
    
    data=cursor.fetchall()
    print("Maximum author searched today ")
    for p in data:
        author.append(p[0])
        print(p[0])
def max_book_search():
    author=[]
    conn = sqlite3.connect('book.db',detect_types=sqlite3.PARSE_DECLTYPES |sqlite3.PARSE_COLNAMES,timeout=1)
    cursor=conn.execute("select bookid,count(bookid) as a  from transaction_details where searchdate=? group by author order by a desc ",(datetime.datetime.now().date(),))
    
    data=cursor.fetchall()
    print("Maximum book searched today ")
    for p in data:
        author.append(p[0])
        print(p[0])
def number_of_visitor():
    author=[]
    conn = sqlite3.connect('book.db',detect_types=sqlite3.PARSE_DECLTYPES |sqlite3.PARSE_COLNAMES,timeout=1)
    cursor=conn.execute("select count(searchdate) as a  from transaction_details where searchdate=? group by author order by a desc ",(datetime.datetime.now().date(),))
    
    data=cursor.fetchall()
    print("No of visitors  today ")
    for p in data:
        author.append(p[0])
        print(p[0])
    
#create_table()

while True:
    print("1. Search Book by id ")
    print("2. Search Book by Name ")
    print("3. Number of books searched in a day ")
    print("4. Most searched book ")
    print("5. Most searched author ")
    print("6. Number of visitor in a day ")
    print("7. Exit ")
    option=int(input("Enter your option "))
    if option==1:
        search_book_by_ids()
    elif option==2:
        search_book_name()
    elif option==3:
        no_of_books()
    elif option==4:
        max_book_search()
    elif option==5:
        max_author_search()
    elif option==6:
        number_of_visitor()
    elif option==7:
        break
    else:
        print("Invalid option selected ")
        

