I am trying to search for a string in the entire database using Python.
The specific code is
q = 'dopamine'
curr = cur.execute("""SELECT * FROM {} WHERE {} MATCH ? """.format(table_name, table_name), (q,))
When I try running the same command by loading the database on the command line, I use
SELECT * FROM umls WHERE umls MATCH 'dopamine';
In both cases I get the error
Error: no such column: umls
I get the same error for any table in the database. What am I doing wrong?
2
You don’t appear to show how the table name is being substituted in? You set q, but there is no reference to the table.
Also has the table been created as an FTS table?
1
Here is your misunderstanding:
WHERE umls MATCH 'dopamine'
MATCH is expecting a column name from you but you are giving it the name of the whole database table. Because you think you can just search through the whole table for a string.
The answer: You can only search ONE COLUMN at a time this way, not the whole table.
This is why it says “no such column” because you are giving it the name of the database table when it asks you for a column. And the database table itself is not a column.
CONCAT(field1, '', field2, '', fieldn)
Using CONCAT you can combine all the columns into one temporary column and search that new column instead. Replace the field1, etc. with the names of the columns.
Also you should use LIKE when searching for strings like this too.
qater is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.