CODEWITHSUNDEEP
pythonmysqlsqlpyodbc
randomdev2
randomdev2

Reputation: 345

Does pyodbc support any form of named parameters?

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.

Upvotes: 23

Views: 39372

Answers (4)

Alperino
Alperino

Reputation: 558

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']

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123594

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},
    )

Upvotes: 1

blhsing
blhsing

Reputation: 106553

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

Upvotes: 1

FlipperPA
FlipperPA

Reputation: 14311

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!

Upvotes: 19

Related Questions