pass list of elements inside cursor.execute mysql query

Question

I have a list of multiple elements as :

l=['asia','america','africa','australia'].

I have mysql table and all I want to count a occurance of word within all rows of column so for example if asia is a word of which I want to check count then I used:

cursor.execute("select count(*)from table_text where round(match(text) against('asia'),5)") cursor.fetchall()

and I got count as 48. How I can pass all list elements as parameter inside cursor.execute and get all words count simultaneously? I did something as:-

for i in l:
    cursor.execute("select count(*)from table_text where round(match(text) against('"i"'),5)")
    cursor.fetchall()

That gives an error!!!

score 1 · Accepted Answer · answered Aug 24 '18 at 12:50

1

Just use + operator for string. So, you can do something like:-

list_new=[]
for words in l:
    cursor.execute("select count(*)from table_text where round(match(text) against('"+words+"'),5)")
    tagg=cursor.fetchall()
    for i in tagg[0]:
        list_new.append(i)
print (list_new)     #Gives you list of counts of all elements

Hope it helps!!!!

answered Aug 24 '18 at 12:50

James

528
1
6
18

I guess you've never heard of [sql-injection](https://stackoverflow.com/questions/601300/what-is-sql-injection). Consider what would happen if someone passed in `words = "blah'),5); drop table table_text; --"` – pault Aug 24 '18 at 17:42

Tony Pellerin · Answer 2 · 2018-08-24T09:17:13.190

-1

I'm not sure of what you're trying to do with the query. I see a few mistakes:

select count(*)from table_text where round(match(text) against('"i"'),5)

Firstly add a space between count and from
Secondly the condition 'match(text) against('"i"')' makes sense but what is the purpose of the round function?
Finally, with an aggregate function such as count, you need a groupby at the end.

I think you can use spaces in your condition to match one of the words. Something like:

conditions = '"' + ' '.join(l) + '"'
# conditions = '"asia america africa australia"'

query = 'SELECT count(*) FROM table_text WHERE MATCH(text) AGAINST(' + conditions + ' IN BOOLEAN MODE)'

Also be careful with quotes and double quotes. The elements of l have simple quotes whereas your query uses double quotes.

edited Aug 24 '18 at 09:17

answered Aug 24 '18 at 08:21

Tony Pellerin

231
1
7

what is use of `' " '` in `conditions` variable? – Learner Aug 24 '18 at 08:38
GROUP BY is not required. If using aggregates without one, then it is as if `GROUP BY ()` was added. There's no need to be careful with quoting, if passing values is done *right*, i.e. using placeholders. String concatenating values to a query is both error prone, as you hint, and dangerous. Documentation on fulltext search also doesn't seem to mention `|` as a valid operator (or I looked in the wrong place). Just white space separated words implies "or". – Ilja Everilä Aug 24 '18 at 08:41
Because you want '... AGAINST("asia | america | africa | australia") ... ' as you use simple quotes for the query, and AGAINST requires a string so the only possibility is "asia | america | africa | australia", not asia ... australia without quotes or 'asia... australia' as it would conflit with the rest of the query. – Tony Pellerin Aug 24 '18 at 08:47
...Also any special operators work only in [boolean mode](https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html). – Ilja Everilä Aug 24 '18 at 08:51
@IljaEverilä Thank you for your corrections, I edited my answer accordingly. However, I think we still need a GROUP BY, otherwise the count would be done with every text containing one of the elements, and I think the author wants the count to be done by element... Am I right ? – Tony Pellerin Aug 24 '18 at 09:24
I'd say you are, but there's a problem: what would you group by? I suppose the text being matched against is fairly unique per row, so grouping by it would produce a lot of counts of 1. This'd be easier if MySQL allowed any old expression in `AGAINST`, but alas it does not – only constant string expressions are allowed, so you cannot for example join against a derived table of words to look for and then group by word. I wonder if there's some function or operator that allows peeking what exactly matched. That'd be a good candidate for grouping. – Ilja Everilä Aug 24 '18 at 09:31
How about the ['regexp_substr'](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-substr) function? The query would become: 'SELECT count(*), REGEXP_SUBSTR(text, conditions) as country FROM table_text GROUP BY country' – Tony Pellerin Aug 24 '18 at 11:31

pass list of elements inside cursor.execute mysql query

2 Answers2