Using a variable as field name in MySQLdb, Python 2.7

Question

This works when I replace the column variable with an actual column name. I do however need a variable. When I use a variable I get a MySQL syntax error. Can a field be a variable? If so, where is the error?

conn = self.create_connection()
        cur = conn[0]
        db = conn[1]

        cur.execute('''
                        UPDATE coefficients

                        SET %s = %s 
                        WHERE coef_id = %s

                    ''' , (sql_col_name, fgi, ici))  
        db.commit()

Ok here's the traceback:

           raise errorclass, errorvalue
_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 ''base_rpm' = 3500 WHERE coef_id = 460' at line 1")

I haven't used MySQLdb, but I have used psycopg2 with a Redshift database and to use variables in the sql statement there you have to define a sql string and pass the sql string to the execute command. Ex: sql = "Update coefficients SET %s = %s WHERE coef_id = %s" cur.execute(sql) If it wasn't defined this way the literal %s was passed to the query. — Bob
– Bob, Commented Aug 30, 2016 at 17:55
I won't post as an answer as I feel uncomfortable doing it myself in SQLite (I feel this is hacky and perhaps I'll gain too if someone tells me I'm wrong...) but you can form the query separately e.g. query = "UPDATE something SET {} = %s".format('hello') and then print query % 'goodbye'. In which case, the use of format doesn't affect %s in your pre-formed string, so you create it in two parts. — roganjosh
– roganjosh, Commented Aug 30, 2016 at 17:55
I can't edit my previous comment, but of course to get the variables in the string you will want something like this in your sql statement. sql = "Update coefficients SET {1} = {2} WHERE coef_id = {3}".format(sql_col_name, fgi, ici) Or whatever formatting practices you prefer, just as long as you add them into the string. — Bob
– Bob, Commented Aug 30, 2016 at 18:06
@BobDunakey but is this standard practice? I always form the string before execution in SQLite but this was only due to severe time pressure and it shut up the error that you can't assign things left of = on execution. I've never been settled with it, surely there is a standard feature for this? — roganjosh
– roganjosh, Commented Aug 30, 2016 at 18:08
@BobDunakey and actually your answer is probably going further into deeper water since you're using format to assign values (on RHS of =) and bypassing the placeholders that reduce the chance of SQL injection? — roganjosh
– roganjosh, Commented Aug 30, 2016 at 18:13

jsbueno · Accepted Answer · 2016-08-30 20:13:11Z

5

The issue there is that Parameter substitution in the execute method is intended to be used for data only - as you found out. That is not quite explicit in the documentation, but it is how most database drivers implement it.

It is important to note the intent of the parameter substitution in the execute and executemany methods is to format Python objects as strings to the database, and apply escaping and quotes, so that SQL injection becomes difficult (if not impossible) without one having to worry about several places where to put the escaping.

What is needed when you need to use variable column names (or vary other parts of the SQL statement) is to format the string using Python's string formatting methods - and leave the resulting string in a suitable way to be used as a suitable parameter for the data substitution, in a second substitution (so that type casting and quoting still is performed by the driver).

To avoid having to escape the %s entries for parameters itself, you could use a different string interpolation method than the % operator, for example, the newer format string method:

  cur.execute('''
                    UPDATE coefficients
                    SET {} = %s 
                    WHERE coef_id = %s

                '''.format(sql_col_name) ,
                (fgi, ici))

This example shows the simplest way that would make your example work - just write it in code so that it is easily readable and maintanable - for example, using an extra variable for the statement, and calling format prior to the line calling execute. But that is just style.

answered Aug 30, 2016 at 20:13

jsbueno

114k11 gold badges159 silver badges239 bronze badges

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

2 Comments

roganjosh Over a year ago

Upvote, thanks for confirming my approach in the comments. That has been a source of discomfort for quite a while but, as you say, it's not explicit in documentation :)

Mike C. Over a year ago

Perfect! Thanks everyone so much for your help.

Collectives™ on Stack Overflow

Using a variable as field name in MySQLdb, Python 2.7

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related