Is mysql.connector reporting incorrect paramstyle? [mysql-connector-python 8.0.29]

Question

Using mysql-connector-python 8.0.29 mysql.connector.paramstyle reports 'pyformat'.

Assuming I've correctly understood the docs here https://peps.python.org/pep-0249/#paramstyle the following ought to be a valid SQL insert when passed to execute() - given the table exists, which it does - and is invoked with the arguments ['Python', 10]:

INSERT INTO lang(name, score) VALUES (%(c1)s, %(c2)s)

However, when this is executed, the following error is returned:

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

This strongly implies that the format %(<name>)s isn't being interpreted as a value placeholder.

However, if I change the insert to the below and invoke as before the insert succeeds.

INSERT INTO lang(name, score) VALUES (%s, %s)

According to the docs however %s is paramstyle 'format' not 'pyformat'.

So is mysql.connector.paramstyle misreporting the supported style? If not, what am I missing?

Background: I'm porting an in-house SQL library from Java to Python. As it's a library it should be neutral to the RDBMS vendor and therefore needs to generate appropriate updates and queries for all the formats PEP 249 allows, based on the reported paramstyle.

# paramstyle.py
import mysql.connector
conf = {
    'user': 'root',
    'password': 'password',
    'host': 'localhost',
    'port': 3306,
    'database': 'test'
}
c = mysql.connector.connect(**conf)
with c.cursor() as csr:
    csr.execute('DROP TABLE IF EXISTS lang')
    csr.execute('CREATE TABLE lang(name VARCHAR(50), score INTEGER)')
    csr.execute('INSERT INTO lang(name, score) VALUES (%(c1)s, %(c2)s)',
        ['Python', 10])
    #csr.execute('INSERT INTO lang(name, score) VALUES (%s, %s)',)
    #    ['Python', 10])
c.commit()
c.close()


$ python3 paramstyle.py
Traceback (most recent call last):
  File "paramstyle.py", line 13, in <module>
    csr.execute('INSERT INTO lang(name, score) VALUES (%(c1)s, %(c2)s)',
  File "/usr/local/lib/python3.8/site-packages/mysql/connector/cursor.py", line 559, in execute
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Aside: Why does PEP 249 allow these five different approaches to argument placeholders? Surely one would have been sufficient? Doesn't this complicate client code that needs to be connector independent?

Answer 1

pyformat doesn't mean you have to use %(name)s style of parameters. It means that these are allowed for named parameters, in addition to printf-style %s for ordered parameters.

But to use these, you have to put the parameters in a dictionary to associate the names. So it should be:

    csr.execute('INSERT INTO lang(name, score) VALUES (%(name)s, %(score)s)',
        {'name': 'Python', 'score': 10])