Guys, such a question, I have a table in the database, called product, it contains:

name= 'Product name'
calories= 'the number of calories in the food'
protein= 'Amount of protein in the food'
fats= 'the amount of fat in the food'
carbohydrates= 'the amount of carbohydrates in the food'

For example: Name= Rice, Calories= 300, Protein= 10, Fat= 2, Carbohydrates= 70 Name= Buckwheat, Calories= 320, Protein= 16, Fat= 1, Carbohydrates= 65

The user enters Rice and wants to get the value of calories, protein, fat and carbohydrates in Rice, in the database it is under the name Rice, and suddenly the user enters Rice, Rusa or somehow makes a mistake, then nothing will be found in the database. Tell me how you can roughly implement it so that the most similar name is found in the database. I know about a fuzzy comparison of strings, but I don’t know how to associate this with the base, maybe there is a special query in sqlite, but I didn’t find anything

If the products are not 100,500, then you can read them all in python and there it is already through fuzzywuzzy to search for some fuzzy one. It will be difficult, I think, to make a fuzzy search right in the database. Although you can do like and replace one or two ... letters with%. And then from what has already got out to look for the nearest, if you do not want to pump out all the names.

CrazyElf2021-11-25 08:54:45

@CrazyElf is utopia. If the user enters rus instead of rice, and you need to show him a pic ... An application without an index triggers in the database will be terribly laggy.

Михаил Алексеевич2021-11-25 08:56:26

you can unload the entire list of product names from the database, use fuzzywuzzy to search, and unload the most suitable one from the database, I wanted to do so at first, but I thought that it would greatly slow down the whole process and thought it might be more humane to eat

Tesla2021-11-25 09:06:48

@Tesla you can. But with an increase in the number of products, such an algorithm will "slow down" more and more. When it gets close to 1000, for example, you will wait tens of seconds. When by 10,000 -up to an hour. I don’t think that will suit you.

Михаил Алексеевич2021-11-25 09:18:37
  • Answer # 1

    Can be used FTS5extension for SQLite

    Of the minuses -function trigram indexit's experimental.

    If I were you, I would take a closer look at PostgreSQL...

    I did not understand a little, you mean you dare to have a database on PostgreSQL.? Is it easier to implement it there?

    Tesla2021-11-25 09:10:44

    @Tesla yes, this is another DBMS in which trigram index has been implemented and works for a very long time. You just create such an index, and in the SQL query itself, write something of the type similar (value), at the output you get a list of results also with a degree of similarity to the query.

    Михаил Алексеевич2021-11-25 09:16:06