-
Notifications
You must be signed in to change notification settings - Fork 0
/
bookdb.py
155 lines (137 loc) · 7.08 KB
/
bookdb.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
import sqlite3
class BookManager:
def __init__(self):
self.conn = None
self.cur = None
@staticmethod
def get_connection():
return sqlite3.connect("bookDatabase.db")
def create_database(self):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("""
CREATE TABLE BookData (
bid INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author TEXT NOT NULL,
genre TEXT,
publication_year INTEGER,
isbn TEXT,
status TEXT DEFAULT 'available',
user_id INTEGER,
FOREIGN KEY (user_id) REFERENCES UserData(id)
);
""")
self.conn.commit()
self.conn.close()
def fill_database(self):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
data = [
('The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 1925, '9780743273565', 'Available', -1),
('To Kill a Mockingbird', 'Harper Lee', 'Classics', 1960, '9780061120084', 'Available', -1),
('1984', 'George Orwell', 'Dystopian', 1949, '9780451524935', 'Available', -1),
('Pride and Prejudice', 'Jane Austen', 'Romance', 1813, '9780141439518', 'Available', -1),
('The Catcher in the Rye', 'J.D. Salinger', 'Coming-of-Age', 1951, '9780316769480', 'Available', -1),
('One Hundred Years of Solitude', 'Gabriel Garcia Marquez', 'Magical Realism', 1967, '9780061120084', 'Available', -1),
('Moby-Dick', 'Herman Melville', 'Adventure', 1851, '9780142437247', 'Available', -1),
('The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 1937, '9780547928227', 'Available', -1),
('The Hitchhiker''s Guide to the Galaxy', 'Douglas Adams', 'Science Fiction', 1979, '9780345391803', 'Available', -1),
('The Lord of the Rings', 'J.R.R. Tolkien', 'Fantasy', 1954, '9780545010221', 'Available', -1),
('Harry Potter and the Sorcerer''s Stone', 'J.K. Rowling', 'Fantasy', 1997, '9780590353427', 'Available', -1),
('Brave New World', 'Aldous Huxley', 'Dystopian', 1932, '9780060850524', 'Available', -1),
('The Odyssey', 'Homer', 'Epic Poetry', 1900, '9780199536788', 'Available', -1),
('The Shining', 'Stephen King', 'Horror', 1977, '9780385121675', 'Available', -1),
('The Alchemist', 'Paulo Coelho', 'Philosophical Fiction', 1988, '9780061120084', 'Available', -1),
('The Road', 'Cormac McCarthy', 'Post-Apocalyptic', 2006, '9780307387899', 'Available', -1),
('Frankenstein', 'Mary Shelley', 'Gothic Fiction', 1818, '9780486282114', 'Available', -1),
('The Color Purple', 'Alice Walker', 'Historical Fiction', 1982, '9780156028356', 'Available', -1),
('Wuthering Heights', 'Emily Bront', 'Gothic Fiction', 1847, '9780141439556', 'Available', -1),
('The Brothers Karamazov', 'Fyodor Dostoevsky', 'Philosophical Fiction', 1880, '9780140449242', 'Available', -1),
('Sapiens: A Brief History of Humankind', 'Yuval Noah Harari', 'History', 2014, '9780062316097', 'Available', -1)]
for item in data:
self.cur.execute("INSERT INTO BookData (title, author, genre, publication_year, isbn, status, user_id ) VALUES (?, ?, ?, ?, ?, ?, ?)", item)
self.conn.commit()
self.conn.close()
def clear_database(self):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("delete from BookData")
self.conn.commit()
self.conn.close()
def add_book(self, title, author, genre, publication_year, isbn, status, user_id):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("INSERT INTO BookData(title, author, genre, publication_year, isbn, status, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)",
(title, author, genre, publication_year, isbn, status, user_id))
self.conn.commit()
self.conn.close()
def list_books(self):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("select * from BookData")
books = self.cur.fetchall()
self.conn.close()
return books
def delete_book(self, id):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("delete from BookData where bid=?", [id])
self.conn.commit()
def edit_book(self, bid, title, author, genre, publication_year, isbn, status, user_id):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("update BookData set title=?, author=?, genre=?, publication_year=?, isbn=?, status=?, user_id=? where bid=?",
[title, author, genre, publication_year, isbn, status, user_id, bid-1])
self.conn.commit()
def book_detail(self, bid):
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("select * from BookData where bid=?", [bid])
book = self.cur.fetchone()
self.conn.close()
return book
def issue_book(self, book_id, user_id):
try:
# Check if the book is available
book = self.book_detail(book_id)
if book and book[6] == 'Available':
# Update the book's status and assign it to the user
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("UPDATE BookData SET status=?, user_id=? WHERE bid=?", ('Issued', user_id, book_id))
self.conn.commit()
self.conn.close()
return True # Book issued successfully
else:
return False # Book not available
except sqlite3.Error as err:
# Handle errors if necessary
print("Error:", err)
return False
def return_book(self, book_id):
try:
# Check if the book is issued
book = self.book_detail(book_id)
if book and book[6] == 'Issued':
# Update the book's status to 'available' and remove the association with the user
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("UPDATE BookData SET status=?, user_id=NULL WHERE bid=?", ('Available', book_id))
self.conn.commit()
self.conn.close()
return True # Book returned successfully
else:
return False # Book not issued or not found
except sqlite3.Error as err:
# Handle errors if necessary
print("Error:", err)
return False
def get_my_books(self, user_id):
if user_id:
self.conn = self.get_connection()
self.cur = self.conn.cursor()
self.cur.execute("select * from BookData WHERE user_id=?", [user_id])
books = self.cur.fetchall()
self.conn.close()
return books