Home Original page

DbApiFaq

This wiki is in the process of being archived due to lack of usage and the resources necessary to serve it — predominately to bots, crawlers, and LLM companies. Edits are discouraged.
Pages are preserved as they were at the time of archival. For current information, please visit python.org.
If a change to this archive is absolutely needed, requests can be made via the infrastructure@python.org mailing list.

These are the frequently asked questions from the DB-SIG mailing list.

Contents

  1. How do I pass parameters to the cursor.execute method?

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