WIP: Фронтенд, работа с базами данных

1 files changed, 130 insertions, 7 deletions

diff --git a/day9/task5/main.py b/day9/task5/main.py
index 8cdf7a1..795dc19 100644
--- a/day9/task5/main.py
+++ b/day9/task5/main.py
@@ -1,13 +1,102 @@
 from http.server import HTTPServer, BaseHTTPRequestHandler
-import logging
+from urllib.parse import parse_qs
+import re
+
+import MySQLdb
+
+from config import *
+
+
+def render_template(path, **kwargs):
+    with open(path) as f:
+        template = f.read()
+
+    for key in kwargs:
+        template = template.replace(f'%%{key}%%', kwargs[key])
+
+    return template
+
+
+def parse_query(query):
+    parsed_query = {'expression': {}, 'condition': {}}
+
+    pattern = re.compile(r'(expression|condition)(\d+)_(key|value)')
+
+    for key, (value,) in query.items():
+        match = pattern.fullmatch(key)
+        if match is not None:
+            index = int(match.group(2))
+            if index not in parsed_query[match.group(1)]:
+                parsed_query[match.group(1)][index] = {}
+
+            parsed_query[match.group(1)][index][match.group(3)] = value
+
+    expressions = []
+    for expression in parsed_query['expression'].values():
+        expressions.append('`{}`="{}"'.format(
+            expression['key'], expression['value']
+        ))
+    expressions = ','.join(expressions)
+
+    conditions = []
+    for condition in parsed_query['condition'].values():
+        conditions.append('`{}`="{}"'.format(
+            condition['key'], condition['value']
+        ))
+    conditions = ','.join(conditions)
+
+    return {'expressions': expressions, 'conditions': conditions}
 
 
 class MyHTTPRequestHandler(BaseHTTPRequestHandler):
     def update_post(self, query):
-        return f'<h1>{query}</h1>'
+        expressions, conditions = parse_query(query)
+
+        cursor = db.cursor()
+        cursor.execute('UPDATE `table_task1` SET {} WHERE {}'.format(
+            expressions, conditions
+        ))
+        result = cursor.fetchall()
+        cursor.close()
+
+        return f'<h1>UPDATE: {result}</h1>'
+
+    def delete_post(self, query):
+        return f'<h1>DELETE: {query}</h1>'
+
+    def add_post(self, query):
+        return f'<h1>ADD: {query}</h1>'
 
     def index_get(self):
-        return 'INDEX!!!!'
+        cursor = db.cursor()
+        cursor.execute('DESCRIBE table_task1;')
+        table_structure = cursor.fetchall()
+
+        cursor.execute('SELECT * FROM table_task1;')
+        content = cursor.fetchall()
+
+        cursor.close()
+
+        heading = []
+        for column in [field[0] for field in table_structure]:
+            heading.append(f'<th>{column}</th>')
+        heading = '<thead><tr>\n%s\n</tr></thead>' % '\n'.join(heading)
+
+        rows = []
+        for row_index, row in enumerate(content):
+            formatted = []
+            for field_index, field in enumerate(row):
+                color = 'odd' if (field_index % 2 + row_index % 2) % 2 == 0 else 'even'
+
+                formatted.append(f'<td class={color}>{field}</td>')
+
+            rows.append('<tr>{}</tr>'.format(''.join(formatted)))
+
+        body = '<tbody>{}</tbody>'.format("\n".join(rows))
+
+        data = render_template('index.html', heading=heading, body=body)
+
+        return data
 
     def _set_response(self):
         self.send_response(200)
@@ -25,16 +114,50 @@ class MyHTTPRequestHandler(BaseHTTPRequestHandler):
 
     def do_POST(self):
         content_length = int(self.headers['Content-Length'])
-        post_data = self.rfile.read(content_length).decode('utf-8')
+        post_data = parse_qs(self.rfile.read(content_length).decode('utf-8'))
+
+        if self.path == '/update':
+            response = self.update_post(post_data)
+
+        elif self.path == '/delete':
+            response = self.delete_post(post_data)
+
+        elif self.path == '/add':
+            response = self.add_post(post_data)
 
-        response = self._router['POST'].get(
-            self.path, lambda *args: '<center><h1>ERROR 404 NOT FOUND</h1></center>'
-        )(post_data)
+        else:
+            response = '<center><h1>ERROR 404 NOT FOUND</h1></center>'
 
         self._set_response()
         self.wfile.write(response.encode('utf-8'))
 
 
+# В файле config.py создайте соответствующие переменные
+db = MySQLdb.connect(
+    host=HOST,
+    user=USERNAME,
+    passwd=PASSWORD,
+    db=DATABASE_NAME
+)
+
+db.cursor().execute(
+    '''
+    CREATE TABLE IF NOT EXISTS `table_task1` (
+      `service_id` int(11) NOT NULL AUTO_INCREMENT,
+      `servtype` varchar(20) NOT NULL DEFAULT 'hosting',
+      `subtype` varchar(32) NOT NULL DEFAULT '',
+      `user_id` bigint(20) NOT NULL,
+      `referrer_user_id` bigint(20) NOT NULL,
+      `state` varchar(1) NOT NULL DEFAULT 'N',
+      `creation_date` date NOT NULL DEFAULT '0000-01-01',
+      `creation_time` time NOT NULL DEFAULT '00:00:00',
+      `creation_request_sent_date` datetime DEFAULT NULL,
+      `notified_about_expiration` smallint(6) NOT NULL DEFAULT '0',
+      PRIMARY KEY (`service_id`)
+    ) ENGINE=InnoDB AUTO_INCREMENT=35109400 DEFAULT CHARSET=utf8;
+    '''
+)
+
 server_address = ("", 8000)
 httpd = HTTPServer(server_address, MyHTTPRequestHandler)
 httpd.serve_forever()