SQL_mutlisearch

Easy search engine into SQL tables with results raking and ordering.

Running

Package available on the following databases:

• sqlite

Installation with

pip install SQL_multisearch

Initialization of the SQL_multisearch class with a simple JSON Body:

from SQL_multisearch import SQL_multisearch

body = {
    "connexion_infos":{...},
    "request": {...},
    "searchable_fields": {...}
}

SQLm = SQL_mutlisearch(body["connexion_infos"], body["request"], body["searchable_fields"])

Available SQL_mutisearch methods

The following methods are available for the SQL_multisearch class:

• _count(): returns an integer with the number of results
• _stats(): returns a dictionary with following schema: {'ranks': {'rank_value': count}, 'total': total}
• _results(): returns a list of results with the following schema: [{'ranking': rank, 'values':{'field_name':'value', ...}}]
• _columns(): returns a list of the table fields

Database connexion details

The body["connexion_infos"] value is a dictionary of multiple values:

{"connexion_infos":{
    "db_system": "sqlite",
    "db_url": "path to the database or url",
    "db_database": "name of the database",
    "db_table": "table name",
    "db_schema": "schema name"
}}

Authorized request actions

Multiple actions are possible with the body["request"] dictionary. It defines the structure and the caracteristics of the return result:

{"request":{
    "value": "string or integer to search",
    "value_type": "str|int",
    "operande": "like|left_like|right_like|ilike|left_ilike|right_ilike|=|>|>=|<|<=",
    "limit": "get an integer",
    "offset": "get an integer",
    "order": "asc|desc, default=desc",
    "schema":["list of field names to return in the result"]
}}

Ranking and searchable fields

SQL_multiserach gives a rank to each result. The research into the database table is possible into multiples fields. In order to give a different rank for each field, a piority can be indicate in the body. The body["searchable_fields"] allow search in multiple fields with different priorities.

["searchable_fields": [
    {
        "field": "field name",
        "priority": "get an integer"
    },
    ]
]

Exemple

from SQL_multisearch import *

body = {
    "connexion_infos": {
        "db_system": db_type,
        "db_url": db_url,
        "db_database": db_db,
        "db_table": db_table,
        "db_schema": db_schema
    },
    "request":{
        "value": "halles",
        "value_type": "str",
        "operande": "ilike",
        "limit":10,
        "offset":30,
        "order": "asc",
        "schema":["N_inventaire", "Rue", "Nom_site"]
    },
    "searchable_fields": [
        {
            "field": "Rue",
            "priority": 2
        },
        {
            "field": "Nom_site",
            "priority":1
        }
    ]
}

SQLm = SQL_multisearch(body["connexion_infos"], body["request"], body["searchable_fields"])
print(SQLm._count())
print(SQLm._results())
print(SQLm._columns())
print(SQLm._stats())

>>2
>>[{'values': {'N_inventaire': 3898, 'Rue': 'PIERRE LESCOT, RUE', 'Nom_site': 'HALLES_BALTARD'}, 'ranking': 1.4285714285714286}, {'values': {'N_inventaire': 3899, 'Rue': 'PIERRE LESCOT, RUE', 'Nom_site': 'HALLES_BALTARD'}, 'ranking': 1.4285714285714286}, {'values': {'N_inventaire': 3900, 'Rue': 'PIERRE LESCOT, RUE', 'Nom_site': 'HALLES_BALTARD'}, 'ranking': 1.4285714285714286}, {'values': {'N_inventaire': 3901, 'Rue': 'PIERRE LESCOT, RUE', 'Nom_site': 'HALLES_BALTARD'}, 'ranking': 1.4285714285714286}, {'values': {'N_inventaire': 3902, 'Rue': 'PIERRE LESCOT, RUE', 'Nom_site': 'HALLES_BALTARD'}, 'ranking': 1.4285714285714286}, {'values': {'N_inventaire': 3903, 'Rue': 'PIERRE LESCOT, RUE', 'Nom_site': 'HALLES_BALTARD'}, 'ranking': 1.4285714285714286}, {'values': {'N_inventaire': 3905, 'Rue': 'PIERRE LESCOT, RUE', 'Nom_site': 'HALLES_BALTARD'}, 'ranking': 1.4285714285714286}, {'values': {'N_inventaire': 131, 'Rue': 'BERGER, RUE', 'Nom_site': 'LES_HALLES'}, 'ranking': 1.6}, {'values': {'N_inventaire': 132, 'Rue': 'BERGER, RUE', 'Nom_site': 'LES_HALLES'}, 'ranking': 1.6}, {'values': {'N_inventaire': 133, 'Rue': 'BERGER, RUE', 'Nom_site': 'LES_HALLES'}, 'ranking': 1.6}]
>>['N_inventaire', 'Rue', 'N_rue', 'Nom_site', 'Arrondissement', 'Ville', 'Latitude_x', 'Longitude_y', 'Support', 'Couleur', 'Taille', 'Date_prise_vue', 'Photographe', 'Date_construction', 'Architecte', 'Classement_MH', 'Generalite_architecture', 'Mot_cle1', 'Mot_cle2', 'Mot_cle3', 'Mot_cle4', 'Mot_cle5', 'Mot_cle6', 'Cote_base', 'Cote_classement', 'Date_inventaire', 'Auteur']
>>{'ranks': {'1.4285714285714286': 7, '1.6': 3}, 'total': 10}