From e38a785445b12b696beefb4cfef815ecfe89711d Mon Sep 17 00:00:00 2001 From: Andrew Date: Wed, 24 Jul 2019 14:55:21 +0400 Subject: =?UTF-8?q?=D0=9E=D0=B1=D0=BD=D0=BE=D0=B2=D0=BB=D0=B5=D0=BD=D0=B0?= =?UTF-8?q?=20=D0=BE=D0=B1=D0=B5=D1=80=D1=82=D0=BA=D0=B0=20=D0=B4=D0=BB?= =?UTF-8?q?=D1=8F=20mysql=20=D0=B1=D0=B0=D0=B7=20=D0=B4=D0=B0=D0=BD=D0=BD?= =?UTF-8?q?=D1=8B=D1=85.=20=D0=98=D1=81=D0=BF=D1=80=D0=B0=D0=B2=D0=BB?= =?UTF-8?q?=D0=B5=D0=BD=D1=8B=20=D0=BE=D1=88=D0=B8=D0=B1=D0=BA=D0=B8=20?= =?UTF-8?q?=D0=B2=20=D0=B2=D0=B0=D0=BB=D0=B8=D0=B4=D0=B0=D1=82=D0=BE=D1=80?= =?UTF-8?q?=D0=B0=D1=85.=20=D0=98=D1=81=D0=BF=D1=80=D0=B0=D0=B2=D0=BB?= =?UTF-8?q?=D0=B5=D0=BD=D0=B0=20=D0=BE=D0=B1=D1=80=D0=B0=D0=B1=D0=BE=D1=82?= =?UTF-8?q?=D0=BA=D0=B0=20=D0=B7=D0=B0=D0=B3=D1=80=D1=83=D0=B7=D0=BA=D0=B8?= =?UTF-8?q?=20csv=20=D1=84=D0=B0=D0=B9=D0=BB=D0=B0=20=D1=81=20=D0=BD=D0=BE?= =?UTF-8?q?=D0=B2=D1=8B=D0=BC=D0=B8=20=D0=B4=D0=B0=D0=BD=D0=BD=D1=8B=D0=BC?= =?UTF-8?q?=D0=B8.?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- day9/task5_vue/backend/database/wrappers.py | 68 ++++++++++++++--------------- 1 file changed, 32 insertions(+), 36 deletions(-) (limited to 'day9/task5_vue/backend/database/wrappers.py') diff --git a/day9/task5_vue/backend/database/wrappers.py b/day9/task5_vue/backend/database/wrappers.py index 5bb9761..d380b2e 100644 --- a/day9/task5_vue/backend/database/wrappers.py +++ b/day9/task5_vue/backend/database/wrappers.py @@ -46,7 +46,7 @@ class MySQLWrapper(Wrapper): self.connection = MySQLdb.connect( host=host, user=username, - passwd=password, + password=password, db=db_name ) @@ -54,19 +54,18 @@ class MySQLWrapper(Wrapper): self.connection.close() def clear_table(self, table_name): - cursor = self.connection.cursor() - cursor.execute(f"START TRANSACTION; DELETE FROM `{table_name}`; COMMIT;") - cursor.close() + with self.connection.cursor() as cursor: + cursor.execute(f"DELETE FROM `{table_name}`;") def get_column_names(self): - cursor = self.connection.cursor() - cursor.execute('DESCRIBE table_task1;') - table_structure = cursor.fetchall() + with self.connection.cursor() as cursor: + cursor.execute('DESCRIBE table_task1;') + table_structure = cursor.fetchall() + table_headers = [field[0] for field in table_structure] return table_headers def insert_one(self, table_name, data_row: dict): - cursor = self.connection.cursor() scheme = self.schemes[table_name] field_names = [] @@ -74,21 +73,23 @@ class MySQLWrapper(Wrapper): for field_name, value in data_row.items(): field_names.append(f'`{field_name}`') - if scheme.fields[field_name].data_type == str: + if scheme.fields[field_name].nullable and value is None: + values.append('NULL') + elif scheme.fields[field_name].data_type == str: values.append(f'"{value}"') else: - values.append(value) + values.append(str(value)) - request = "START TRANSACTION; INSERT INTO `{}` ({}) VALUES ({}); COMMIT;".format( + request = "INSERT INTO `{}` ({}) VALUES ({});".format( table_name, ",".join(field_names), ",".join(values) ) - cursor.execute(request) - cursor.close() + with self.connection.cursor() as cursor: + cursor.execute(request) - def update(self, table_name, expressions, conditions): - cursor = self.connection.cursor() + self.connection.commit() + def update(self, table_name, expressions, conditions): expressions_formatted = [] for field_name, value in expressions.items(): if value != 'NULL' or not value.isnumeric(): @@ -101,48 +102,43 @@ class MySQLWrapper(Wrapper): value = f'"{value}"' conditions_formatted.append(f'`{field_name}`={value}') - cursor.execute("START TRANSACTION; UPDATE `{}` SET {} WHERE {}; COMMIT;".format( - table_name, ','.join(expressions_formatted), ' AND '.join(conditions_formatted) - )) + with self.connection.cursor() as cursor: + cursor.execute("UPDATE `{}` SET {} WHERE {};".format( + table_name, ','.join(expressions_formatted), ' AND '.join(conditions_formatted) + )) - cursor.close() + self.connection.commit() def delete_from(self, table_name, conditions): - cursor = self.connection.cursor() - conditions_formatted = [] for field_name, value in conditions.items(): if value != 'NULL' or not value.isnumeric(): value = f'"{value}"' conditions_formatted.append(f'`{field_name}`={value}') - cursor.execute("START TRANSACTION; DELETE FROM `{}` WHERE {}; COMMIT;".format( - table_name, ' AND '.join(conditions_formatted) - )) + with self.connection.cursor() as cursor: + cursor.execute("DELETE FROM `{}` WHERE {};".format( + table_name, ' AND '.join(conditions_formatted) + )) - cursor.close() + self.connection.commit() def get_data(self, table_name): - cursor = self.connection.cursor() + with self.connection.cursor() as cursor: + cursor.execute(f'SELECT * FROM `{table_name}`;') + content = list(map(list, cursor.fetchall())) - cursor.execute(f'SELECT * FROM `{table_name}`;') - content = list(map(list, cursor.fetchall())) - - cursor.close() return content def get_rows(self, table_name, conditions): - cursor = self.connection.cursor() - conditions_formatted = [] for field_name, value in conditions.items(): if value != 'NULL' or not value.isnumeric(): value = f'"{value}"' conditions_formatted.append(f'`{field_name}`={value}') - cursor.execute(f'SELECT * FROM `{table_name}` WHERE {" AND ".join(conditions_formatted)};') - content = list(map(list, cursor.fetchall())) - - cursor.close() + with self.connection.cursor() as cursor: + cursor.execute(f'SELECT * FROM `{table_name}` WHERE {" AND ".join(conditions_formatted)};') + content = list(map(list, cursor.fetchall())) return content -- cgit v1.2.3