Skip to content

Instantly share code, notes, and snippets.

@justswan
Created August 12, 2016 13:51
Show Gist options
  • Save justswan/cf690a722c17cb414569f8370ae0205c to your computer and use it in GitHub Desktop.
Save justswan/cf690a722c17cb414569f8370ae0205c to your computer and use it in GitHub Desktop.
#!/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]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment