Does pyodbc support any form of named parameters?

Question

I know sqlite3 has

data = {'test_col': 012345679}
sqlite3_conn.execute("""
    UPDATE test_db SET test_col = :test_col
    ;""", data)

and mysql-connector-python has

data = {'test_col': 012345679}
mysql_conn.execute("""
    UPDATE test_db SET test_col = %(test_col)s
    ;""", data)

but does pyodbc support any form of named parameters? I like being able to just pass a dict to the execute method. It is very convenient, and with some of my queries, such as INSERT INTO ... ON DUPLICATE KEY UPDATE, it is needed.

Metrics

The short answer is "No" (ref: here). In some cases a workaround is possible, e.g., with EXEC for stored procedures in T-SQL, as explained in the answer here. – Tokyo

Answer 1

It doesn't support named parameters, but bound parameters passed in the correct order are fairly straightforward:

x = "This"
y = 345

mssql_cur.execute("SELECT * FROM mytable WHERE colx = ? AND coly = ?", x, y)

or

mssql_cur.execute("SELECT * FROM mytable WHERE colx = ? AND coly = ?", (x, y))

More details and options here, such as passing executemany parameters:

https://github.com/mkleehammer/pyodbc/wiki/Cursor

Good luck!

Gingerich

Those are qmark style placeholders. I am talking about named style placeholders, or named parameters: sqlite3 --> :colname; mysql-connector --> %(colname)s – Metrics

I haven't been in the code in a while, but last I was, they were not supported. You can pass as x, y or (x, y). With executemany, you can pass as ((x, y), (a, b)). You can make a feature request here, it would be nice to have: github.com/mkleehammer/pyodbc/issues – Gingerich

Answer 2

You can create a helper function to convert an execute method that only accepts positional parameters into a wrapper function that accepts named parameters. Use a regex pattern that grabs the name after a colon but ignores quoted strings and comments:

import re

def to_positional(func, _pattern=re.compile(r"'(?:''|[^'])*'|--.*|:(\w+)")):
    def execute(operation, namespace):
        def replacer(match):
            if name := match[1]:
                params.append(namespace[name])
                return '?'
            return match[0]
        params = []
        return func(_pattern.sub(replacer, operation), params)
    return execute

so that you can call the wrapped execute method with a query with named parameters:

sql = """\
UPDATE products
SET
    name=:name,
    price=:price,
    description=':description ''' || :description || ''''
WHERE id=:id; -- :comment
"""
data = {'id': 123, 'name': 'foo', 'price': 99.99, 'description': 'foo description'}
to_positional(mssql_cur.execute)(sql, data)

To more easily see the transformed query without involving a database you can make the helper function wrap print instead of mssql_cur.execute:

to_positional(print)(sql, data)

so it would output:

UPDATE products
SET
    name=?,
    price=?,
    description=':description ''' || ? || ''''
WHERE id=?; -- :comment
 ['foo', 99.99, 'foo description', 123]

Demo: https://ideone.com/tXlAfP

Sensory

Answer 3

pyodbc itself does not support named parameters, but SQLAlchemy does, thereby making named parameters available to any database that has a SQLAlchemy dialect.

import sqlalchemy as sa

engine = sa.create_engine("mssql+pyodbc://scott:tiger^5HHH@my_mssql_dsn")

with engine.begin() as conn:
    conn.execute(
        sa.text("UPDATE test_db SET test_col = :param_a"),
        {"param_a": 123},
    )

Tokyo

Answer 4

The solution suggested by blhsing is great, I would only add some improvements:

1. you might also want to ignore mulit-line comments between /* and */
2. using :(\w*) instead of :(\w+) in the pattern allows you to catch ill-formatted queries with isolated colons such as SELECT id =: id FROM table (note the whitespace between : id).
3. The three-fold nested function to_positional is quite complex and hard to understand. Maybe just provide a function that accepts a query in named parameter style and a dict of parameters and returns an equivalent query in ?-style along with a tuple of parameter values.

For instance, adjust the to_positional function as follows:

import re

def to_positional(query:str, params:dict)->tuple[str, tuple]:
    """Parse (query, params)-pair in named parameter style into equivalent
   (query, params) pair in qmark-style.
   """
    pattern = re.compile(r"'(?:''|[^'])*'|--.*|/\*[\s\S]*?\*/|:(\w*)")
    p = []
    def replacer(match):
        name = match[1]
        if name is None:
            return match[0]
        elif len(name)==0:
            raise SyntaxError("Cannot parse isolated ':'.")
        p.append(params[name])
        return '?'
    query = pattern.sub(replacer, query)
    return query, p

Then use it to parse any (query, param:dict) pair in named parameter style to (query, param:tuple) in ?-style like so:

query = """
   SELECT
      -- asdf = : asdf, -- Uncomment this line to provoke a SyntaxError
      id = :id,
      name = :name,
      price = :price, -- :comment
      description = ':description ''' || :description || ''''
   /* WHERE
      id=:id
      AND description= :description
   */
   ;"""
params = {'id': 123, 'name': 'foo', 'price': 99.99, 'description': 'foo description'}
query, params = to_positional(query, params)
print(query, params)

returns

SELECT
    -- asdf = : asdf, -- Uncomment this line to provoke a SyntaxError
    id = ?,
    name=?,
    price=?, -- :comment
    description=':description ''' || ? || ''''
/* WHERE
    id=:id
    AND description= :description
*/
;
[123, 'foo', 99.99, 'foo description']

Eurythmic