Insert sql with param variables using %(name)s parameter style is failing

vi3k6i5 commented 3 years ago

Insert sql with parameter style %(name)s is failing.

Environment details

OS type and version: Mac os, latest
Python version: python --version 3.8
pip version: pip --version
google-cloud-spanner 3.8.0

Steps to reproduce

Run the sql query shown below.

Code example

sql = 'INSERT INTO backends_square (root, square) VALUES (%(root)s, %(square)s)'
params = {'root': 2, 'square': 4}
cursor.execute(sql, params)

Stack trace

  File "/Users/vikash/Documents/python-spanner/google/cloud/spanner_dbapi/parser.py", line 169, in expect
    def expect(word, token):
django.db.utils.ProgrammingError: FUNC: `%(root)s, %(square)s)` does not begin with `a-zA-z` nor a `_`

larkee commented 3 years ago

As per PEP 249, there are five ways to mark parameters in an SQL query. My understanding is that a DB-API driver needs to support at least one, which for Spanner is format. So this isn't technically a bug.

However, I do think that we should try add support for the three suggested options: numeric, named, and pyformat.

IlyaFaer commented 3 years ago

Seems pyformat was going to be supported, but something went wrong (see the method). Trying to make it work correctly...

vi3k6i5 commented 3 years ago

Linking for more context: https://github.com/googleapis/python-spanner-django/issues/343

googleapis / python-spanner