CODEWITHSUNDEEP
mysqlpython-3.xpymysql
Sank_BE
Sank_BE

Reputation: 33

Running Full Search on a mysql Table

So I have already a table called "profiles" in my "mysql database". I am trying to search the record/records on it based on a text search. I tried running full text search, but it is not giving me anything:

import pymysql
mydb = pymysql.connect( host = 'a' ,  user ="b" ,  passwd = "x" , db = "project_db")  
cursor = mydb.cursor() 

query = """SELECT * FROM profiles WHERE MATCH(c1, c2, c3, c4, c5, c6, c7, c8)
AGAINST('cycle owner' IN NATURAL LANGUAGE MODE)"""

cursor.execute(query)
res = cursor.fetchall()

for r in res:
    print(r)
cursor.close()
mydb.commit()
mydb.close()

The output is as:

---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
<ipython-input-16-86948bcd8c0b> in <module>
      6 AGAINST('cycle owner' IN NATURAL LANGUAGE MODE)"""
      7 
----> 8 cursor.execute(query)
      9 res = cursor.fetchall()
     10 

D:\Softwares\Anaconda\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    168         query = self.mogrify(query, args)
    169 
--> 170         result = self._query(query)
    171         self._executed = query
    172         return result

D:\Softwares\Anaconda\lib\site-packages\pymysql\cursors.py in _query(self, q)
    326         self._last_executed = q
    327         self._clear_result()
--> 328         conn.query(q)
    329         self._do_get_result()
    330         return self.rowcount

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
    515                 sql = sql.encode(self.encoding, 'surrogateescape')
    516         self._execute_command(COMMAND.COM_QUERY, sql)
--> 517         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    518         return self._affected_rows
    519 

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
    730         else:
    731             result = MySQLResult(self)
--> 732             result.read()
    733         self._result = result
    734         if result.server_status is not None:

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in read(self)
   1073     def read(self):
   1074         try:
-> 1075             first_packet = self.connection._read_packet()
   1076 
   1077             if first_packet.is_ok_packet():

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    682 
    683         packet = packet_type(buff, self.encoding)
--> 684         packet.check_error()
    685         return packet
    686 

D:\Softwares\Anaconda\lib\site-packages\pymysql\protocol.py in check_error(self)
    218             errno = self.read_uint16()
    219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)
    221 
    222     def dump(self):

D:\Softwares\Anaconda\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    107         errval = data[3:].decode('utf-8', 'replace')
    108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)

InternalError: (1191, "Can't find FULLTEXT index matching the column list")

Can anyone help me and tell why it is showing no search result and hence give me a heads up?

Thanks!!

Upvotes: 0

Views: 113

Answers (1)

Ozan Akcora
Ozan Akcora

Reputation: 36

In order to use Full Text search, first you need to create the full text index on related columns.

You can use the following syntax to add fulltext index to existing table.

ALTER TABLE table_name  
ADD FULLTEXT(column_name1, column_name2,…)

Upvotes: 1

Related Questions