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
– John Ruddell, Commented Aug 26, 2014 at 15:48

Community · Accepted Answer · 2017-05-23 10:25:20Z

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, 2017 at 10:25

CommunityBot

11 silver badge

answered Aug 26, 2014 at 15:51

johntellsall

15.2k4 gold badges48 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2882289 Over a year ago

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.

Sylvain Leroux · Accepted Answer · 2014-08-26 15:52:23Z

0

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.

edited Aug 26, 2014 at 15:52

answered Aug 26, 2014 at 15:45

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Collectives™ on Stack Overflow

Python: Iterating through MySQL columns

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related