sDFjGnZb

1import sqlite3
2import pprint
3
4
5def execute_query(db_name, query, entry):
6    connection = sqlite3.connect(db_name)
7    connection_cursor = connection.cursor()
8    connection_cursor.execute(query, entry)
9    connection.commit()
10    connection.close()
11
12
13def create_table(db_name, query):
14    connection = sqlite3.connect(db_name)
15    connection_cursor = connection.cursor()
16    connection_cursor.execute(query)
17    connection.commit()
18    connection.close()
19
20
21def select_data(db_name, query, entry):
22    connection = sqlite3.connect(db_name)
23    connection_cursor = connection.cursor()
24    connection_cursor.execute(query, entry)
25    connection.commit()
26    rows = []
27    for row in connection_cursor.execute(query, entry):
28        rows.append(row)
29    pp = pprint.PrettyPrinter()
30    pp.pprint(rows)
31    connection.close()
32
33
34db_books = "books.db"
35books_table_query = """CREATE TABLE IF NOT EXISTS books (
36                                                        id integer PRIMARY KEY,
37                                                        book_title text,
38                                                        author text,
39                                                        publish_date date,
40                                                        publisher text,
41                                                        selling_price numeric
42                                                        )"""
43publishers_table_query = """CREATE TABLE IF NOT EXISTS publishers (
44                                                        id integer PRIMARY KEY,
45                                                        publisher_name text,
46                                                        book_title text,
47                                                        author text,
48                                                        printed_quantity integer,
49                                                        printing_price numeric
50                                                        )"""
51
52
53# Insert
54def insert_book(book_title, author, publish_date, publisher, selling_price):
55    insert_query = """INSERT INTO books (book_title, author, publish_date, publisher, selling_price) 
56                      VALUES(?, ?, ?, ?, ?)"""
57    book = [book_title, author, publish_date, publisher, selling_price]
58    execute_query(db_books, insert_query, book)
59
60
61def insert_publisher(publisher_name, book_title, author, printed_quantity, printing_price):
62    insert_query = """INSERT INTO publishers (publisher_name, book_title, author, printed_quantity, printing_price) 
63                      VALUES(?, ?, ?, ?, ?)"""
64    publisher = [publisher_name, book_title, author, printed_quantity, printing_price]
65    execute_query(db_books, insert_query, publisher)
66
67
68# Search
69def get_from_books(search_string):
70    select_query = """SELECT * FROM books WHERE book_title OR 
71                                                author OR 
72                                                publish_date OR 
73                                                publisher OR 
74                                                selling_price LIKE ?"""
75    title = ['%' + search_string + '%']
76    select_data(db_books, select_query, title)
77
78
79def get_from_publishers(search_string):
80    select_query = """SELECT * FROM publishers WHERE publisher_name OR 
81                                                     book_title OR 
82                                                     author OR 
83                                                     printed_quantity OR 
84                                                     printing_price LIKE ?"""
85    title = ['%' + search_string + '%']
86    select_data(db_books, select_query, title)
87
88
89# Update Book methods
90def update_book_title(new_value, book_id):
91    update_query = """UPDATE books SET book_title = ? WHERE id = ?"""
92    update_data = [new_value, book_id]
93    execute_query(db_books, update_query, update_data)
94
95
96def update_book_publisher(new_value, book_id):
97    update_query = """UPDATE books SET publisher = ? WHERE id = ?"""
98    update_data = [new_value, book_id]
99    execute_query(db_books, update_query, update_data)
100
101
102def update_book_author(new_value, book_id):
103    update_query = """UPDATE books SET author = ? WHERE id = ?"""
104    update_data = [new_value, book_id]
105    execute_query(db_books, update_query, update_data)
106
107
108def update_book_publish_date(new_value, book_id):
109    update_query = """UPDATE books SET publish_date = ? WHERE id = ?"""
110    update_data = [new_value, book_id]
111    execute_query(db_books, update_query, update_data)
112
113
114def update_book_selling_price(new_value, book_id):
115    update_query = """UPDATE books SET selling_price = ? WHERE id = ?"""
116    update_data = [new_value, book_id]
117    execute_query(db_books, update_query, update_data)
118
119
120# Update Publisher methods
121def update_publisher_name(new_value, publisher_id):
122    update_query = """UPDATE publishers SET publisher_name = ? WHERE id = ?"""
123    update_data = [new_value, publisher_id]
124    execute_query(db_books, update_query, update_data)
125
126
127def update_publisher_book_title(new_value, publisher_id):
128    update_query = """UPDATE publishers SET book_title = ? WHERE id = ?"""
129    update_data = [new_value, publisher_id]
130    execute_query(db_books, update_query, update_data)
131
132
133def update_publisher_author(new_value, publisher_id):
134    update_query = """UPDATE publishers SET author = ? WHERE id = ?"""
135    update_data = [new_value, publisher_id]
136    execute_query(db_books, update_query, update_data)
137
138
139def update_publisher_printed_quantity(new_value, publisher_id):
140    update_query = """UPDATE publishers SET printed_quantity = ? WHERE id = ?"""
141    update_data = [new_value, publisher_id]
142    execute_query(db_books, update_query, update_data)
143
144
145def update_publisher_printing_price(new_value, publisher_id):
146    update_query = """UPDATE publishers SET printing_price = ? WHERE id = ?"""
147    update_data = [new_value, publisher_id]
148    execute_query(db_books, update_query, update_data)
149
150
151# Delete
152def delete_book_by_id(book_id):
153    delete_query = """DELETE FROM books WHERE id = ?"""
154    entry_id = [book_id]
155    execute_query(db_books, delete_query, entry_id)
156
157
158def delete_publisher_by_id(publisher_id):
159    delete_query = """DELETE FROM publishers WHERE id = ?"""
160    entry_id = [publisher_id]
161    execute_query(db_books, delete_query, entry_id)