How to implement search in sqlite in python?

Question

Python

Hello everyone, I'm making a bot in Telegram. Tell me how to implement a search engine for the SQLite database. Python just started learning, so this is the only thing I could come up with

async def sql_search(message):
search_query = message.text
await bot.send_message(message.from_user.id, search_query)
for ret in cur.execute('SELECT description, guide FROM guides WHERE keys LIKE ?', ('%'+search_query+'%',)).fetchall():
await bot.send_message(message.from_user.id, f'{ret[0]}\nRecipe:\n{ret[1 ]}')

I wrote keys for each article. For example: make cook scrambled eggs with vegetables and sausage scrambled eggs with sausage
As you understand, the following effect has arisen. Entering 'cook' returns all articles with the given key. 'Scrambled eggs with vegetables' is looking for, but 'Scrambled eggs with vegetables' is no longer available.

Please send articles or guides where this issue could be studied. And in general, did I approach the matter correctly?

Answer 1

LIKE searches for an exact match of part of a string, i.e. '%яичница с овощами%'finds only strings containing the substring 'scrambled eggs with vegetables' verbatim and preliteral.
You, apparently, want some kind of full-text search. sqlite has a pretty interesting built-in , try to figure it out and customize to your needs.
If this option does not work, then you can do something of your own on your knee:
1. break the tags into separate words (and place them in a separate keywords table associated with the tags)
2. filter out unnecessary words like "cook"
3. normalize the words - here you will need a stemmer or a dictionary (I think you can find a library for python, but I'm not sure about the support of the Russian language)
4. also pass the request through steps 1-3
5. look further like this:

SELECT tag_id, count(*)
  FROM keywords
 WHERE word IN ('query_word1', 'query_word2' ...)
 GROUP BY tag_id HAVING count(*) = <number of words in query>

Answer 2

It's called "fuzzy search" and it's not an easy topic. As one joke says, "length, width, height and depth are not at all the same as length, width, height and depth."

Answer 3

A neural network should be used.
Of course, she needs to be trained (there are hardly any ready-made solutions on the Internet), but you can apply for freelance and wherever you want, even on YouTube for videos.