def addnewbook():
print("\n---New Book Entry---\n")
name = strnotnull("Name: ")
desc = strnotnull("Description: ")
author = strnotnull("Author: ")
isbn = strnotnull("ISBN: ")
edition = inputnumber("Edition: ")
publisher = strnotnull("Publisher: ")
location = strnotnull("Location: ")
qty = inputnumber("Quantity: ")
r = answer("Save the new book? y/n: ")
if r == "y":
conn = connection.sql_connection()
try:
conn.execute(
"INSERT INTO book (NAME,desc,author,isbn,edition,publisher,location,qty) VALUES (?,?,?,?,?,?,?,?)",
(name, desc, author, isbn, edition, publisher, location, qty))
conn.commit()
print('' "\nrecord created successfully\n" '')
except sqlite3.Error as e:
print("Failed to create new book", e)
finally:
conn.close()
else:
print("User canceled, book not saved!")
def allbooklist():
try:
conn = connection.sql_connection()
cur = conn.cursor()
cur.execute(
'''select id, name , author, isbn, edition, publisher, location, qty from book'''
)
records = cur.fetchall()
if len(records) > 0:
print(f"\n{len(records)} Results found."
) #print how many results found
table = from_db_cursor(cur) #new table from the prettytable class
table.field_names = [
"ID", "Name", "Author", "ISBN", "Edition", "Publisher",
"Location", "Qty."
] #Table headers
for row in records:
table.add_row(row) #add rows to table from database cursor
print(table) #print table
else:
print("Search did not return any results.")
except sqlite3.Error as e:
print("Database error: !", e)
finally:
conn.close()
def search_func(text):
try:
conn = connection.sql_connection()
cur = conn.cursor()
cur.execute(
'''select rowid, name, author, isbn, publisher from book_index
where book_index match ?;''', (text, ))
# id like ? or name like ? or desc like ? or author like ? or isbn like ?
#or publisher like ?''',('%'+text+'%','%'+text+'%','%'+text+'%','%'+text+'%','%'+text+'%','%'+text+'%'))
records = cur.fetchall()
if len(records) > 0:
print(f"\n{len(records)} Results found."
) #print how many results found
table = from_db_cursor(cur) #new table from the prettytable class
table.field_names = ["ID", "Name", "Author", "ISBN",
"Publisher"] #Table headers
for row in records:
table.add_row(row) #add rows to table from database cursor
print(table) #print table
else:
print("Search did not return any results.")
except sqlite3.Error as e:
print("Database error: !", e)
finally:
conn.close()
def borrowedlist(type):
try:
conn = connection.sql_connection()
cur = conn.cursor()
status = type
cur.execute(
"Select id,bookid,bookname,userid,username,cdate,status,rdate from borrow where status = ?",
(status, ))
records = cur.fetchall()
print(f"{len(records)} Records found")
if len(records) > 0:
x = from_db_cursor(cur)
x.field_names = [
"ID", "Book id", "Book name", "User id", "User name",
"Date created", "Status", "Return Date"
]
for row in records:
x.add_row(row)
print(x)
else:
print("No records found!")
except sqlite3.Error as e:
print("Failed to reach the database!")
finally:
conn.close()
def borrowbook(): #function to borrow a book.
while True: # While true keep asking for a valid book id number
bookid = inputnumber(
"Book ID or 0 to cancel: ") #call function to input number
if bookid == 0: # if the bookid is 0 the user canceled
print("User Canceled, no records saved!")
return #return to main manu
else:
bookname = verifybook(
bookid) # if the value is not cero search in database.
if bookname == "": # If the return is none the book doesn't exist
continue #continue asking for a valid number
else:
break #else break the while and continue
print(f"\tBook name: {bookname}") # Print the book name
while True: # While true keep asking for a valid user id number
userid = inputnumber(
"User ID or 0 to cancel: ") #call function to input number
if userid == 0: # if the userid is 0 the user canceled
print("User Canceled, no records saved!")
return #return to main manu
else:
username = verifyuser(
userid) # if the value is not cero search in database.
if username == "": # If the return is none the user doesn't exist
continue #continue asking for a valid number
else:
break #else break the while and continue
print(f"\tNombre usuario: {username}") # print the user name
cdate = datetime.date.today()
status = 0
save = strnotnull("Do you want to save? y/n: ")
if save == 'y':
try:
conn = connection.sql_connection()
cur = conn.cursor()
cur.execute('''select * from book where id = ?''', (bookid, ))
records = cur.fetchall()
newqty = records[0][7] - 1
cur.execute("update book set qty = ? where id = ?",
(newqty, bookid))
conn.execute(
"INSERT INTO borrow (bookid,bookname,userid,username,cdate,status) VALUES (?,?,?,?,?,?)",
(bookid, bookname, userid, username, cdate, status))
conn.commit()
print('' "\nrecord created successfully\n" '')
except sqlite3.Error as e:
print("Failed to create new borrow", e)
finally:
conn.close()
else:
print("User cancelled, record not saved!")
return
def verifyuser(id):
conn = connection.sql_connection()
cur = conn.cursor()
cur.execute('''select * from users where id = ?''', (id, ))
records = cur.fetchall()
r = ""
if len(records) == 0:
print(
f"The user with the ID:{id} does not exist please try again or enter 0 to cancel!"
)
else:
r = records[0][1]
cur.close()
conn.close()
return r
def retur(id):
try:
conn = connection.sql_connection()
cur = conn.cursor()
cur.execute(
'''select id, bookid, bookname, userid, username, cdate, status from borrow where id = ?''',
(id, ))
records = cur.fetchall()
if len(records) > 0:
bookid = records[0][1]
if records[0][6] == 1:
print(f"This borrow ID {id} is already returned!")
else:
table = from_db_cursor(cur)
table.field_names = [
"Borrow ID", "Book id", "Book name", "User id",
"User name", "Date created", "Status"
]
for row in records:
table.add_row(row)
print(table)
re = strnotnull(
"Do you want to mark this borrow as returned? y/n: ")
if re == "y":
cur.execute('''select * from book where id = ?''',
(bookid, ))
records = cur.fetchall()
newqty = records[0][7] + 1
cur.execute("update book set qty = ? where id = ?",
(newqty, bookid))
date = datetime.date.today()
status = 1
cur.execute(
"update borrow set status = ? , rdate = ? where id = ?",
(status, date, id))
conn.commit()
print("Record updated successfully")
elif re == "n":
print("User canceled, no records updated!")
else:
print(f"The borrow ID {id} does not exists!")
except sqlite3.Error as e:
print("Database Error:!", e)
finally:
conn.close()
def verifybook(id):
conn = connection.sql_connection()
cur = conn.cursor()
cur.execute('''select * from book where id = ?''', (id, ))
records = cur.fetchall()
r = ""
if len(records) == 0:
print(
f"The book with the ID:{id} does not exist please try again or enter 0 to cancel!"
)
elif records[0][7] > 0:
r = records[0][1]
else:
print(f"Te book {id} {records[0][1]} is not available!")
cur.close()
conn.close()
return r
def viewusers():
conn = connection.sql_connection()
try:
cur = conn.cursor()
cur.execute("select * from users")
records = cur.fetchall()
print(f"{len(records)} Records found")
if len(records) > 0:
table = from_db_cursor(cur)
table.field_names = ["ID", "Name", "Address", "Phone"]
for row in records:
table.add_row(row)
print(table)
else:
print("No records found!")
except sqlite3.Error as e:
print("Database error!", e)
finally:
conn.close()
def addnewuser():
print("----Create new user-----")
name = strnotnull("Name: ")
address = strnotnull("Address: ")
phone = strnotnull("Phone #: ")
r = answer("Do you want to create the new user? y/n: ")
if r == "y":
conn = connection.sql_connection()
try:
conn.execute(
"INSERT INTO users (NAME,address,phone) VALUES (?,?,?)",
(name, address, phone))
conn.commit()
print('' "\nUser created successfully\n" '')
except sqlite3.Error as e:
print("Failed to create new user", e)
finally:
conn.close()
else:
print("User canceled, User not created!")
def book_detail(id): #function to print book in detail view
try:
conn = connection.sql_connection()
cur = conn.cursor()
cur.execute(
'''select id, name, desc, author, isbn, edition, publisher, location, qty from book where id = ?;''',
(id, ))
records = cur.fetchall()
dash = "-" * 90
if len(records) > 0:
print(dash)
format1('Book ID', records[0][0])
format1('Name', records[0][1])
format1('Author', records[0][3])
format1('ISBN', records[0][4])
format1('Edition', records[0][5])
format1('Publisher', records[0][6])
format1('Location', records[0][7])
format1('Qty', records[0][1])
wrapper = textwrap.TextWrapper(width=80)
if records[0][2] != None:
print("\033[1mDescription: \033[0m ")
desc = wrapper.wrap(text=records[0][2])
for line in desc:
print(line)
else:
print("\033[1mDescription: \033[0m None")
print(dash)
else:
print("The book ID: [" + str(id) + "] does not exists:")
except sqlite3.Error as e:
print("Database error: !", e)
finally:
conn.close()
def create_sqlite(data):
con = sql_connection()
data.to_sql('regions', con, if_exists='replace', index=False)
con.close()
