MySQL: do a basic search

Question

I have a table of names in my database, and I would like to conduct a fuzzy search on it, for example, my database contains:

Name         ID
John Smith   1
Edward Smith 2
Gabriel Gray 3
Paul Roberts 4

At that moment, when I search the database through python, I can only perform an exact match search. But I would like to be able to do a fuzzy search, where I can find the name "blacksmith" and return John Smith and Edward Smith.

Answer 1

In the simplest form, use a comparison LIKE:

SELECT * FROM table WHERE name LIKE '%smith%';

FULLTEXT ( ), SOUNDEX() ( , , " " "" ), levenshtein ..

Answer 2

import MySQLdb
search_str = 'smith'
conn = MySQLdb.connect(host="localhost", user="me",passwd="pw",db="mydb")
c = conn.cursor()
c.execute("SELECT name FROM mytable WHERE name LIKE %s", '%' + search_str + '%')
c.fetchall()
c.close()
conn.close()