Using SphinxSearch in Python

An application that uses Sphinx along with a database usually does this:

sends a search query to Sphinx,
receives results from Sphinx,
sends a query to the database to get details that will be displayed to users.

Prepare the environment

$ sudo dnf install mariadb-devel
(venv) $ pip install mariadb

Test the connection from Python

from mariadb import connect
con = connect(host='127.0.0.1', port=9306)
cur = con.cursor()
cur.execute("select * from jrnl where match({});".format('linux'))
res = cur.fetchall()
# Retrive searched objects from database
jrnls = Jrnl.query.filter(Jrnl.id.in_(res)).all()

Paginate with SphinxSearch

Pagination should be done using the search engine:

$ mysql -h127.0.0.1 -P9306
 > select * from jrnl where match('linux');
 > show meta;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 6     |
| total_found   | 6     |
| time          | 0.046 |
| keyword[0]    | flask |
| docs[0]       | 6     |
| hits[0]       | 15    |
+---------------+-------+

cur.execute("SELECT id FROM {} WHERE match({}) LIMIT {},{};".format(index, query, limit, offset))
res = cur.fetchall()

cur.execute("SHOW meta")
meta = cur.fetchall()
# find row with 'total' and retrive it's value
total = int([item[1] for item in meta if item[0] == 'total'][0])

Better architecture

To learn the better architecture, take a look on https://github.com/lukasszz/lhcom4 and check:

class app.models.SearchableMixin
app.main.routes.search
app.search.query_index(index, query, page, per_page)

Resoruces

http://sphinxsearch.com/blog/2014/03/28/basics-of-paginating-results/