Differences between revisions 8 and 9
Revision 8 as of 2008-11-15 14:00:51
Size: 1257
Editor: localhost
Comment: converted to 1.6 markup
Revision 9 as of 2010-03-09 04:12:39
Size: 1977
Editor: bb14f857
Comment: 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)


CategoryDatabase

DbApiFaq (last edited 2010-03-09 04:12:39 by bb14f857)

Unable to edit the page? See the FrontPage for instructions.