1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
.. _sql engines:
===========
SQL Engines
===========
.. sidebar:: further read
- `SQLite <https://www.sqlite.org/index.html>`_
- `PostgreSQL <https://www.postgresql.org>`_
- `MySQL <https://www.mysql.com>`_
With the *SQL engines* you can bind SQL databases into SearXNG. The following
Relational Database Management System (RDBMS) are supported:
- :ref:`engine sqlite`
- :ref:`engine postgresql`
- :ref:`engine mysql_server`
All of the engines above are just commented out in the :origin:`settings.yml
<searx/settings.yml>`, as you have to set the required attributes for the
engines, e.g. ``database:`` ...
.. code:: yaml
- name: ...
engine: {sqlite|postgresql|mysql_server}
database: ...
result_template: {template_name}
query_str: ...
By default, the engines use the ``key-value`` template for displaying results /
see :origin:`simple <searx/templates/simple/result_templates/key-value.html>`
theme. If you are not satisfied with the original result layout, you can use
your own template, set ``result_template`` attribute to ``{template_name}`` and
place the templates at::
searx/templates/{theme_name}/result_templates/{template_name}
If you do not wish to expose these engines on a public instance, you can still
add them and limit the access by setting ``tokens`` as described in section
:ref:`private engines`.
Configure the engines
=====================
The configuration of the new database engines are similar. You must put a valid
SQL-SELECT query in ``query_str``. At the moment you can only bind at most one
parameter in your query. By setting the attribute ``limit`` you can define how
many results you want from the SQL server. Basically, it is the same as the
``LIMIT`` keyword in SQL.
Please, do not include ``LIMIT`` or ``OFFSET`` in your SQL query as the engines
rely on these keywords during paging. If you want to configure the number of
returned results use the option ``limit``.
.. _engine sqlite:
SQLite
------
.. sidebar:: info
- :origin:`sqlite.py <searx/engines/sqlite.py>`
.. _MediathekView: https://mediathekview.de/
SQLite is a small, fast and reliable SQL database engine. It does not require
any extra dependency. To demonstrate the power of database engines, here is a
more complex example which reads from a MediathekView_ (DE) movie database. For
this example of the SQlite engine download the database:
- https://liste.mediathekview.de/filmliste-v2.db.bz2
and unpack into ``searx/data/filmliste-v2.db``. To search the database use e.g
Query to test: ``!mediathekview concert``
.. code:: yaml
- name: mediathekview
engine: sqlite
disabled: False
categories: general
result_template: default.html
database: searx/data/filmliste-v2.db
query_str: >-
SELECT title || ' (' || time(duration, 'unixepoch') || ')' AS title,
COALESCE( NULLIF(url_video_hd,''), NULLIF(url_video_sd,''), url_video) AS url,
description AS content
FROM film
WHERE title LIKE :wildcard OR description LIKE :wildcard
ORDER BY duration DESC
Extra Dependencies
------------------
For using :ref:`engine postgresql` or :ref:`engine mysql_server` you need to
install additional packages in Python's Virtual Environment of your SearXNG
instance. To switch into the environment (:ref:`searx-src`) you can use
:ref:`searx.sh`::
$ sudo utils/searx.sh shell
(searx-pyenv)$ pip install ...
.. _engine postgresql:
PostgreSQL
----------
.. _psycopg2: https://www.psycopg.org/install
.. sidebar:: info
- :origin:`postgresql.py <searx/engines/postgresql.py>`
- ``pip install`` psycopg2_
PostgreSQL is a powerful and robust open source database. Before configuring
the PostgreSQL engine, you must install the dependency ``psychopg2``. You can
find an example configuration below:
.. code:: yaml
- name: my_database
engine: postgresql
database: my_database
username: searxng
password: password
query_str: 'SELECT * from my_table WHERE my_column = %(query)s'
.. _engine mysql_server:
MySQL
-----
.. sidebar:: info
- :origin:`mysql_server.py <searx/engines/mysql_server.py>`
- ``pip install`` :pypi:`mysql-connector-python <mysql-connector-python>`
MySQL is said to be the most popular open source database. Before enabling MySQL
engine, you must install the package ``mysql-connector-python``.
The authentication plugin is configurable by setting ``auth_plugin`` in the
attributes. By default it is set to ``caching_sha2_password``. This is an
example configuration for quering a MySQL server:
.. code:: yaml
- name: my_database
engine: mysql_server
database: my_database
username: searxng
password: password
limit: 5
query_str: 'SELECT * from my_table WHERE my_column=%(query)s'
Acknowledgment
==============
This development was sponsored by `Search and Discovery Fund
<https://nlnet.nl/discovery>`_ of `NLnet Foundation <https://nlnet.nl/>`_.
|
