· 7 years ago · Feb 25, 2019, 09:30 PM
1from utils.utils import ErrorMsgUtils
2from utils.utils import TypeUtils
3from author_model.author import Author
4import mysql.connector
5
6
7
8
9class AuthorDBDAO:
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 AUTHOR "
24 "(ID INT AUTO_INCREMENT PRIMARY KEY, "
25 "FIRST_NAME VARCHAR(255), "
26 "LAST_NAME VARCHAR(255)"
27 ")")
28 # Success
29 print("Table Author 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_author(self, first_name, last_name):
38 """
39 Validates type first_name & last_name is str
40 Validates author does not exists by first & last name
41 :param first_name: str
42 :param last_name: str
43 :return: Author | None
44 """
45
46
47 # Validates type
48 if not TypeUtils.all_of_type(first_name, last_name, var_type=str):
49 print(ErrorMsgUtils.type_error(first_name, last_name, var_type=str))
50 return
51
52
53 # Validate existence
54 author_to_check = self.find_by_name(first_name, last_name)
55 if author_to_check is not None:
56 print(ErrorMsgUtils.already_exists(f"{first_name}-{last_name}"))
57 return
58
59
60 sql = f"INSERT INTO AUTHOR(FIRST_NAME, LAST_NAME) VALUES ('{first_name}', '{last_name}')"
61 # Execution
62 try:
63 self.cursor.execute(sql)
64 self.db.commit()
65
66
67 # Error
68 except mysql.connector.Error as error:
69 print(ErrorMsgUtils.display_error(error))
70 else:
71 print(self.cursor.rowcount, "was inserted.")
72 print("ID: ", self.cursor.lastrowid)
73 # Return the Book
74 return Author(
75 first_name=first_name,
76 last_name=last_name,
77 id=self.cursor.lastrowid
78 )
79
80
81 def find_by_name(self, first_name, last_name):
82 """
83 Validates first_name & last_name are str
84 Returns Author if exists
85 :param first_name: str
86 :param last_name: str
87 :return: Author | None
88 """
89
90
91 # Validates type
92 if not TypeUtils.all_of_type(first_name, last_name, var_type=str):
93 print(ErrorMsgUtils.type_error(first_name, last_name, var_type=str))
94 return
95
96
97 # Validate legal value
98 if not first_name:
99 print(ErrorMsgUtils.illegal_value(first_name))
100 if not last_name:
101 print(ErrorMsgUtils.illegal_value(last_name))
102
103
104 sql = f"SELECT * FROM AUTHOR WHERE FIRST_NAME = '{first_name}' AND LAST_NAME = '{last_name}'"
105 # Execution
106 try:
107 self.cursor.execute(sql)
108 results = self.cursor.fetchone()
109 if not results:
110 print(ErrorMsgUtils.does_not_exists(table_name='Author', var=f"{first_name}, {last_name}"))
111 return
112
113
114 # Return results
115 return Author(
116 id=results[0],
117 first_name=results[1],
118 last_name=results[2]
119 )
120
121
122 except mysql.connector.Error as error:
123 print(ErrorMsgUtils.display_error(error))
124
125
126 def find_by_id(self, id):
127 """
128 Validate ID type is int
129 Returns Author if exists
130 :param id: int
131 :return: Author | None
132 """
133
134
135 # Validate type
136 if not TypeUtils.is_type(id, var_type=int):
137 print(ErrorMsgUtils.type_error(id, var_type=int))
138 return
139
140
141 sql = f"SELECT * FROM AUTHOR WHERE ID = {id}"
142 # Execution
143 try:
144 self.cursor.execute(sql)
145 results = self.cursor.fetchone()
146 if not results:
147 print(ErrorMsgUtils.does_not_exists(table_name='Author', var=id))
148 return
149
150
151 # Return results
152 return Author(
153 id=results[0],
154 first_name=results[1],
155 last_name=results[2]
156 )
157
158
159 except mysql.connector.Error as error:
160 print(ErrorMsgUtils.display_error(error))
161
162
163 def update_author(self, author):
164 """
165 Validates author type is Author
166 :param author: Author
167 :return: Updated Author | None
168 """
169
170
171 # Validate type
172 if not TypeUtils.is_type(author, var_type=Author):
173 print(ErrorMsgUtils.type_error(author, var_type=Author))
174 return
175
176
177 sql = f"UPDATE AUTHOR SET " \
178 f"FIRST_NAME = '{author.first_name}', " \
179 f"LAST_NAME = '{author.last_name}' " \
180 f"WHERE ID = {author.id}"
181
182
183 # Execution
184 try:
185 self.cursor.execute(sql)
186 self.db.commit()
187 # Error
188 except mysql.connector.Error as error:
189 print(ErrorMsgUtils.display_error(error))
190 return
191 else:
192 # Return updated results
193 return self.find_by_id(author.id)
194
195
196 def remove_author(self, id):
197 """
198 Validates id type is int
199 Validates existence before removing
200 Removing & returns the removed row
201 :param id:
202 :return:
203 """
204
205
206 # Validates type
207 if not TypeUtils.is_type(id, var_type=int):
208 print(ErrorMsgUtils.type_error(id, var_type=int))
209 return
210
211
212 # Validate existence
213 author = self.find_by_id(id)
214 if author is None:
215 return
216
217
218 sql = f"DELETE FROM AUTHOR WHERE ID = {id}"
219 # Execution
220 try:
221 self.cursor.execute(sql)
222 self.db.commit()
223
224
225 # Error
226 except mysql.connector.Error as error:
227 print(ErrorMsgUtils.display_error(error))
228 else:
229 return author
230
231
232 def find_all(self):
233 """
234 Returns all authors or none
235 :return: List of Authors | None
236 """
237
238
239 sql = "SELECT * FROM AUTHOR"
240 try:
241 self.cursor.execute(sql)
242 results = self.cursor.fetchall()
243 # No Data case
244 if not results:
245 print(ErrorMsgUtils.no_data_available())
246 return
247
248
249 # Return results
250 return results
251
252
253 # Error
254 except mysql.connector.Error as error:
255 print(ErrorMsgUtils.display_error(error))
256
257
258 def find_authors_books(self, author_id):
259 """
260 Validates author_id type is int
261 Returns all authors books
262 :param author_id: int
263 :return: List of Books | None
264 """
265
266
267 # Validates type
268 if not TypeUtils.is_type(author_id, var_type=int):
269 print(ErrorMsgUtils.type_error(author_id, var_type=int))
270 return
271
272
273 sql = f"SELECT BOOKS.* FROM AUTHOR, BOOKS " \
274 f"WHERE AUTHOR.ID = {author_id} AND BOOKS.AUTHOR_ID = {author_id}"
275 # Execution
276 try:
277 self.cursor.execute(sql)
278 results = self.cursor.fetchall()
279 # No Data
280 if not results:
281 print(ErrorMsgUtils.no_data_available())
282 return
283
284
285 # Return results
286 return results
287 # Error
288 except mysql.connector.Error as error:
289 print(ErrorMsgUtils.display_error(error))