CODEWITHSUNDEEP
pythonpython-2.7mysql-python
Mike C.
Mike C.

Reputation: 1921

Using a variable as field name in MySQLdb, Python 2.7

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")

Upvotes: 1

Views: 1499

Answers (1)

jsbueno
jsbueno

Reputation: 110486

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.

Upvotes: 5

Related Questions