The API-specification of databases of language Python, version 2.0
The interface of the module
Access to a database is realized with the help of objects of connection (connection objects). The following designer should give the module for them:
connect (parameters...)
The designer for creation of connection with a database. Returns Object of connection. Has a number{line} of parameters which depend on a database. [1]
The following global variables of the module should be determined:
apilevel
The line constant designating supported level DB API. Now lines '1.0' and '2.0' are supposed only.
If not it is set, level Database API 1.0 is supposed.
threadsafety
The integer constant designating a level nitebezopasnosti, supported by the given interface. Possible{probable} values:
0 = the module cannot be divided{shared} by strings.
1 = strings can divide{share} the module, but not connections.
2 = strings can divide{share} both the module, and connections.
3 = strings can divide{share} the module, connections and cursors.
Division in the above-stated context means, that two strings can use a resource, not using external object of synchronization (mutex) for blocking. Notice, that you not always can provide nitebezopasnost` for external resources, operating access with the help of a semaphore: the resource can use the global variable or other external resources which are taking place outside of your control.
paramstyle
The line constant designating type of formatting of a marker of parameter, expected by the interface. Possible{probable} values [2]:
'qmark' = Style of a question, for example '... WHERE name =? '
'numeric' = the Numerical (item) marker, for example '... WHERE name =:1 '
'named' = the Called marker, for example '... WHERE name =:name '
'format' = Codes of format ANSI C printf, for example '... WHERE name = % s'
'pyformat' = the Expanded codes of a format of language Python, for example '... WHERE name = % (name) s'
The module should provide the information on mistakes with the help of the following exceptions or their subclasses:
Warning
The exception caused for the important preventions{warnings}, such, as truncation of the data during an insert, etc. This class should be a derivative from class Python StandardError (certain{determined} in the module exceptions).
Error
The exception being a base class of all other exceptions, connected to mistakes. You can use it{him} for interception of all exceptions with the help of the unique statement 'except'. Preventions{warnings} are not considered{examined} as a mistake and consequently should not use this class as base. This class should be a derivative from class Python StandardError (certain{determined} in the module exceptions).
InterfaceError
Exception is raised for the mistakes concerning to the interface of a database in the greater degree, than to the base. Should be a derivative from class Error.
DatabaseError
Exception is raised for the mistakes concerning to a database. It should be a subclass of class Error.
DataError
Exception is raised for the mistakes concerning to problems with the processable data, such as division into a zero, an output{exit} of size for limits of allowable values, etc. Should be a derivative from class DatabaseError.
OperationalError
Exception is raised for the mistakes concerning to operations of a database, not necessarily taking place under the control of the programmer over unexpected breakage of connection, the name of a source of the data is not found, it is impossible to process transaction, a mistake of distribution of memory during processing, etc. Should be a derivative from class DatabaseError.
IntegrityError
Exception is raised in a case when reference integrity of a database is mentioned, for example, attempt of the link to nonexistent external recording. Should be a derivative from class DatabaseError.
InternalError
Exception is raised in case the database finds out an internal mistake, for example, the cursor any more is not allowable, transaction is not synchronized, etc. Should be a derivative from class DatabaseError.
ProgrammingError
Exception is raised in case of mistakes of programming, for example, the table is not found or already exists, a syntactic mistake in offer SQL, the incorrect number of parameters, etc. is set. Should be a derivative from class DatabaseError.
NotSupportedError
Exception is raised in case of use of call API which is not supported by a database, for example, search .rollback () to the connection which is not supporting transactions or transactions at which are switched - off. Should be a derivative from class DatabaseError.
The circuit of inheritance of exceptions:
StandardError
| __ Warning
| __ Error
| __ InterfaceError
| __ DatabaseError
| __ DataError
| __ OperationalError
| __ IntegrityError
| __ InternalError
| __ ProgrammingError
| __ NotSupportedError
The note: Values of these exceptions are not determined. They should prompt distinctly{clearly} to the user what exactly went not so.
Objects of connection
Objects of connection should react to the following calls:
close ()
To close connection now (instead of by a call __ del __). Connection will be unused with this moment and further; if any operation will try to use this connection, exception Error (or his{its} subclass) will be excited. The same is applied and to all objects of the cursor, trying to use this connection.
commit ()
To finish any uncompleted transaction in a database. Notice, that if the base supports automatic fixing, in a starting position she should be switched - off. The interface can give a method for inclusion of autofixing.
Modules of the databases which are not supporting transactions, should realize this method as zaglushki.
rollback ()
This method is not obligatory as not all databases provide support of transactions. [3]
In case the database provides support of transactions, this method forces a database to roll away the uncompleted transaction to the beginning. Closing of connection without preliminary obvious fixing changes will lead to to implicit recoil.
cursor ()
To return new Object of the cursor, using connection. If the base does not provide support of the concept of the cursor, the module should emulate cursors, using other means in the limits necessary for this specification. [4]
Objects of the cursor
These objects represent the cursor of a database used for management by a context of operation of sample.
Objects of the cursor should support the following methods and attributes:
description
This read-only the attribute represents sequence of seven-element sequences. Each of such sequences contains the information describing one column of result: (name, type_code, display_size, internal_size, precision, scale, null_ok). This attribute will accept value None for the operations which are not returning lines or in a case, when the cursor yet has not executed operation with the help of a method executeXXX ().
Type_code It can be interpreted by his{its} comparison with the Objects of type determined in the following section.
rowcount
This read-only the attribute defines{determines} number of lines chosen (for offers DQL like select) or mentioned (for modifying offers DML like update or insert) last call executeXXX ().
This attribute accepts value-1 in case above the cursor any is not executed executeXXX () or if the interface cannot define{determine} quantity{amount} of lines in last operation. [7]
callproc (a name of procedure [, parameters])
This method is unessential, as not all databases support stored{kept} procedures. [3]
To call stored{kept} procedure with the set name. The sequence of parameters should contain one input{entrance} for each argument expected by procedure. The result of a call comes back as the modified copy of initial sequence. Input-parameters remain untouched, output and input/output parameters can be replaced with new values.
Procedure can provide also on an output{exit} resulting set. It then should be accessible through standard methods fetchXXX ().
close ()
To close the cursor (now, instead of by a call __ del __). From the moment of a call and further the cursor becomes unused; in case of attempt to address to some operation of the cursor, exception Error (or a subclass) is raised.
execute (operation [, parameters])
To prepare and execute operation of a database (search or the command). Parameters can be provided as sequence or the dictionary and will be adhered to variables in operation. Variables are set in the notation specific to a database (it is detailed see attribute of the module paramstyle). [5]
The link to operation will be stored{kept} by the cursor. If the same object of operation is passed again, the cursor can optimize the behaviour. It is the most effective for algorithms in which are many times used the same operation, but with binding various parameters.
For peak efficiency at a reuse of operation, it is the best way to use a method setinputsizes () beforehand to set types and the sizes of parameters. For parameter discrepancy of beforehand certain information is allowable; realization should compensate it, probably, with some loss of efficiency.
Parameters can be determined also as the list of trains, for example, for an insert of set of lines within the framework of one operation, but it is not encouraged: instead of it it is necessary to use executemany ().
Returned values are not determined.
executemany (operation, seq_of_parameters)
To prepare operation of a database (search or the command) and then to execute her{it} for all sequences or the dictionaries found in sequence of parameters seq_of_parameters.
Modules are free in realization of this method or with use plural vyzovovo a method execute (), or with use of massive operations to force a database to process sequence entirely for one call.
The notes given for execute (), are applicable{applied} as well to this method.
Returned values are not determined.
fetchone ()
Chooses the next line from resulting set - result of search, returning single sequence, or None in a case when the data no more. [6]
Exception Error (or a subclass) is raised, if the previous call executeXXX () has not returned resulting set, or yet there was no such call.
fetchmany ([razmer=cursor.arraysize])
Chooses the following set of lines from result of search, returning sequence of sequences (for example, the list of trains). The empty sequence comes back, if there are no lines more.
The number of lines for sample for one call is defined{determined} by parameter. If the parameter is not set, the number of lines for sample defines{determines} attribute of the cursor arraysize. This method should try to choose so much lines, how much is specified in parameter. If such quantity{amount} of lines is inaccessible, the smaller number of lines can be returned.
Exception Error (or a subclass) is raised, if the previous call executeXXX () has not returned resulting set, or yet there was no such call.
Note, that there are some reasons connected to parameter of the size. For achievement of optimum productivity it is usually the best way to use attribute arraysize. If the parameter of the size is used, it is better, that he saved the same size from one call fetchmany () to the following.
fetchall ()
Chooses all (staying) lines of result of search, returning them as sequence of sequences (for example, the list of trains). Notice, that the attribute of the cursor arraysize can influence productivity of this operation.
Exception Error (or a subclass) is raised, if the previous call executeXXX () has not returned resulting set, or yet there was no such call.
nextset ().
This method is not obligatory as not all databases support some resulting sets [3]
This method will force the cursor to proceed{pass} to the following available set, ignoring all staying lines of the current set.
If more sets no, the method returns None. Otherwise he returns value of the validity, and the subsequent references{manipulations} to methods of sample will return lines from the following resulting set.
Exception Error (or a subclass) is raised, if the previous call executeXXX () has not returned resulting set, or yet there was no such call.
arraysize
This read/write the attribute defines{determines} number of the lines chosen fetchmany () for once. By default matters 1, meaning sample of one line for once.
Realizations should adhere to this value in that, as to a method fetchmany (), however they have the full right to choose on one line for time at interaction with a database. This attribute can be used also in realization executemany ().
setinputsizes (sizes)
It can be used before a call executeXXX () for preliminary definition of areas of memory under parameters of operation.
The parameter sizes (sizes) is determined as sequence - one element for each entrance parameter. The element should be object of type (Type Object, for example, IntType), corresponding to type of parameter, or an integer determining the maximal length of line parameter. If the element matters None, under this column will not be preserved beforehand certain area of memory (it poleehno to avoid allocation of areas for the big inputs).
This method can be used before a call of a method executeXXX ().
Realizations can leave this method zaglushkoj, and users can not use it{him}.
setoutputsize (the size [, a column])
Establishes the size of the buffer of a column for sample big stolbcov (for example, LONG, BLOB, etc.). The column is set as an index (number{room} of a column) in rezul`tatirujuhhem set. If the column is not set, the size by default for all big stolbcov in the cursor is established.
This method can be used before a call of a method executeXXX ().
Realizations can leave this method zaglushkoj, and users can not use it{him}.
Objects of type and designers
Many databases demand the entrance data in the certain format for binding to input-parameters of operations. For example, if the data are intended for a column such as DATE, they should be are prepared for a database in the certain line format. The same problems exist for stolbcov " Row ID " or the big binary elements (for example, blob'ov or stolbcov RAW). It represents a problem for Python, as parameters of a method executeXXX () are not typified. When the module of a database sees line object Python, he does not know, whether it is necessary to adhere it{him} as a simple symbolical column (CHAR), as the raw binary element (BINARY) or as date (DATE).
For overcoming this problem the module should provide the designers described below for creation of objects which can store{keep} special values. When such object peredetsja to methods of the cursor, the module can define{determine} correct type of entrance parameter and adhere it{him} in appropriate way.
The attribute of object of the cursor description returns the information on each of stolbcov result of search. The code such as type_code should be checked up on equality to one of the Objects of type described below. Objects of type can be equal more than to one code of type (for example, DATETIME it can be equal to codes of type for stolbcov dates, time and a time label; in detail see. Advice{councils} on the realizations given below).
The module exports the following designers and single-element sets:
Date (year, month, day)
This function creates the object containing value of date.
Time (hour, minute, second)
This function creates the object containing value of time.
Timestamp (year, month, day, hour, minute, second)
This function creates the object containing value of a time label.
DateFromTicks (readout)
This function creates the object containing value of date, from the set quantity{amount} of readout (ticks) (numbers of seconds from the beginning of an epoch; in detail see the documentation on the standard module time language Python).
TimeFromTicks (readout)
This function creates the object containing value of time, from the set quantity{amount} of readout (ticks) (numbers of seconds from the beginning of an epoch; in detail see the documentation on the standard module time language Python).
TimestampFromTicks (readout)
This function creates the object containing value of a time label, from the set quantity{amount} of readout (ticks) (numbers of seconds from the beginning of an epoch; in detail see the documentation on the standard module time language Python).
BINARY (line)
This function creates the object, capable to contain binary (long) line value.
STRING
This object of type is used for the description line stolbcov in a database (for example, CHAR).
BINARY
This object of type is used for the description (long) binary stolbcov in a database (for example, LONG, RAW, BLOB).
NUMBER
This object of type is used for the description numerical stolbcov in a database.
DATETIME
This object of type is used for the description in a database stolbcov date / time.
ROWID
This object of type is used for the description in a database stolbcov " Row ID ".
NULL-values SQL are represented with help None at input and output.
The note: Use of readout Unix at interaction with a database can create problems because of limitation of a spectrum of dates described by them.
Advice{Councils} on realization
* Preferable types for objects date / time are that are determined in a package mxDateTime. He provides necessary designers and methods as at level Python, and Ci.
* Preferable type of object for binary objects are the types of buffers accessible in standard Python, since version 1.5.2. For the additional information address to the documentation on Python. For the information on the interface to Si look in Include/bufferobject.h and Objects/bufferobject.c in source codes Python.
* An example of realization of designers of date / time basing readout Unix, delegating job to the general{common} designers:
import time
def DateFromTicks (ticks):
return apply (Date, time.localtime (ticks) [:3])
def TimeFromTicks (ticks):
return apply (Time, time.localtime (ticks) [3:6])
def TimestampFromTicks (ticks):
return apply (Timestamp, time.localtime (ticks) [:6])
* This class of language Python allows to realize the above-stated objects of type even in spite of the fact that the field of a code of type contains set of values for one object of type:
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 object of type at comparison on equality appears to the equal all values transferred{handed} to the designer.
* A fragment of a code of language Python realizing hierarchy of exceptions, above mentioned:
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 language Si for creation of objects of exceptions you can use API PyErr_NewException (fullname, base, NULL).
The basic changes in version 2.0 concerning version 1.0
Python Database API 2.0 enters some significant changes concerning version 1.0. Whereas some from these changes will lead to to infringement of job of the existing scripts based on DB API 1.0, senior number{room} of the version have been increased for reflection of this change.
The most important changes between versions 1.0 and 2.0:
* Has disappeared necessity for the separate module dbi, this function is built - in the interface of the module.
* New designers and Objects of type for values date / time, Objects such as RAW have been added were renamed in BINARY. The received set should cover all basic types of the data usually available in modern databases SQL.
* (apilevel, threadlevel, paramstyle) and methods (executemany, nextset) for maintenance of the best binding new constants have been added to databases.
* Now semantics .callproc (), necessary for a call of stored{kept} procedures is clearly determined.
* Definition of returned value .execute () is changed. Earlier returned value was based on type of offer SQL (that it was extremely difficult to realize correctly) - now it is not determined; use instead of him{it} more floppy attribute .rowcount. Modules are free in returning values in an old kind, however it any more is not offered by the specification and should be considered dependent on the interface of a database.
* The exceptions based on classes, have been built - in the specification. Realizatory the module are free to expand the circuit of exceptions determined in this specification, by inheritance from the described classes of exceptions.
Open questions
In spite of the fact that the specification of version 2.0 has cleared many of the questions left open in version 1.0, there are some more problems:
* To define{determine} suitable value of return .nextset () for a case of presence of new resulting set.
* To create numerical type with the fixed point for use in quality not having losses of accuracy of monetary and decimal formats.
Footnotes
1. As a guideline parameters of the designer of connection should be realized as key parameters for more intuitive use and according to the following sequence of parameters:
dsn = the Source of the data as a line
user = the Login name as a line (unessentially)
password = the Password as a line (unessentially)
host = the Host name (is unessential)
database = the Name of a database (unessentially)
For example, connection can look as follows:
connect (dsn ='myhost:MYDB ', user ='guido ', password = ' 234 $ ')
2. Realizatory the module should prefer 'numeric', 'named' and 'pyformat' before other formats as it assumes the big clearness and flexibility.
3. If the database does not support the function necessary for a method, the interface should raise exception at attempt of use of this method.
The preferable approach consists in refusal of realization of such method, thus resulting to generation by language Python of exception AttributeError in case this method is requested. It allows the programmer to check up opportunities of a database, using standard function hasattr ().
Necessity of dynamic creation of a method can not suit some dynamically configured interfaces. These interfaces in that case should cause exception NotSupportedError for the instruction{indication} on impossibility of execution{performance} of recoil when the method is already called.
4. The interface of a database can support the called cursors by the sanction of line argument for a method. This property is not a part of the specification, as it complicates semantics of methods .fetchXXX ().
5. The module will use a method __ getitem __ object of parameters for display as positions (integers), and names (lines) in values of parameters. It allows to use as entrance sizes as sequences, and dictionaries.
The term "binding" concerns to process of binding of entrance value to the buffer of performance of a database. In practical terms it means, that entrance value directly is used as value in operation. From the client "allocation" ("escaping") this value should not be required so that it{he} could be used - value should be equal to the valid value from a database.
6. Notice, that the interface can realize sample of lines with use of files and other kinds of optimization. There is no guarantee, that the reference{manipulation} to this method will shift the corresponding cursor forward only for one line.
7. The attribute rowcount can be programmed by the way dynamically updating his{its} value. It can be useful for the databases returning intelligent values rowcount only after the first call of a method .fetchXXX ().
| 
