· 7 years ago · Feb 25, 2019, 09:24 PM
1import mysql.connector
2from books_model.book import Book
3from author_model.author import Author
4from author_model.dbdao import AuthorDBDAO
5from utils.utils import TypeUtils
6from utils.utils import ErrorMsgUtils
7
8
9
10
11class BooksDBAO:
12
13
14 def __init__(self, db):
15 self.db = db
16 self.cursor = self.db.cursor()
17
18
19 def create_table(self):
20 """
21 Create Books Table
22 :return:
23 """
24 try:
25 self.cursor.execute("CREATE TABLE BOOKS "
26 "(ID INT AUTO_INCREMENT, "
27 "NAME VARCHAR(255), "
28 "PRICE DOUBLE, "
29 "AUTHOR_ID INT, "
30 "PRIMARY KEY (ID), "
31 "FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(ID)"
32 ")")
33 # Success
34 print("Table Books was created successfully")
35
36
37 # Error
38 except mysql.connector.Error as error:
39 print(ErrorMsgUtils.display_error(error))
40
41
42 def create_book(self, name, price, author):
43 """
44 Validates name & price & author type
45 Validates Book is not exists by name
46 Creates a new book into the BOOKS's table
47 :param name: str
48 :param price: int | float
49 :param author: Author
50 :return: Book | None
51 """
52
53
54 # Validates name
55 if not TypeUtils.is_type(name, var_type=str):
56 print(ErrorMsgUtils.type_error(name, var_type=str))
57 return
58
59
60 # Validates price
61 if not TypeUtils.is_type_or(price, var_type1=int, var_type2=float):
62 print(ErrorMsgUtils.none_of_type(int, float, var=print()))
63 return
64
65
66 # Validates author
67 if not TypeUtils.is_type(author, var_type=Author):
68 print(ErrorMsgUtils.type_error(author, var_type=Author))
69 return
70
71
72 # Validates existence
73 book_to_check = self.find_by_name(name)
74 if book_to_check is not None:
75 print(ErrorMsgUtils.already_exists(name))
76 return
77
78
79 # Validates author existence
80 author_db = AuthorDBDAO(db=self.db)
81 author_by_id = author_db.find_by_id(author.id)
82 if author_by_id is None:
83 print(ErrorMsgUtils.does_not_exists(table_name='Author', var=author.id))
84 return
85
86
87 # SQL & Execution
88 sql = f"INSERT INTO BOOKS (NAME, PRICE, AUTHOR_ID) VALUES ('{name}', {price}, {author.id})"
89 try:
90 self.cursor.execute(sql)
91 self.db.commit()
92 except mysql.connector.Error as error:
93 print(ErrorMsgUtils.display_error(error))
94 # Return None
95 return None
96 else:
97 print(self.cursor.rowcount, "was inserted.")
98 print("ID: ", self.cursor.lastrowid)
99 # Return the Book
100 return Book(
101 name=name,
102 price=price,
103 id=self.cursor.lastrowid,
104 author_id=author.id
105 )
106
107
108 def find_by_id(self, id):
109 """
110 Validates ID type is int
111 Returns Book by ID if exists
112 :param id: int
113 :return: Book | None
114 """
115
116
117 # Validates type
118 if not TypeUtils.is_type(id, var_type=int):
119 print(ErrorMsgUtils.type_error(id, var_type=int))
120 return
121
122
123 # Execution
124 sql = f"SELECT * FROM BOOKS WHERE ID = {id}"
125 try:
126 self.cursor.execute(sql)
127 results = self.cursor.fetchone()
128 if not results:
129 print(ErrorMsgUtils.does_not_exists(table_name='Book', var=id))
130 return None
131
132
133 # Return results
134 book_by_id = Book(
135 id=results[0],
136 name=results[1],
137 price=results[2],
138 author_id=results[3],
139 )
140 return book_by_id
141
142
143 # Error
144 except mysql.connector.Error as error:
145 print(ErrorMsgUtils.display_error(error))
146
147
148 def find_by_name(self, name):
149 """
150 Validates Name type is str
151 :param name: str
152 :return: Book | None
153 """
154
155
156 # Validates type
157 if not TypeUtils.is_type(name, var_type=str):
158 print(ErrorMsgUtils.type_error(name, var_type=str))
159 return
160
161
162 # Execution
163 sql = f"SELECT * FROM BOOKS WHERE NAME = '{name}'"
164 try:
165 self.cursor.execute(sql)
166 results = self.cursor.fetchone()
167 if not results:
168 print(ErrorMsgUtils.does_not_exists(table_name='Book', var=name))
169 return None
170
171
172 # Returns results
173 book_by_name = Book(
174 id=results[0],
175 name=results[1],
176 price=results[2],
177 author_id=results[3])
178 return book_by_name
179
180
181 # Error
182 except mysql.connector.Error as error:
183 print(ErrorMsgUtils.display_error(error))
184
185
186 def update_book(self, book):
187 """
188 Validates Book type is Book
189 :param book: Book
190 :return: Updated Book | None
191 """
192
193
194 # Validates type
195 if not TypeUtils.is_type(book, var_type=Book):
196 print(ErrorMsgUtils.type_error(book, var_type=Book))
197 return
198
199
200 # Validate existence
201 book_to_update = self.find_by_id(book.id)
202 if book_to_update is None:
203 print(ErrorMsgUtils.does_not_exists(table_name='Book', var=book.id))
204 return
205
206
207 # Execution
208 sql = f"UPDATE BOOKS SET NAME = '{book.name}', " \
209 f"PRICE = '{book.price}' " \
210 f"WHERE ID = {book.id}"
211 try:
212 self.cursor.execute(sql)
213 self.db.commit()
214
215
216 # Error
217 except mysql.connector.Error as error:
218 print(ErrorMsgUtils.display_error(error))
219 return None
220 else:
221
222
223 # Return updated Book
224 print(self.cursor.rowcount, "was updated.")
225 updated_book = self.find_by_id(book.id)
226 return updated_book
227
228
229 def remove_book(self, id):
230 """
231 Validates ID type is int
232 Validates existence before removing
233 Delete Book and returns it
234 :param id: int
235 :return: Deleted Book | None
236 """
237
238
239 # Validates type
240 if not TypeUtils.is_type(id, var_type=int):
241 print(ErrorMsgUtils.type_error(id, var_type=int))
242 return
243
244
245 # Validates existence
246 book = self.find_by_id(id)
247 if not book:
248 return
249
250
251 # Execution
252 sql = f"DELETE FROM BOOKS WHERE ID = {id}"
253
254
255 try:
256 self.cursor.execute(sql)
257 self.db.commit()
258
259
260 # Error
261 except mysql.connector.Error as error:
262 print(ErrorMsgUtils.display_error(error))
263 else:
264
265
266 # Returns Deleted Book
267 print(self.cursor.rowcount, "was deleted.")
268 return book
269
270
271 def find_all(self):
272 """
273 Returns all Books
274 :param self:
275 :return: List of Books
276 """
277
278
279 try:
280 # Execution
281 sql = "SELECT * FROM BOOKS"
282 self.cursor.execute(sql)
283 results = self.cursor.fetchall()
284
285
286 # No Data
287 if not results:
288 print(ErrorMsgUtils.no_data_available())
289 return
290
291
292 # Return results
293 return results
294
295
296 # Error
297 except mysql.connector.Error as error:
298 print(ErrorMsgUtils.display_error(error))