gistfile1.txt

#!/usr/bin/env python3
import sqlite3
import os
import sys
import json
import sys
import json
import re
from wsgiref.simple_server import make_server
from cgi import parse_qs, escape

table_ru = str.maketrans("йцукенгшщзхъфывапролджэячсмитьбю","qwertyuiop[]asdfghjkl;'zxcvbnm,.")
table_en = str.maketrans("qwertyuiop[]asdfghjkl;'zxcvbnm,.","йцукенгшщзхъфывапролджэячсмитьбю")

sqlite_file = 'mvideo_suggest.db'    # name of the sqlite database file
fd = os.open(sqlite_file, os.O_RDONLY)
conn = sqlite3.connect(sqlite_file)
conn.row_factory = lambda cursor, row: row[0]

httpvar='term'

def suggest(q):
        c = conn.cursor()
        orig = q
        ru2en = q.translate(table_ru)
        en2ru = q.translate(table_en)
        all_rows = c.execute('SELECT distinct request FROM requests AS requests1 JOIN razbors ON (razbor_id = razbors.id) WHERE (request LIKE "{org}%" OR request LIKE "{ruen}%" OR request LIKE "{enru}%") AND requests1.power IN (SELECT MAX(power) FROM requests WHERE (request LIKE "{org}%" OR request LIKE "{ruen}%" OR request LIKE "{enru}%") AND razbor_id = requests1.razbor_id) GROUP BY requests1.power, razbor_id ORDER BY requests1.power DESC LIMIT 10'.\
                        format(org=orig, ruen=ru2en, enru=en2ru)).fetchall()
        if (len(all_rows) < 10):
                new_rows = c.execute('SELECT distinct request FROM requests AS requests1 JOIN razbors ON (razbor_id = razbors.id) WHERE (request LIKE "% {org}%" OR request LIKE "% {ruen}%" OR request LIKE "% {enru}%") AND requests1.power IN (SELECT MAX(power) FROM requests WHERE (request LIKE "% {org}%" OR request LIKE "% {ruen}%" OR request LIKE "% {enru}%") AND razbor_id = requests1.razbor_id) GROUP BY requests1.power, razbor_id ORDER BY requests1.power DESC LIMIT {limit}'.\
                        format(org=orig, ruen=ru2en, enru=en2ru, limit=10-len(all_rows)))
                for row in c:
                        if (row not in all_rows):
                                all_rows.append(row)
        c.close()
        #conn.close()

        return json.dumps(all_rows, ensure_ascii=False)

def transform_q(q):
        q = re.sub("[\(\)!?;\"\`\/\[\]«»=#$@]", ' ', q)
        q = re.sub("[- .,:'*]* ", ' ', q)
        q = re.sub("[- .,:'*]*$", '', q)
        q = re.sub("\s{2,}", ' ', q)
        return q

def application(env, start_response):
        parameters = parse_qs(env.get('QUERY_STRING', ''))
        if httpvar in parameters and parameters[httpvar][0]:
                res = suggest(transform_q(escape(parameters[httpvar][0]))).encode('utf-8');
        else:
                res = "[]".encode('utf-8')

        start_response('200 OK', [
                ('Content-Type', 'application/json; charset=utf-8'),
                ('Content-Length', str(len(res)))
        ])
        return [res]