3

I am using PyMySQL to execute SQL query commands from python. My pystyle is pyformat which was found using:

>>> pymysql.paramstyle
pyformat

My db and cursor details are as follows:

>>> MYDB = pymysql.connect(_params_)
>>> cursor = MYDB.cursor()

I then execute a SQL query using,

>>> cursor.execute("SELECT * FROM %(tablename)s",  {"tablename": "activity"})

I get an error stating,

ProgrammingError: (1064, u"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 '''activity''' at line 1")

On the other hand, the query by itself works,

>>> unsafe_sql = ("Select * from activity")
>>> cursor.execute(unsafe_sql)
>>> 4

I am not sure what is going on with my first query. Any help appreciated.

lordlabakdas
  • 1,163
  • 5
  • 18
  • 33
  • You should have backticks around `%(tablename)s`, not single quotes. – Barmar Jul 26 '18 at 01:16
  • sorry, was trying something there......even without single quotes it does not work....will update the query – lordlabakdas Jul 26 '18 at 01:20
  • Are you still getting the same error? because the error message shows the quotes. – Barmar Jul 26 '18 at 01:22
  • I think the problem may be that `cursor.execute()` automatically wraps any substituted values with quotes. You'll need to do normal string formatting for the table name. Only use `cursor.execute()` substitution for values. – Barmar Jul 26 '18 at 01:24

1 Answers1

5

You can't pass a table name as a parameter to cursor.execute(). Whenever a parameter is a string it quotes it when it substitutes into the query. Use a normal string formatting method, e.g.

cursor.execute("SELECT * FROM %(tablename)s" % {"tablename": "activity"})
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Ugh, these are the kind of things that drive me nuts when learning. If only the error message could be just a bit more specific :/ Googling this error was also tough (at least for me). I ran into the same issue with `LOAD DATA` and the `INTO TABLE ` part where at least to me it seems a bit more natural to make the table a variable .... Thanks! – Jimbo Mar 05 '21 at 00:06