Python DB-API 2.0仕様
36407 ワード
Python DB-API 2.0仕様
PEP:
249
タイトル:
Python Database API Specification v2.0
バージョン:
83893e13db91
変更日:
2008-03-03 12:37:19 +0000 (Mon, 03 Mar 2008)
作成者:
Marc-André Lemburg
翻訳:
使用人7001
ディスカッションメール:
ステータス:
最終的に
カテゴリ:
情報
作成:
履歴:
代替:
248
概要:
モジュールインタフェース(Module Interface):
接続オブジェクト(Connection Object):
Cursor Objects:
データ型オブジェクトおよび構造(Type Objects and Constructors):
モジュール作成者への実装方法の概略(Implementation Hints for Module Authors):
* The preferred object type for Binary objects are the buffer types available in standard Python starting with version 1.5.2. Please see the Python documentation for details. For information about the C interface have a look at Include/bufferobject.h and Objects/bufferobject.c in the Python source distribution. * This Python class allows implementing the above type objects even though the description type code field yields multiple values for on type object: class DBAPITypeObject: def __init__(self,*values): self.values = values def __cmp__(self,other): if other in self.values: return 0 if other < self.values: return 1 else: return -1 The resulting type object compares equal to all values passed to the constructor. * Here is a snippet of Python code that implements the exception hierarchy defined above: import exceptions class Error(exceptions.StandardError): pass class Warning(exceptions.StandardError): pass class InterfaceError(Error): pass class DatabaseError(Error): pass class InternalError(DatabaseError): pass class OperationalError(DatabaseError): pass class ProgrammingError(DatabaseError): pass class IntegrityError(DatabaseError): pass class DataError(DatabaseError): pass class NotSupportedError(DatabaseError): pass In C you can use the PyErr_NewException(fullname, base, NULL) API to create the exception objects.
オプションのDB API拡張(Optional DB API Extensions):
PEP:
249
タイトル:
Python Database API Specification v2.0
バージョン:
83893e13db91
変更日:
2008-03-03 12:37:19 +0000 (Mon, 03 Mar 2008)
作成者:
Marc-André Lemburg
翻訳:
使用人7001
ディスカッションメール:
ステータス:
最終的に
カテゴリ:
情報
作成:
履歴:
代替:
248
：PEP Python Enhancement Proposals ， Python 概要:
API Python 。 ， ， ，
， Python 。
：
*
*
*
* Type Objects and Constructors
*
* DB API
*
* （Two-Phase Commit）
*
* 1.0 2.0
* （Open Issues）
* （Footnotes）
* （Acknowledgments）
the SIG for Database Interfacing with Python
([email protected])。
Python http://www.python.org/topics/database/.
Python DB-API2.0 。 1.0
PEP 248 。 ， 。
モジュールインタフェース(Module Interface):
（connection objects） 。
：
connect(parameters...)
， Connection 。
， 。[1]
：
apilevel
， DB API 。
'1.0' '2.0'。
， DB-API 1.0。
threadsafety
， ， ：
0 ， 。
1 ， （connections）。
2 。
3 ， 、 （module，connections，cursors）。
（mutex semaphore） ， 。
， ，
， 。
paramstyle
SQL 。 [2]：
'qmark' ， ：'...WHERE name=?'
'numeric' ， ：'...WHERE name=:1'
'named' ， ：'...WHERE name=:name'
'format' （ANSI C printf format codes） ：'...WHERE name=%s'
'pyformat' python （Python extended format codes）， ：'...WHERE name=%(name)s'
：
Warning
， 。 Python StandardError （ exceptions ）。
Error
。
‘except’ 。 （Warnings） ，
， Python StandardError 。
InterfaceError
（ ） 。
Error 。
DatabaseError
。
Error 。
DataError
， ： ， 。
DatabaseError 。
OperationalError
， 。 ： 、
、 、 。
DatabaseError 。
IntegrityError
， 。 DatabaseError 。
InternalError
， （cursor） 、 。
DatabaseError 。
ProgrammingError
， （table） 、SQL 、
。 DatabaseError 。
NotSupportedError
， API 。
.rollback() ， 。
DatabaseError 。
：
StandardError
|__Warning
|__Error
|__InterfaceError
|__DatabaseError
|__DataError
|__OperationalError
|__IntegrityError
|__InternalError
|__ProgrammingError
|__NotSupportedError
： （Exceptions） （ ） ， 。
接続オブジェクト(Connection Object):
：
.close()
（ __del__ ）。
，
（Error ）， （cursor）
， 。
， ，
（rollback）， 。
.commit()
。
， （auto-commit），
。 。
， 。
.rollback()
， 。[3]
，
。 commit()
， rollback() 。
.cursor()
（Cursor Object）。
， 。[4]
Cursor Objects:
.execute*() ， 。
，
。
， ，
（ .rollback() .commit() ）。
：
.description
， 7 tulip 。
tulip ：
(name,
type_code,
display_size,
internal_size,
precision,
scale,
null_ok)
， (name and type_code) ， 。
None。
.execute*()
（None）。
type_code Type 。
.rowcount
，
（ .execute ）。 （DQL），
'select' ， （DML ） 'update' 'insert'
。
， 。
-1 [7]
： ， （None） -1。
.callproc(procname[,parameters])
( ， [3])
， ， ，
。 ， （Input parameters）
， 。
， fech
（.fetch*() methods）。
.close()
（ __del__ ）。 。
Error 。
.execute(operation[,parameters])
（ ）。
。 。（
paramstyle ）。[5]
， ，
。 （ ）
， 。
， ， .setinputsizes()
。 ，
， 。
tuples tuple list ， 。
， .executemany() 。
。
.executemany(operation,seq_of_parameters)
（ ），
。
.execute() ，
， 。
， 。
（ ） 。
.execute() 。
。
.fetchone()
， ，
None。[6]
.execute ()
， （Error ）。
.fetchmany([size=cursor.arraysize])
Fetch the next set of rows of a query result, returning a
sequence of sequences (e.g. a list of tuples). An empty
sequence is returned when no more rows are available.
The number of rows to fetch per call is specified by the
parameter. If it is not given, the cursor's arraysize
determines the number of rows to be fetched. The method
should try to fetch as many rows as indicated by the size
parameter. If this is not possible due to the specified
number of rows not being available, fewer rows may be
returned.
An Error (or subclass) exception is raised if the previous
call to .execute*() did not produce any result set or no
call was issued yet.
Note there are performance considerations involved with
the size parameter. For optimal performance, it is
usually best to use the arraysize attribute. If the size
parameter is used, then it is best for it to retain the
same value from one .fetchmany() call to the next.
.fetchall()
Fetch all (remaining) rows of a query result, returning
them as a sequence of sequences (e.g. a list of tuples).
Note that the cursor's arraysize attribute can affect the
performance of this operation.
An Error (or subclass) exception is raised if the previous
call to .execute*() did not produce any result set or no
call was issued yet.
.nextset()
(This method is optional since not all databases support
multiple result sets. [3])
This method will make the cursor skip to the next
available set, discarding any remaining rows from the
current set.
If there are no more sets, the method returns
None. Otherwise, it returns a true value and subsequent
calls to the fetch methods will return rows from the next
result set.
An Error (or subclass) exception is raised if the previous
call to .execute*() did not produce any result set or no
call was issued yet.
.arraysize
This read/write attribute specifies the number of rows to
fetch at a time with .fetchmany(). It defaults to 1
meaning to fetch a single row at a time.
Implementations must observe this value with respect to
the .fetchmany() method, but are free to interact with the
database a single row at a time. It may also be used in
the implementation of .executemany().
.setinputsizes(sizes)
This can be used before a call to .execute*() to
predefine memory areas for the operation's parameters.
sizes is specified as a sequence -- one item for each
input parameter. The item should be a Type Object that
corresponds to the input that will be used, or it should
be an integer specifying the maximum length of a string
parameter. If the item is None, then no predefined memory
area will be reserved for that column (this is useful to
avoid predefined areas for large inputs).
This method would be used before the .execute*() method
is invoked.
Implementations are free to have this method do nothing
and users are free to not use it.
.setoutputsize(size[,column])
Set a column buffer size for fetches of large columns
(e.g. LONGs, BLOBs, etc.). The column is specified as an
index into the result sequence. Not specifying the column
will set the default size for all large columns in the
cursor.
This method would be used before the .execute*() method
is invoked.
Implementations are free to have this method do nothing
and users are free to not use it.
データ型オブジェクトおよび構造(Type Objects and Constructors):
Many databases need to have the input in a particular format for
binding to an operation's input parameters. For example, if an
input is destined for a DATE column, then it must be bound to the
database in a particular string format. Similar problems exist
for "Row ID" columns or large binary items (e.g. blobs or RAW
columns). This presents problems for Python since the parameters
to the .execute*() method are untyped. When the database module
sees a Python string object, it doesn't know if it should be bound
as a simple CHAR column, as a raw BINARY item, or as a DATE.
To overcome this problem, a module must provide the constructors
defined below to create objects that can hold special values.
When passed to the cursor methods, the module can then detect the
proper type of the input parameter and bind it accordingly.
A Cursor Object's description attribute returns information about
each of the result columns of a query. The type_code must compare
equal to one of Type Objects defined below. Type Objects may be
equal to more than one type code (e.g. DATETIME could be equal to
the type codes for date, time and timestamp columns; see the
Implementation Hints below for details).
The module exports the following constructors and singletons:
Date(year,month,day)
This function constructs an object holding a date value.
Time(hour,minute,second)
This function constructs an object holding a time value.
Timestamp(year,month,day,hour,minute,second)
This function constructs an object holding a time stamp
value.
DateFromTicks(ticks)
This function constructs an object holding a date value
from the given ticks value (number of seconds since the
epoch; see the documentation of the standard Python time
module for details).
TimeFromTicks(ticks)
This function constructs an object holding a time value
from the given ticks value (number of seconds since the
epoch; see the documentation of the standard Python time
module for details).
TimestampFromTicks(ticks)
This function constructs an object holding a time stamp
value from the given ticks value (number of seconds since
the epoch; see the documentation of the standard Python
time module for details).
Binary(string)
This function constructs an object capable of holding a
binary (long) string value.
STRING
This type object is used to describe columns in a database
that are string-based (e.g. CHAR).
BINARY
This type object is used to describe (long) binary columns
in a database (e.g. LONG, RAW, BLOBs).
NUMBER
This type object is used to describe numeric columns in a
database.
DATETIME
This type object is used to describe date/time columns in
a database.
ROWID
This type object is used to describe the "Row ID" column
in a database.
SQL NULL values are represented by the Python None singleton on
input and output.
Note: Usage of Unix ticks for database interfacing can cause
troubles because of the limited date range they cover.
モジュール作成者への実装方法の概略(Implementation Hints for Module Authors):
* / （Date/time objects） Python datetime
（Python 2.3 ，2.4 C API），
mxDateTime （ Python1.5.2 ）。
， （ Python C ）。
* Unix ticks date/time ：
import time
def DateFromTicks(ticks):
return Date(*time.localtime(ticks)[:3])
def TimeFromTicks(ticks):
return Time(*time.localtime(ticks)[3:6])
def TimestampFromTicks(ticks):
return Timestamp(*time.localtime(ticks)[:6])* The preferred object type for Binary objects are the buffer types available in standard Python starting with version 1.5.2. Please see the Python documentation for details. For information about the C interface have a look at Include/bufferobject.h and Objects/bufferobject.c in the Python source distribution. * This Python class allows implementing the above type objects even though the description type code field yields multiple values for on type object: class DBAPITypeObject: def __init__(self,*values): self.values = values def __cmp__(self,other): if other in self.values: return 0 if other < self.values: return 1 else: return -1 The resulting type object compares equal to all values passed to the constructor. * Here is a snippet of Python code that implements the exception hierarchy defined above: import exceptions class Error(exceptions.StandardError): pass class Warning(exceptions.StandardError): pass class InterfaceError(Error): pass class DatabaseError(Error): pass class InternalError(DatabaseError): pass class OperationalError(DatabaseError): pass class ProgrammingError(DatabaseError): pass class IntegrityError(DatabaseError): pass class DataError(DatabaseError): pass class NotSupportedError(DatabaseError): pass In C you can use the PyErr_NewException(fullname, base, NULL) API to create the exception objects.
オプションのDB API拡張(Optional DB API Extensions):
DB API 2.0 ，
， DB API ，
DB API2.0 。
DB API ，
， AttributeError
NotSupportedError， 。
， （Python warnings）
， 。
“ ”（"Warning Message"）。
Cursor Attribute .rownumber
This read-only attribute should provide the current 0-based
index of the cursor in the result set or None if the index
cannot be determined.
The index can be seen as index of the cursor in a sequence
(the result set). The next fetch operation will fetch the row
indexed by .rownumber in that sequence.
（Warning Message）： "DB-API extension cursor.rownumber used"
Connection Attributes .Error, .ProgrammingError, etc.
All exception classes defined by the DB API standard should be
exposed on the Connection objects as attributes (in addition
to being available at module scope).
These attributes simplify error handling in multi-connection
environments.
（Warning Message）： "DB-API extension connection. used"
Cursor Attributes .connection
This read-only attribute return a reference to the Connection
object on which the cursor was created.
The attribute simplifies writing polymorph code in
multi-connection environments.
Warning Message: "DB-API extension cursor.connection used"
Cursor Method .scroll(value[,mode='relative'])
Scroll the cursor in the result set to a new position according
to mode.
If mode is 'relative' (default), value is taken as offset to
the current position in the result set, if set to 'absolute',
value states an absolute target position.
An IndexError should be raised in case a scroll operation would
leave the result set. In this case, the cursor position is left
undefined (ideal would be to not move the cursor at all).
Note: This method should use native scrollable cursors, if
available , or revert to an emulation for forward-only
scrollable cursors. The method may raise NotSupportedErrors to
signal that a specific operation is not supported by the
database (e.g. backward scrolling).
（Warning Message）： "DB-API extension cursor.scroll() used"
Cursor Attribute .messages
This is a Python list object to which the interface appends
tuples (exception class, exception value) for all messages
which the interfaces receives from the underlying database for
this cursor.
The list is cleared by all standard cursor methods calls (prior
to executing the call) except for the .fetch*() calls
automatically to avoid excessive memory usage and can also be
cleared by executing "del cursor.messages[:]".
All error and warning messages generated by the database are
placed into this list, so checking the list allows the user to
verify correct operation of the method calls.
The aim of this attribute is to eliminate the need for a
Warning exception which often causes problems (some warnings
really only have informational character).
（Warning Message）： "DB-API extension cursor.messages used" Connection Attribute .messages Same as cursor.messages except that the messages in the list are connection oriented. The list is cleared automatically by all standard connection methods calls (prior to executing the call) to avoid excessive memory usage and can also be cleared by executing "del connection.messages[:]". （Warning Message）："DB-API extension connection.messages used" Cursor Method .next() Return the next row from the currently executing SQL statement using the same semantics as .fetchone(). A StopIteration exception is raised when the result set is exhausted for Python versions 2.2 and later. Previous versions don't have the StopIteration exception and so the method should raise an IndexError instead. （Warning Message）："DB-API extension cursor.next() used" Cursor Method .__iter__() Return self to make cursors compatible to the iteration protocol [8]. （Warning Message）："DB-API extension cursor.__iter__() used" Cursor Attribute .lastrowid This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany().（Warning Message）： "DB-API extension cursor.lastrowid used"
オプションのエラー処理拡張(Optional Error Handling Extensions):The core DB API specification only introduces a set of exceptions which can be raised to report errors to the user. In some cases, exceptions may be too disruptive for the flow of a program or even render execution impossible. For these cases and in order to simplify error handling when dealing with databases, database module authors may choose to implement user defineable error handlers. This section describes a standard way of defining these error handlers. Cursor/Connection Attribute .errorhandler Read/write attribute which references an error handler to call in case an error condition is met. The handler must be a Python callable taking the following arguments: errorhandler(connection, cursor, errorclass, errorvalue) where connection is a reference to the connection on which the cursor operates, cursor a reference to the cursor (or None in case the error does not apply to a cursor), errorclass is an error class which to instantiate using errorvalue as construction argument. The standard error handler should add the error information to the appropriate .messages attribute (connection.messages or cursor.messages) and raise the exception defined by the given errorclass and errorvalue parameters. If no errorhandler is set (the attribute is None), the standard error handling scheme as outlined above, should be applied. Warning Message: "DB-API extension .errorhandler used" Cursors should inherit the .errorhandler setting from their connection objects at cursor creation time.
オプションの2段階コミット拡張(Optional Two-Phase Commit Extensions):Many databases have support for two-phase commit (TPC) which allows managing transactions across multiple database connections and other resources. If a database backend provides support for two-phase commit and the database module author wishes to expose this support, the following API should be implemented. NotSupportedError should be raised, if the database backend support for two-phase commit can only be checked at run-time. TPC Transaction IDs As many databases follow the XA specification, transaction IDs are formed from three components: * a format ID * a global transaction ID * a branch qualifier For a particular global transaction, the first two components should be the same for all resources. Each resource in the global transaction should be assigned a different branch qualifier. The various components must satisfy the following criteria: * format ID: a non-negative 32-bit integer. * global transaction ID and branch qualifier: byte strings no longer than 64 characters. Transaction IDs are created with the .xid() connection method: .xid(format_id, global_transaction_id, branch_qualifier) Returns a transaction ID object suitable for passing to the .tpc_*() methods of this connection. If the database connection does not support TPC, a NotSupportedError is raised. The type of the object returned by .xid() is not defined, but it must provide sequence behaviour, allowing access to the three components. A conforming database module could choose to represent transaction IDs with tuples rather than a custom object. TPC Connection Methods .tpc_begin(xid) Begins a TPC transaction with the given transaction ID xid. This method should be called outside of a transaction (i.e. nothing may have executed since the last .commit() or .rollback()). Furthermore, it is an error to call .commit() or .rollback() within the TPC transaction. A ProgrammingError is raised, if the application calls .commit() or .rollback() during an active TPC transaction. If the database connection does not support TPC, a NotSupportedError is raised. .tpc_prepare() Performs the first phase of a transaction started with .tpc_begin(). A ProgrammingError should be raised if this method outside of a TPC transaction. After calling .tpc_prepare(), no statements can be executed until tpc_commit() or tpc_rollback() have been called. .tpc_commit([xid]) When called with no arguments, .tpc_commit() commits a TPC transaction previously prepared with .tpc_prepare(). If .tpc_commit() is called prior to .tpc_prepare(), a single phase commit is performed. A transaction manager may choose to do this if only a single resource is participating in the global transaction. When called with a transaction ID xid, the database commits the given transaction. If an invalid transaction ID is provided, a ProgrammingError will be raised. This form should be called outside of a transaction, and is intended for use in recovery. On return, the TPC transaction is ended. .tpc_rollback([xid]) When called with no arguments, .tpc_rollback() rolls back a TPC transaction. It may be called before or after .tpc_prepare(). When called with a transaction ID xid, it rolls back the given transaction. If an invalid transaction ID is provided, a ProgrammingError is raised. This form should be called outside of a transaction, and is intended for use in recovery. On return, the TPC transaction is ended. .tpc_recover() Returns a list of pending transaction IDs suitable for use with .tpc_commit(xid) or .tpc_rollback(xid). If the database does not support transaction recovery, it may return an empty list or raise NotSupportedError.
よくある質問(Frequently Assked Questions):DB API 。 。 Question: .fetch*() ， tuples。 Answer: 。 .description 。 ， DB API .fetch ， 。 * ， 。 * ， 。 ， ， ， 。
1.0～2.0の主な変更(Major Changes from Version 1.0 to Version 2.0):Python DB API 2.0 1.0 。 DB API 1.0 。 ， DB-API 2.0 。 1.0 2.0 ： * dbi ， 。 * / ，RAW BINARY。 SQL 。 * ， （apilevel, threadlevel, paramstyle） （.executemany(), .nextset()）。 * .callproc()。 * .execute() 。 ， SQL （ ）-- ； .rowcount 。 ， ， 。 * 。 。 DB API 2.0 ： * DB-API 。
残された問題(Open Issues):2.0 1.0 ， ： * Define a useful return value for .nextset() for the case where a new result set is available. * Integrate the decimal module Decimal object for use as loss-less monetary and decimal interchange format.
脚注(Footnotes):[1] ， the connection constructor parameters should be implemented as keyword parameters for more intuitive use and follow this order of parameters: dsn Data source name as string user User name as string (optional) password Password as string (optional) host Hostname (optional) database Database name (optional) E.g. a connect could look like this: connect(dsn='myhost:MYDB',user='guido',password='234$') [2] Module implementors should prefer 'numeric', 'named' or 'pyformat' over the other formats because these offer more clarity and flexibility. [3] If the database does not support the functionality required by the method, the interface should throw an exception in case the method is used. The preferred approach is to not implement the method and thus have Python generate an AttributeError in case the method is requested. This allows the programmer to check for database capabilities using the standard hasattr() function. For some dynamically configured interfaces it may not be appropriate to require dynamically making the method available. These interfaces should then raise a NotSupportedError to indicate the non-ability to perform the roll back when the method is invoked. [4] a database interface may choose to support named cursors by allowing a string argument to the method. This feature is not part of the specification, since it complicates semantics of the .fetch*() methods. [5] The module will use the __getitem__ method of the parameters object to map either positions (integers) or names (strings) to parameter values. This allows for both sequences and mappings to be used as input. The term "bound" refers to the process of binding an input value to a database execution buffer. In practical terms, this means that the input value is directly used as a value in the operation. The client should not be required to "escape" the value so that it can be used -- the value should be equal to the actual database value. [6] Note that the interface may implement row fetching using arrays and other optimizations. It is not guaranteed that a call to this method will only move the associated cursor forward by one row. [7] The rowcount attribute may be coded in a way that updates its value dynamically. This can be useful for databases that return usable rowcount values only after the first call to a .fetch*() method. [8] Implementation Note: Python C extensions will have to implement the tp_iter slot on the cursor object instead of the .__iter__() method.
感謝(Acknowledgements):Andrew Kuchling， Python Database API Specification 2.0 HTML PEP 。 James Henstridge API 。