summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAndrew <saintruler@gmail.com>2019-07-02 19:43:15 +0400
committerAndrew <saintruler@gmail.com>2019-07-02 19:43:15 +0400
commit048932c01b2c3031c6f8f3738ab9066c532c317b (patch)
treede750e1d703500484c1c708875becfd1ff03f3eb
parent155aba6c88cc651d2639ff2f11b0bbdb956cd4b8 (diff)
WIP: Фронтенд, работа с базами данных
Diffstat
-rw-r--r--day9/task5/index.html164
-rw-r--r--day9/task5/main.py137
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()
generated by cgit v1.2.3 (git 2.25.1) at 2025-12-16 06:54:08 +0000