Python: Iterating through MySQL columns

Question

I'm wondering if you can help me. I'm trying to change the value in each column if the text matches a corresponding keyword. This is the loop:

for i in range(0, 20, 1):
    cur.execute("UPDATE table SET %s = 1 WHERE text rlike %s") %(column_names[i], search_terms[i])

The MySQL command works fine on its own, but not when I put it in the loop. It's giving an error at the first %s

Does anyone have any insights?

This is the error:

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s = 1 WHERE text rlike %s' at line 1")

Column names looks like

column_names = ["col1","col2","col3"...]

Search terms look like

search_terms = ["'(^| |.|-)word1[;:,. ?-]'","'(^| |.|-)word2[;:,. ?-]'",...]

why do you have range(0, 20, 1)? thats the same as just range(20) or even range(0, 20) — John Ruddell, Aug 26 '14 at 15:48

Sylvain Leroux · Answer 1 · 2014-08-26T15:52:23.160

Missing quotes and wrong parenthesis placement...

for i in range(0, 20, 1):
    cur.execute("UPDATE table SET %s = 1 WHERE text rlike '%s'" %(column_names[i], search_terms[i]))
#                                                         ^  ^
#              (-----------------------------------------------------------------------------------)

Please note, this is not the right way of doing this, if your string may contain quotes by itself...

What about that instead:

for i in range(0, 20, 1):
    cur.execute("UPDATE table SET %s = 1 WHERE text rlike ?" % (column_names[i],),
                (search_terms[i],))

This uses the % operator to set the column name, but uses an executes parameter to bind the data, letting the DB driver escape all characters that need so.

score 0 · Accepted Answer · edited May 23 '17 at 10:25

0

The right way to do this is to give values to Python, which will quote things correctly.

adapted from voyager's post:

for i in range(0, 20, 1):
    cur.execute("UPDATE table SET {} = 1 WHERE text rlike %s".format(column_names[i]),
                (search_terms[i],),
               )

In this case it's confusing because the column_name isn't a value, it's part of the table structure, so it's inserted using good old string formatting. The search_term is a value, so is passed to cursor.execute() for correct, safe quoting.

(Don't use string manipulation to add the quotes -- you're exposing yourself to SQL injection.)

edited May 23 '17 at 10:25

Community

1
1

answered Aug 26 '14 at 15:51

johntellsall

14,394
4
46
40

Thank you! This works for me. I dont fully understand the syntax you've used, but I'll spend the time now, while this does its text searches to try and see what you've done there. – Aug 26 '14 at 16:02

Python: Iterating through MySQL columns

2 Answers2