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