Getting an error when using prepared statement in python

Question

whenever I try to run a normal query all works perfectly fine. the code executes and I can get the results but whenever I try to use a prepared statement in python I keep getting the following error:

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? WHERE name = ?' at line 1

The code I'm trying to run:

cursor = con.db.cursor(prepared=True)
try:
   cursor.execute("SELECT * FROM %s WHERE name = %s", ('operations', 'check', ))
except mysql.connector.Error as error:
   print(error)
except TypeError as e:
   print(e)

I've tried also to change the tuple object to string and removed one of the '%s' just for checking. but I still get an error for the '%s' synax.

another thing I've tried is to use a dict object so I've changed the '%s' to '%(table)s' and '%(name)s' and used a dict of

{'table': 'operations', 'name': 'check'}

example:

   cursor.execute("SELECT * FROM %(table)s WHERE name = %(name)s", {'table': 'operations', 'name': 'check'})

but again it didn't worked and I still got the exception

am I missing something?

Thanks in advance!

-------- Edit --------

Thanks to @khelwood, I've fixed the problem. as @khelwood mentioned in comments the problem was because I tried to use the '%s' as a parameter for table name. python prepared statements can't handle parameters for things such as table names so thats what throwed the exception

You can't insert a table name as a query parameter. You can pass the name you're looking for as a parameter, but it should be in a tuple: ("check",) — khelwood
– khelwood, Commented Dec 30, 2020 at 11:08
@JailtonSilva The , is correct when you're passing query parameters to cursor.execute. — khelwood
– khelwood, Commented Dec 30, 2020 at 11:13
@khelwood You were right, the problem was because I tried to insert the table name as a parameter. Thanks! — Tyler Perry
– Tyler Perry, Commented Dec 30, 2020 at 11:35

khelwood · Accepted Answer · 2020-12-30 12:50:42Z

1

You can't insert a table name as a query parameter. You can pass the name you're looking for as a parameter, but it should be in a tuple: ("check",)

So

cursor.execute("SELECT * FROM operations WHERE name = %s", ("check", ))

answered Dec 30, 2020 at 12:50

khelwood

59.7k14 gold badges91 silver badges116 bronze badges

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

Collectives™ on Stack Overflow

Getting an error when using prepared statement in python

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