This page should summarize topics for the DB API 3.0. It is meant to provide

A related page is ExtendingTheDbApi, which lists features that aren't general enough to make it into the spec.

Discussions should be held at db-sig@python.org, with summaries (even preliminary ones) entered here. This is also the place where limits imposed by the underlying database/library implementation should be entered for further reference. It could also be used as a place for technical voting by the driver authors (with tags like 'personal preference', 'trivial to implement', 'difficult to implement', 'implementable only with loss of performance' etc.)

Prior Art

Unified Parameter Style

Possibilities

Pros

Cons

Possible Compromises

Ease of Implementation

Driver authors should list with each style how easy they could be implemented. 'Native' means that the SQL can be passed directly to the database, 'tokenize' means that the driver would have to tokenize the SQL. 'tokenize, already implemented' if the current driver tokenizes the SQL already in the current implementation.

Because it's mostly a feature of the database where some style is native, this should list the database name. If it a driver specic decision, then the driver name should be listed.

qmark: WHERE a = ?

native
mxODBC, JDBC, SAP DB, SQLite
tokenize

numeric: WHERE a = :1

Is this useful at all? If Oracle uses them simply as an indicator of 'pass by position' without regard to the actual numbers, then this style is confusing at best.

native
SAP DB
tokenize
mxODBC, JDBC

named: WHERE a = :value

native
Oracle, SAP DB (positional args, support for mappings could be implemented in the kernel), SQLite
tokenize
mxODBC, JDBC Remark: On M$ SQL Server they use @value, so there should be a way to know which character is used as prefix to value ( '@' instead of ':' ).

format: WHERE a = %s

native
tokenize
mxODBC, JDBC, SAP DB

pyformat: WHERE a = %(value)s

native
psycopg, psycopg2
tokenize
mxODBC, JDBC, SAP DB

Preliminary Consensus

Additional Driver Objects

Prepared statements in addition to Connection and Cursor. Related is the idea of statement caching:

Parameter Passing

cursor.execute ("SELECT where size = :size", {'size': localvar}) vs.cursor.execute ("SELECT where size = :size", size = localvar)

cursor.execute ("SELECT where size = ?", [localvar]) vs. cursor.execute ("SELECT where size = ?", localvar)

Refined transaction model

Currently, DB-API 2.0 loosely defines transactions at the level of a connection. However, many drivers provide extensions that allow multiple (distinct, nested, or both) transactions to exist on a given connection. Maybe it is time for a next generation DB-API to better formalize the transactional scope of a connection. Backends and drivers that wish to allow multiple concurrent transactions will have to implement a simple connection pool so that the same physical connection can be shared by multiple logical DB-API connection objects. Of course, this alters the concept of a connection and may require even more infrastructure to support. i.e., a developer could request an unshared connection so that global (physical) connection variables can be changed in isolation.

Also, some thought should be directed towards properly handling the semantics of nested transactions, as more and more backends are now supporting them.

Schema Information

Role of the DB API spec

Common Modules


CategoryDatabase

DbApi3 (last edited 2010-11-05 09:30:29 by port-87-234-198-221)

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