String Interpolation Causing MysqlDB Errors

Question

I'm trying to use mysqlDB, but I've run into a problem that doesn't make sense to me at all.

My problem is centered around the table name in the following execute statements.

This works:

c.execute(u'''insert into AIN0 (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
                        (str(date), int(raw), int(formula), 0.1))

Neither of these work:

c.execute(u'''insert into %s (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
                        (str("AIN0"), str(date), int(raw), int(formula), 0.1))
c.execute(u'''insert into 'AIN0' (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values (%s, %s, %s, %s)''',
                        (str(date), int(raw), int(formula), 0.1))

The thing is, both of the non-working ones result in the same error message:

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 ''AIN0' (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values ('2011' at line 1")

Notice that in the error message, AIN0 is surrounded by single quotes, even in the case where I inserted it using the string interpolation operator. Just to be sure, I made an intentional syntax error in the working case in order to see what MySQL was looking at.

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')) AIN0 (Date_Info, Raw_Data, Calculation_Formula, Calculated_Data) values ('201' at line 1")

Sure enough, in the one that MySQL works correctly with, AIN0 is not surrounded by single quotes leading me to believe that that is what is causing the error.

Why are single quotes being inserted around my strings when I use string interpolation and how do I get them to stop?

@S.Lott: Perhaps because he doesn't understand the difference between string interpolation and SQL parameter insertion (which isn't helped by (in this case) SQL parameter insertion using %s as a place holder). — John Machin
– John Machin, Commented Mar 26, 2011 at 16:33

John Machin · Accepted Answer · 2011-03-26 16:44:43Z

If by "string interpolation" you mean like Python's string formatting, that's NOT what you are using. cursor.execute() does SQL parameter insertion, which has its own set of rules. The confusion happens because some database interfaces (including mysqlDB) use %s as a placeholder ... other database interfaces use e.g. ?

When you use parameterised SQL, the place holders can appear only where an expression can appear in SQL. A table name is not an expression. When your argument is a string, it is wrapped in single quotes so that in the resultant SQL it looks like an SQL string literal. This involves doubling single quotes e.g. what in Python would appear as a string literal like "O'Brien" becomes 'O''Brien' in SQL.

So, you can't stop it from wrapping single quotes arround it. You need to do two steps: (1) build an SQL string that inserts AIN0 where you want it, using Python string formatting. (2) Use that SQL string in your cursor.execute()

Collectives™ on Stack Overflow

String Interpolation Causing MysqlDB Errors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related