diff options
| -rw-r--r-- | day9/task5/database.py | 16 | ||||
| -rw-r--r-- | day9/task5/index.html | 236 | ||||
| -rw-r--r-- | day9/task5/main.py | 47 | ||||
| -rw-r--r-- | day9/task5/server.py | 3 | ||||
| -rw-r--r-- | day9/task5/utils.py | 31 |
5 files changed, 163 insertions, 170 deletions
diff --git a/day9/task5/database.py b/day9/task5/database.py index 067c481..9b958b7 100644 --- a/day9/task5/database.py +++ b/day9/task5/database.py @@ -1,7 +1,19 @@ import MySQLdb from config import * +import logging + +def db_column_names(): + cursor = db.cursor() + cursor.execute('DESCRIBE table_task1;') + table_structure = cursor.fetchall() + table_headers = [field[0] for field in table_structure] + return table_headers + + +logger = logging.getLogger('tableApp') +logger.info(f'Trying to connect to database "{DATABASE_NAME}@{HOST}"...') # В файле config.py создайте соответствующие переменные db = MySQLdb.connect( host=HOST, @@ -9,7 +21,9 @@ db = MySQLdb.connect( passwd=PASSWORD, db=DATABASE_NAME ) +logger.info('Connected') +logger.info(f'Preparing table "table_task1"...') db.cursor().execute( ''' CREATE TABLE IF NOT EXISTS `table_task1` ( @@ -26,4 +40,4 @@ db.cursor().execute( PRIMARY KEY (`service_id`) ) ENGINE=InnoDB AUTO_INCREMENT=35109400 DEFAULT CHARSET=utf8; ''' -)
\ No newline at end of file +) diff --git a/day9/task5/index.html b/day9/task5/index.html index 7fe81bb..798fb92 100644 --- a/day9/task5/index.html +++ b/day9/task5/index.html @@ -11,59 +11,46 @@ } thead > tr > th { - padding: 10px; - font-size: 20px; + padding: 6px; + font-size: 16px; background-color: #6f6f6f; } td { - padding: 5px; - font-size: 17px; + padding: 3px; + font-size: 13px; } + </style> - input[type="date"], input[type="time"] { + <style type="text/css"> + .formInput[type="date"], .formInput[type="time"] { padding: 7px; font-size: 1.3em; } - input { + .formInput { margin: 3px 0; } + </style> - #table_operations { - margin: 30px; - font-size: 20px; - } - + <style type="text/css"> .odd { background-color: #dedede; } .even { background-color: #c5c5c5; } - .keyword { color: blue; } + .editableField:hover { + background-color: rgba(0, 0, 0, 0.3); + } </style> </head> -<body onload="loadDefaultOperation()"> - - <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> - +<body onload="onPageLoad()"> <div> <form action="/update"> - <label> service_id <input type="text" name="service_id" disabled></label> <br> - <label> servtype <input type="text" name="servtype"></label> <br> - <label> subtype <input type="text" name="subtype"></label> <br> - <label> user_id <input type="text" name="user_id"></label> <br> - <label> referrer_user_id <input type="text" name="referrer_user_id"></label> <br> + <label> service_id <input type="text" name="service_id" class="formInput" disabled></label> <br> + <label> servtype <input type="text" name="servtype" class="formInput"></label> <br> + <label> subtype <input type="text" name="subtype" class="formInput"></label> <br> + <label> user_id <input type="text" name="user_id" class="formInput"></label> <br> + <label> referrer_user_id <input type="text" name="referrer_user_id" class="formInput"></label> <br> <label> state <select name="state"> @@ -74,14 +61,17 @@ <option>O</option> </select> </label><br> - <label> creation_date <input type="date" name="creation_date"></label> <br> - <label> creation_time <input type="time" name="creation_time"></label> <br> + <label> creation_date <input type="date" name="creation_date" class="formInput"></label> <br> + <label> creation_time <input type="time" name="creation_time" class="formInput"></label> <br> <label> creation_request_sent_date - <input type="date" name="creation_request_sent_date"> - <input type="time" name="creation_request_sent_time"> + <input type="date" name="creation_request_sent_date" class="formInput"> + <input type="time" name="creation_request_sent_time" class="formInput"> + </label> <br> + <label> + notified_about_expiration + <input type="text" name="notified_about_expiration" class="formInput"> </label> <br> - <label> notified_about_expiration <input type="text" name="notified_about_expiration"></label> <br> <button type="submit">Submit</button> </form> @@ -90,43 +80,6 @@ <table id="table"></table> <script> - let queryHTMLElements = { - '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': ` - <span class="keyword">WHERE</span><br> - - <div id="conditions"></div> - <button type="button" onclick="addCondition()">Добавить условие</button> - `, - - '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]; - - request.post('http://localhost:8000/get', renderTable, {'type': 'full'}); - } - const request = { get: function (url, callback) { let xmlHttp = new XMLHttpRequest(); @@ -155,6 +108,65 @@ } }; + const defaultColumnInputs = { + 'service_id': `<input type="text">`, + 'servtype': `<input type="text">`, + 'subtype': `<input type="text">`, + 'user_id': `<input type="text">`, + 'referrer_user_id': `<input type="text">`, + 'state': `<select> + <option>N</option> + <option>A</option> + <option>S</option> + <option>D</option> + <option>O</option> + </select>`, + + 'creation_date': `<input type="date">`, + 'creation_time': `<input type="time">`, + 'creation_request_sent_date': `<input type="date"><input type="time">`, + 'notified_about_expiration': `<input type="text">` + }; + + function onPageLoad() { + request.post('http://localhost:8000/get', renderTable, {'type': 'full'}); + } + + function onFieldClick(fieldId) { + let fieldElement = document.getElementById(fieldId); + + if (fieldElement.firstChild.nodeName !== 'INPUT' && fieldElement.firstChild.nodeName !== 'SELECT') { + let [columnName, serviceId] = fieldId.split('-'); + fieldElement.innerHTML = defaultColumnInputs[columnName]; + if (columnName === 'creation_request_sent_date') { + let dateElement = fieldElement.firstChild; + let timeElement = fieldElement.childNodes[1]; + + dateElement.onkeyup = timeElement.onkeyup = (event) => { + if (event.code === 'Enter') { + if (dateElement.value !== '' && timeElement.value !== '') + fieldEditSubmit(fieldId, `${dateElement.value} ${timeElement.value}`); + } + }; + } + else { + let inputElement = fieldElement.firstChild; + inputElement.onkeyup = (event) => { + if (event.code === 'Enter') + fieldEditSubmit(fieldId, inputElement.value); + }; + } + } + } + + function fieldEditSubmit(fieldId, value) { + let [columnName, serviceId] = fieldId.split('-'); + request.post('http://localhost:8000/update', () => {}, { + 'service_id': serviceId, [columnName]: value + }); + document.getElementById(fieldId).innerHTML = value; + } + function renderTable(text) { let data = JSON.parse(text); @@ -163,25 +175,32 @@ let tableHeaders = document.createElement('thead'); let headerRow = document.createElement('tr'); tableHeaders.appendChild(headerRow); - for (let field of data['headers']) { + data['headers'].forEach((field) => { let header = document.createElement('th'); header.innerText = field; headerRow.appendChild(header); - } + }); headerRow.appendChild(document.createElement('th')); headerRow.appendChild(document.createElement('th')); let tableContent = document.createElement('tbody'); data['content'].forEach((row, rowIndex) => { - row.push(`<button value="${row[0]}">Edit</button>`); - row.push(`<button value="${row[0]}">Remove</button>`); + row.push(`<button value="${row[0]}" onclick="setupEditFields('${row[0]}')">✎</button>`); + row.push(`<button value="${row[0]}" onclick="removeField('${row[0]}')">✖</button>`); let contentRow = document.createElement('tr'); row.forEach((column, colIndex) => { let color = (colIndex % 2 + rowIndex % 2) % 2 === 0 ? 'odd' : 'even'; let columnNode = document.createElement('td'); - columnNode.classList.add(color); + if (colIndex !== 0 && colIndex < data['headers'].length) { + let fieldId = `${data['headers'][colIndex]}-${row[0]}`; + columnNode.classList.add('editableField'); + columnNode.onclick = () => { onFieldClick(fieldId) }; + columnNode.setAttribute('id', fieldId); + } + + columnNode.classList.add(color); columnNode.innerHTML = column; contentRow.appendChild(columnNode); }); @@ -193,55 +212,24 @@ table.appendChild(tableContent); } - 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 setupEditFields(service_id) { + request.post('http://localhost:8000/get', (text) => { + let row = JSON.parse(text); - 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}"> - <span class="keyword">AND</span>`; - - document.getElementById('conditions').appendChild(element); + Object.keys(row).forEach((element) => { + document.getElementsByName(element)[0].value = row[element]; + }); - conditionsCount++; + let [sent_date, sent_time] = row['creation_request_sent_date'].split(' '); + document.getElementsByName('creation_request_sent_date')[0].value = sent_date; + document.getElementsByName('creation_request_sent_time')[0].value = sent_time; + }, { + 'type': 'single_id', 'service_id': service_id + }); } - 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; + function removeField(service_id) { + console.log(service_id); } </script> </body> diff --git a/day9/task5/main.py b/day9/task5/main.py index 738afbe..d5bcbb8 100644 --- a/day9/task5/main.py +++ b/day9/task5/main.py @@ -1,21 +1,29 @@ from router import route -from utils import render_template, parse_query, NOT_FOUND_CODE -from database import db +from utils import render_template, NOT_FOUND_CODE from config import SERVER_HOST, SERVER_PORT +import logging +from sys import stdout + @route('/update', ['POST']) def update_post(query, *args): - expressions, conditions = parse_query(query) + service_id = query['service_id'] + permitted_fields = db_column_names() + permitted_fields.remove('service_id') + + query_set = [] + for field_name, value in query.items(): + if field_name in permitted_fields: + query_set.append(f'{field_name}="{value}"') cursor = db.cursor() - cursor.execute('UPDATE `table_task1` SET {} WHERE {}'.format( - expressions, conditions + cursor.execute("UPDATE `table_task1` SET {} WHERE {};".format( + ','.join(query_set), f'service_id="{service_id}"' )) - result = cursor.fetchall() cursor.close() - return f'<h1>UPDATE: {result}</h1>' + return f'<h1>Database Updated</h1>' @route('/delete', ['POST']) @@ -30,19 +38,15 @@ def add_post(query, *args): @route('/get', ['POST']) def db_get(query, *args): + table_headers = db_column_names() cursor = db.cursor() if query['type'] == 'full': - cursor.execute('DESCRIBE table_task1;') - table_structure = cursor.fetchall() - cursor.execute('SELECT * FROM table_task1;') content = cursor.fetchall() cursor.close() - table_headers = [field[0] for field in table_structure] - json_content = [] for row in content: new_row = [] @@ -53,6 +57,7 @@ def db_get(query, *args): json_content.append(new_row) return {'headers': table_headers, 'content': json_content} + elif query['type'] == 'single_id': cursor.execute(f'SELECT * FROM table_task1 WHERE service_id="{query["service_id"]}";') content = cursor.fetchone() @@ -65,7 +70,7 @@ def db_get(query, *args): col = str(col) json_content.append(col) - return json_content + return dict(zip(table_headers, json_content)) return NOT_FOUND_CODE @@ -73,10 +78,24 @@ def db_get(query, *args): @route('/') def index_get(query, *args): data = render_template('index.html') - return data +def prepare_logger(): + logger = logging.getLogger('tableApp') + logger.setLevel(logging.INFO) + + sh = logging.StreamHandler(stdout) + formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') + sh.setFormatter(formatter) + logger.addHandler(sh) + + if __name__ == '__main__': + prepare_logger() + from server import start_server + from database import db, db_column_names + + logging.getLogger('tableApp').info(f'Starting server...') start_server(SERVER_HOST, SERVER_PORT) diff --git a/day9/task5/server.py b/day9/task5/server.py index 47e29f9..a6044bd 100644 --- a/day9/task5/server.py +++ b/day9/task5/server.py @@ -5,6 +5,8 @@ from json import dumps from router import run from utils import HTTP_STATUS_CODES +import logging + class MyHTTPRequestHandler(BaseHTTPRequestHandler): def _set_response(self, code, content_type): @@ -57,4 +59,5 @@ class MyHTTPRequestHandler(BaseHTTPRequestHandler): def start_server(host, port): server_address = (host, port) httpd = HTTPServer(server_address, MyHTTPRequestHandler) + logging.getLogger('tableApp').info(f'Server started on {host}:{port}') httpd.serve_forever() diff --git a/day9/task5/utils.py b/day9/task5/utils.py index 5a48a9f..3e0dc64 100644 --- a/day9/task5/utils.py +++ b/day9/task5/utils.py @@ -74,34 +74,3 @@ def render_template(path, **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 = ' AND '.join(conditions) - - return {'expressions': expressions, 'conditions': conditions} |