MySQLdb: cursor.query( 'select * from %s;', ('thistable',) )
should end up as: 'select * from thistable'
actually ends up as: "select * from 'thistable' "
the db natually throws syntax error: ...near ' 'thistable' ' at ...
It behaves as though the data converter is including the string's quotes as part of the string, ie the string is " 'thistable' " instead of 'thistable'. Any and all help with this is deeply appreciated. One thing I did notice in my questing is that the script's charset is utf8, while the db server and db are latin1. Could this be my problem?
OS: os X Sierra
python: 3.6
MySQL: 5.3.6
mysql-connector-c: 6.1.11
There's a difference between building dynamic SQL and building parameterized queries.
In general, parameterized queries let you plug in values for comparison/input, but not database objects. So, your application code assumes that %s
is a quoted literal, not an database object.
If you need to dynamically add in database objects (tables, columns, procedure names, etc.), you will probably need to build the query string first, with placeholders (like %s
) for actual parameters you need to pass in.
In this case, ypercubeᵀᴹ's suggestion to simply use the string 'select * from thistable'
directly. If you needed to run the same query against multiple tables, loop through the list of table, building the string like:
queryString = 'SELECT * FROM ' + currTable