CODEWITHSUNDEEP
pythonmysqlsqlpython-2.7mysql-python
Hado
Hado

Reputation: 39

MySQL Exact phrase match

I'm using python and the mysqldb module to search a database for a user input. I have tested the python and established a connection to the server and database. It's the SQL code giving me trouble. My SQl is as follows:

SELECT * FROM panda WHERE MATCH(Question) AGAINST('"{0}"')

I'm using python so the {0} is the user input. that bit works. For some reason it won't match the exact phrase to whats in the column on the database. For example, if the user inputs 'See you' the database matches it as 'I want to see you' not just 'See you'. Is there any way to get the specific phrase? I have tried using boolean mode and regex but they don't work.

edit: Thank you all. All of your methods worked, it just turned out I hadn't formatted the SQL correctly. sorry for all the trouble.

Upvotes: 0

Views: 942

Answers (4)

O. Jones
O. Jones

Reputation: 108841

MATCH ... AGAINST, also known as FULLTEXT search, is performing as designed for you. Its purpose is to dig into quantities of text and find rows that contain matching text to your query.

Notice that a FULLTEXT index only sees the first 750 or so characters of the text in the column.

It's useful functionality, because it works even if random punctuation or other words show up in the text. WHERE column = 'value' demands exact matches.

You might try this query: It will present the results roughly in increasing length order. This will push "See you" ahead of "I want to see you" in the order of results.

SELECT * 
  FROM panda
 WHERE MATCH(Question) AGAINST('"{0}"')
 ORDER BY CEIL(LOG2(LENGTH(Question))) ASC,
       MATCH(Question) AGAINST('"{0}"')

Notice that the expression MATCH(Question) AGAINST('"{0}"') produces a number specifying the closeness of the match when you use it in a SELECT or ORDER BY clause.

Notice also that the expression CEIL(LOG2(LENGTH(Question))) in your ORDER BY clause groups your results by chunks based on length. It groups lengths of 3-4 together, then lengths 5-8, 9-16, and so forth.

Upvotes: 0

sbhatla
sbhatla

Reputation: 1088

This is how to use python and sql together to search for a certain column 'Question' with a value equal to inputVar and get the entire entry in row.

import MySQLdb
db = MySQLdb.connect("localhost", "root", "password", "MY_DATABASE")
cursor=db.cursor()

sql="SELECT * FROM MY_TABLE WHERE Question = '%s';" %inputVar; 
    try:      
        cursor.execute(sql)
        row = cursor.fetchall()
        print row[0] #assuming 1st column is 'Question' column.
        db.commit()
    except:
        print ("ROLLBACK!!!")
        db.rollback()

Upvotes: 1

user3741598
user3741598

Reputation: 295

Then why don't use just use WHERE Question = '"{0}"'

Upvotes: 1

steffen
steffen

Reputation: 17058

Have a look at the documentation of MATCH ... AGAINST. What you want is a simple comparison, try something like this:

SELECT * FROM panda WHERE Question = {0};

Upvotes: 0

Related Questions