How do you bind parameters in the Python pymysql library?-CodePudding

As described here https://peps.python.org/pep-0249/#paramstyle, in MySql it should be possible to bind parameters using the keyword syntax, like this: email=:email. This is different from using an unnamed placehoder syntax, like email=%s.

But this code doesn't work:

import pymysql 
con = pymysql.connect(host='localhost', user='root', password=pw, database=db_name, port=4306)

stmt = "INSERT INTO `test_table` (`email`, `password`) VALUES (:email, :password)"
with con.cursor() as cursor:
    # Create a new record
    cursor.execute(stmt, {'email': "FOO", 'password': "BAR"})
con.commit()

Not even adding

pymysql.paramstyle = 'named'

at the top.

The error is

(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 ':email, password=:password)' at line 1")

but unfortunately, I'm not able to find such documentation (This page doesn't document anything.. https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html)

What is the right syntax?

Thanks!

CodePudding user response：

https://pymysql.readthedocs.io/en/latest/modules/cursors.html says:

If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.

Even though the :name placeholder format is in the PEP you reference, the pymysql package does not seem to implement that format.

This should work:

stmt = "INSERT INTO `test_table` (`email`, `password`) VALUES (%(email)s, %(password)s)"
with con.cursor() as cursor:
    # Create a new record
    cursor.execute(stmt, {'email': "FOO", 'password': "BAR"})