CODEWITHSUNDEEP
pythonmysql
Jim
Jim

Reputation: 73

MySQL: conduct a basic search

I have a names table in my database and I would wish 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 the moment when I search the database via python I can only do exact match searching. But I would like to be able to do fuzzy searching where by I can search for the name "smith" and bring back John Smith and Edward Smith.

Upvotes: 3

Views: 368

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169284

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()

Upvotes: 0

Wrikken
Wrikken

Reputation: 70460

In simplest form, you'd use the LIKE comparison:

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

More elaborate searches can de done with FULLTEXT index (large amounts of text), SOUNDEX() (works on words in the english language, matching on other languages is everything from 'somewhat workable' to 'terrible'), levenshtein distance of words etc.

Upvotes: 5

Related Questions