diff options
| -rw-r--r-- | day9/task5/index.html | 164 | ||||
| -rw-r--r-- | day9/task5/main.py | 137 |
2 files changed, 286 insertions, 15 deletions
diff --git a/day9/task5/index.html b/day9/task5/index.html index 1785124..7d65bd9 100644 --- a/day9/task5/index.html +++ b/day9/task5/index.html @@ -1,9 +1,157 @@ -<html> - <head> - <title>Python is awesome!</title> - </head> - <body> - <h1>Afternerd</h1> - <p>Congratulations! The HTTP Server is working!</p> - </body> +<html lang="ru"> + +<head> + <meta charset="utf-8"> + <title>"table_task1" table view</title> + <style type="text/css"> + table { + border-spacing: 0; + padding: 0; + border: 1px solid black; + } + + thead > tr > th { + padding: 10px; + font-size: 20px; + background-color: #6f6f6f; + } + + td { + padding: 5px; + font-size: 17px; + } + + #table_operations { + margin: 30px; + font-size: 20px; + } + + .odd { + background-color: #dedede; + } + + .even { + background-color: #c5c5c5; + } + + .keyword { + color: blue; + } + + </style> +</head> + +<body onload="loadDefaultOperation()"> + <table> + %%heading%% + + %%body%% + </table> + + <div id="table_operations"> + <select onchange="typeChanged()" id="operation"> + <option>UPDATE</option> + <option>DELETE FROM</option> + <option>INSERT INTO</option> + </select> `table_task1`<br> + + <form method="post" action="/update" id="operation_form"> + <div id="query"></div> + <button type="submit">Выполнить запрос</button> + </form> + </div> + + <script> + let queryHTMLElements = { + 'UPDATE': ` + <input hidden name="operation" value="UPDATE"> + + <span class="keyword">SET</span><br> + + <div id="update_set"></div> + <button type="button" onclick="addUpdateSet()">Добавить условие</button><br> + + <span class="keyword">WHERE</span><br> + + <div id="conditions"></div> + <button type="button" onclick="addCondition()">Добавить условие</button> + `, + + 'DELETE FROM': ` + <input hidden name="operation" value="DELETE FROM"> + + <span class="keyword">WHERE</span><br> + + <div id="conditions"></div> + <button type="button" onclick="addCondition()">Добавить условие</button> + `, + + 'INSERT INTO': ` + <input hidden name="operation" value="INSERT INTO"> + + <span class="keyword">VALUES</span><br> + ` + }; + + const DEFAULT_OPERATION = 'UPDATE'; + + let expressionsCount = 0; + let conditionsCount = 0; + + function loadDefaultOperation() { + let query = document.getElementById('query'); + query.innerHTML = queryHTMLElements[DEFAULT_OPERATION]; + } + + function addUpdateSet() { + let element = document.createElement('div'); + + const keyName = `expression${expressionsCount}_key`; + const valueName = `expression${expressionsCount}_value`; + element.innerHTML = `<input type="text" name="${keyName}"> = <input type="text" name="${valueName}">`; + + document.getElementById('update_set').appendChild(element); + + expressionsCount++; + } + + function addCondition() { + let element = document.createElement('div'); + + const keyName = `condition${conditionsCount}_key`; + const valueName = `condition${conditionsCount}_value`; + element.innerHTML = `<input type="text" name="${keyName}"> = <input type="text" name="${valueName}">`; + + document.getElementById('conditions').appendChild(element); + + conditionsCount++; + } + + function typeChanged() { + let query = document.getElementById('query'); + let text = document.getElementById('operation').value; + query.innerHTML = queryHTMLElements[text]; + + let operationForm = document.getElementById('operation_form'); + switch (text) { + case 'UPDATE': + operationForm.method = 'POST'; + operationForm.action = '/update'; + break; + case 'DELETE FROM': + operationForm.method = 'POST'; + operationForm.action = '/delete'; + break; + case 'INSERT INTO': + operationForm.method = 'POST'; + operationForm.action = 'add'; + break; + } + + expressionsCount = 0; + conditionsCount = 0; + } + </script> +</body> + </html>
\ No newline at end of file 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() |