1257
Comment: converted to 1.6 markup
|
← Revision 9 as of 2010-03-09 04:12:39 ⇥
1977
Added information about different parameter styles and how to inspect/use each
|
Deletions are marked like this. | Additions are marked like this. |
Line 16: | Line 16: |
/!\ Drivers differ in the way the parameters are passed to .execute () | /!\ Drivers differ in the way the parameters are passed to .execute(); Some examples of parameter passing: |
Line 18: | Line 21: |
* a tuple: {{{.execute ("... col = ?", ("value"))}}} | |
Line 22: | Line 26: |
/!\ The substitution value in the statement differs as well. See the paramstyle section (under Module Interface) in the [[http://www.python.org/peps/pep-0249.html|DB-API 2.0 specification]] | /!\ Drivers also differ in the substitution sequence used to denote a parameter. The substitution style can be inspected by reading the {{{paramstyle}}} atribute of the module being used: {{{ >>> print module_name.paramstyle 'qmark' }}} Some examples of usage for each {{{paramstyle}}}: * format: {{{.execute("... WHERE my_column = %s", (value,)) }}} * pyformat: {{{.execute("... WHERE my_column = %(name)s", {"name": value}) }}} * qmark: {{{.execute("... WHERE my_column = ?", (value,)) }}} * numeric: {{{.execute("... WHERE my_column = :1", (value,)) }}} * named: {{{.execute("... WHERE my_column = :name", {"name": value}) }}} See the {{{paramstyle}}} section (under Module Interface) in the [[http://www.python.org/peps/pep-0249.html|DB-API 2.0 specification]] for more information. |
These are the frequently asked questions from the DB-SIG mailing list.
How do I pass parameters to the cursor.execute method?
Don't use the '%' concatenation operator, pass them as a series of extra parameters. For instance
>>> cursor.execute("SELECT * FROM my_table WHERE my_column = '%s'" % "column_value")
May do what you want, but more by accident than design. If you change it to;
>>> cursor.execute("SELECT * FROM my_table WHERE my_column = %s", "column_value")
Then the DB-API module will make sure your value is correctly escaped and turned into an object appropriate for the database.
Drivers differ in the way the parameters are passed to .execute();
Some examples of parameter passing:
a list: .execute ("... col = ?", ["value"])
a tuple: .execute ("... col = ?", ("value"))
variable arguments: .execute ("... col = ?", "value")
a dictionary: .execute ("... col = :arg", {'arg': "value"})
keyword args: .execute ("... col = :arg", arg = "value")
Drivers also differ in the substitution sequence used to denote a parameter. The substitution style can be inspected by reading the paramstyle atribute of the module being used:
>>> print module_name.paramstyle 'qmark'
Some examples of usage for each paramstyle:
format: .execute("... WHERE my_column = %s", (value,))
pyformat: .execute("... WHERE my_column = %(name)s", {"name": value})
qmark: .execute("... WHERE my_column = ?", (value,))
numeric: .execute("... WHERE my_column = :1", (value,))
named: .execute("... WHERE my_column = :name", {"name": value})
See the paramstyle section (under Module Interface) in the DB-API 2.0 specification for more information.
See also: DbApiCheatSheet (under construction)