20

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.

randomdev2
  • 315
  • 1
  • 2
  • 7
  • 1
    The short answer is "No" (ref: [here](https://groups.google.com/forum/#!topic/pyodbc/zuXrEgk_14A)). In some cases a workaround is possible, e.g., with EXEC for stored procedures in T-SQL, as explained in the answer [here](http://stackoverflow.com/a/34179375/2144390). – Gord Thompson Dec 09 '15 at 13:14

1 Answers1

17

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!

dbinott
  • 911
  • 1
  • 11
  • 36
FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • Those are qmark style placeholders. I am talking about named style placeholders, or named parameters: [sqlite3](https://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.execute) --> `:colname`; [mysql-connector](http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html) --> `%(colname)s` – randomdev2 Sep 23 '15 at 21:45
  • 1
    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: https://github.com/mkleehammer/pyodbc/issues – FlipperPA Sep 24 '15 at 00:37