« back to all changes in this revision
Viewing changes to doc/pep-0249.txt
- browse files at revision 270
- compare with another revision
- download diff
- download tarball
- view history from revision 270
- Committer: Federico Di Gregorio
- Date: 2007-04-10 06:36:18 UTC
- Revision ID: fog-7050d8d8d2669efe71403b32fda453f9b1f6a470
Fixed both Python 2.5 and 64 bit problems.
- files added:
- LICENSE
- debian
- psycopg2da
- files removed:
- ZPsycopgDA/DABase.py
- files modified:
- ChangeLog
added
removed
doc/pep-0249.txt
1
PEP: 249
2
Title: Python Database API Specification v2.0
3
Version: $Revision: 1555 $
4
Author: db-sig@python.org (Python Database SIG)
5
Editor: mal@lemburg.com (Marc-Andre Lemburg)
6
Status: Final
7
Type: Informational
8
Replaces: 248
9
Release-Date: 07 Apr 1999
10
11
Introduction
12
13
This API has been defined to encourage similarity between the
14
Python modules that are used to access databases. By doing this,
15
we hope to achieve a consistency leading to more easily understood
16
modules, code that is generally more portable across databases,
17
and a broader reach of database connectivity from Python.
18
19
The interface specification consists of several sections:
20
21
* Module Interface
22
* Connection Objects
23
* Cursor Objects
24
* DBI Helper Objects
25
* Type Objects and Constructors
26
* Implementation Hints
27
* Major Changes from 1.0 to 2.0
28
29
Comments and questions about this specification may be directed
30
to the SIG for Database Interfacing with Python
31
(db-sig@python.org).
32
33
For more information on database interfacing with Python and
34
available packages see the Database Topic
35
Guide at http://www.python.org/topics/database/.
36
37
This document describes the Python Database API Specification 2.0
38
and a set of common optional extensions. The previous version 1.0
39
version is still available as reference, in PEP 248. Package
40
writers are encouraged to use this version of the specification as
41
basis for new interfaces.
42
43
Module Interface
44
45
Access to the database is made available through connection
46
objects. The module must provide the following constructor for
47
these:
48
49
connect(parameters...)
50
51
Constructor for creating a connection to the database.
52
Returns a Connection Object. It takes a number of
53
parameters which are database dependent. [1]
54
55
These module globals must be defined:
56
57
apilevel
58
59
String constant stating the supported DB API level.
60
Currently only the strings '1.0' and '2.0' are allowed.
61
62
If not given, a DB-API 1.0 level interface should be
63
assumed.
64
65
threadsafety
66
67
Integer constant stating the level of thread safety the
68
interface supports. Possible values are:
69
70
0 Threads may not share the module.
71
1 Threads may share the module, but not connections.
72
2 Threads may share the module and connections.
73
3 Threads may share the module, connections and
74
cursors.
75
76
Sharing in the above context means that two threads may
77
use a resource without wrapping it using a mutex semaphore
78
to implement resource locking. Note that you cannot always
79
make external resources thread safe by managing access
80
using a mutex: the resource may rely on global variables
81
or other external sources that are beyond your control.
82
83
paramstyle
84
85
String constant stating the type of parameter marker
86
formatting expected by the interface. Possible values are
87
[2]:
88
89
'qmark' Question mark style,
90
e.g. '...WHERE name=?'
91
'numeric' Numeric, positional style,
92
e.g. '...WHERE name=:1'
93
'named' Named style,
94
e.g. '...WHERE name=:name'
95
'format' ANSI C printf format codes,
96
e.g. '...WHERE name=%s'
97
'pyformat' Python extended format codes,
98
e.g. '...WHERE name=%(name)s'
99
100
The module should make all error information available through
101
these exceptions or subclasses thereof:
102
103
Warning
104
105
Exception raised for important warnings like data
106
truncations while inserting, etc. It must be a subclass of
107
the Python StandardError (defined in the module
108
exceptions).
109
110
Error
111
112
Exception that is the base class of all other error
113
exceptions. You can use this to catch all errors with one
114
single 'except' statement. Warnings are not considered
115
errors and thus should not use this class as base. It must
116
be a subclass of the Python StandardError (defined in the
117
module exceptions).
118
119
InterfaceError
120
121
Exception raised for errors that are related to the
122
database interface rather than the database itself. It
123
must be a subclass of Error.
124
125
DatabaseError
126
127
Exception raised for errors that are related to the
128
database. It must be a subclass of Error.
129
130
DataError
131
132
Exception raised for errors that are due to problems with
133
the processed data like division by zero, numeric value
134
out of range, etc. It must be a subclass of DatabaseError.
135
136
OperationalError
137
138
Exception raised for errors that are related to the
139
database's operation and not necessarily under the control
140
of the programmer, e.g. an unexpected disconnect occurs,
141
the data source name is not found, a transaction could not
142
be processed, a memory allocation error occurred during
143
processing, etc. It must be a subclass of DatabaseError.
144
145
IntegrityError
146
147
Exception raised when the relational integrity of the
148
database is affected, e.g. a foreign key check fails. It
149
must be a subclass of DatabaseError.
150
151
InternalError
152
153
Exception raised when the database encounters an internal
154
error, e.g. the cursor is not valid anymore, the
155
transaction is out of sync, etc. It must be a subclass of
156
DatabaseError.
157
158
ProgrammingError
159
160
Exception raised for programming errors, e.g. table not
161
found or already exists, syntax error in the SQL
162
statement, wrong number of parameters specified, etc. It
163
must be a subclass of DatabaseError.
164
165
NotSupportedError
166
167
Exception raised in case a method or database API was used
168
which is not supported by the database, e.g. requesting a
169
.rollback() on a connection that does not support
170
transaction or has transactions turned off. It must be a
171
subclass of DatabaseError.
172
173
This is the exception inheritance layout:
174
175
StandardError
176
|__Warning
177
|__Error
178
|__InterfaceError
179
|__DatabaseError
180
|__DataError
181
|__OperationalError
182
|__IntegrityError
183
|__InternalError
184
|__ProgrammingError
185
|__NotSupportedError
186
187
Note: The values of these exceptions are not defined. They should
188
give the user a fairly good idea of what went wrong, though.
189
190
191
Connection Objects
192
193
Connection Objects should respond to the following methods:
194
195
.close()
196
197
Close the connection now (rather than whenever __del__ is
198
called). The connection will be unusable from this point
199
forward; an Error (or subclass) exception will be raised
200
if any operation is attempted with the connection. The
201
same applies to all cursor objects trying to use the
202
connection. Note that closing a connection without
203
committing the changes first will cause an implicit
204
rollback to be performed.
205
206
207
.commit()
208
209
Commit any pending transaction to the database. Note that
210
if the database supports an auto-commit feature, this must
211
be initially off. An interface method may be provided to
212
turn it back on.
213
214
Database modules that do not support transactions should
215
implement this method with void functionality.
216
217
.rollback()
218
219
This method is optional since not all databases provide
220
transaction support. [3]
221
222
In case a database does provide transactions this method
223
causes the the database to roll back to the start of any
224
pending transaction. Closing a connection without
225
committing the changes first will cause an implicit
226
rollback to be performed.
227
228
.cursor()
229
230
Return a new Cursor Object using the connection. If the
231
database does not provide a direct cursor concept, the
232
module will have to emulate cursors using other means to
233
the extent needed by this specification. [4]
234
235
236
Cursor Objects
237
238
These objects represent a database cursor, which is used to
239
manage the context of a fetch operation. Cursors created from
240
the same connection are not isolated, i.e., any changes
241
done to the database by a cursor are immediately visible by the
242
other cursors. Cursors created from different connections can
243
or can not be isolated, depending on how the transaction support
244
is implemented (see also the connection's rollback() and commit()
245
methods.)
246
247
Cursor Objects should respond to the following methods and
248
attributes:
249
250
.description
251
252
This read-only attribute is a sequence of 7-item
253
sequences. Each of these sequences contains information
254
describing one result column: (name, type_code,
255
display_size, internal_size, precision, scale,
256
null_ok). The first two items (name and type_code) are
257
mandatory, the other five are optional and must be set to
258
None if meaningfull values are not provided.
259
260
This attribute will be None for operations that
261
do not return rows or if the cursor has not had an
262
operation invoked via the executeXXX() method yet.
263
264
The type_code can be interpreted by comparing it to the
265
Type Objects specified in the section below.
266
267
.rowcount
268
269
This read-only attribute specifies the number of rows that
270
the last executeXXX() produced (for DQL statements like
271
'select') or affected (for DML statements like 'update' or
272
'insert').
273
274
The attribute is -1 in case no executeXXX() has been
275
performed on the cursor or the rowcount of the last
276
operation is not determinable by the interface. [7]
277
278
Note: Future versions of the DB API specification could
279
redefine the latter case to have the object return None
280
instead of -1.
281
282
.callproc(procname[,parameters])
283
284
(This method is optional since not all databases provide
285
stored procedures. [3])
286
287
Call a stored database procedure with the given name. The
288
sequence of parameters must contain one entry for each
289
argument that the procedure expects. The result of the
290
call is returned as modified copy of the input
291
sequence. Input parameters are left untouched, output and
292
input/output parameters replaced with possibly new values.
293
294
The procedure may also provide a result set as
295
output. This must then be made available through the
296
standard fetchXXX() methods.
297
298
.close()
299
300
Close the cursor now (rather than whenever __del__ is
301
called). The cursor will be unusable from this point
302
forward; an Error (or subclass) exception will be raised
303
if any operation is attempted with the cursor.
304
305
.execute(operation[,parameters])
306
307
Prepare and execute a database operation (query or
308
command). Parameters may be provided as sequence or
309
mapping and will be bound to variables in the operation.
310
Variables are specified in a database-specific notation
311
(see the module's paramstyle attribute for details). [5]
312
313
A reference to the operation will be retained by the
314
cursor. If the same operation object is passed in again,
315
then the cursor can optimize its behavior. This is most
316
effective for algorithms where the same operation is used,
317
but different parameters are bound to it (many times).
318
319
For maximum efficiency when reusing an operation, it is
320
best to use the setinputsizes() method to specify the
321
parameter types and sizes ahead of time. It is legal for
322
a parameter to not match the predefined information; the
323
implementation should compensate, possibly with a loss of
324
efficiency.
325
326
The parameters may also be specified as list of tuples to
327
e.g. insert multiple rows in a single operation, but this
328
kind of usage is depreciated: executemany() should be used
329
instead.
330
331
Return values are not defined.
332
333
.executemany(operation,seq_of_parameters)
334
335
Prepare a database operation (query or command) and then
336
execute it against all parameter sequences or mappings
337
found in the sequence seq_of_parameters.
338
339
Modules are free to implement this method using multiple
340
calls to the execute() method or by using array operations
341
to have the database process the sequence as a whole in
342
one call.
343
344
Use of this method for an operation which produces one or
345
more result sets constitutes undefined behavior, and the
346
implementation is permitted (but not required) to raise
347
an exception when it detects that a result set has been
348
created by an invocation of the operation.
349
350
The same comments as for execute() also apply accordingly
351
to this method.
352
353
Return values are not defined.
354
355
.fetchone()
356
357
Fetch the next row of a query result set, returning a
358
single sequence, or None when no more data is
359
available. [6]
360
361
An Error (or subclass) exception is raised if the previous
362
call to executeXXX() did not produce any result set or no
363
call was issued yet.
364
365
fetchmany([size=cursor.arraysize])
366
367
Fetch the next set of rows of a query result, returning a
368
sequence of sequences (e.g. a list of tuples). An empty
369
sequence is returned when no more rows are available.
370
371
The number of rows to fetch per call is specified by the
372
parameter. If it is not given, the cursor's arraysize
373
determines the number of rows to be fetched. The method
374
should try to fetch as many rows as indicated by the size
375
parameter. If this is not possible due to the specified
376
number of rows not being available, fewer rows may be
377
returned.
378
379
An Error (or subclass) exception is raised if the previous
380
call to executeXXX() did not produce any result set or no
381
call was issued yet.
382
383
Note there are performance considerations involved with
384
the size parameter. For optimal performance, it is
385
usually best to use the arraysize attribute. If the size
386
parameter is used, then it is best for it to retain the
387
same value from one fetchmany() call to the next.
388
389
.fetchall()
390
391
Fetch all (remaining) rows of a query result, returning
392
them as a sequence of sequences (e.g. a list of tuples).
393
Note that the cursor's arraysize attribute can affect the
394
performance of this operation.
395
396
An Error (or subclass) exception is raised if the previous
397
call to executeXXX() did not produce any result set or no
398
call was issued yet.
399
400
.nextset()
401
402
(This method is optional since not all databases support
403
multiple result sets. [3])
404
405
This method will make the cursor skip to the next
406
available set, discarding any remaining rows from the
407
current set.
408
409
If there are no more sets, the method returns
410
None. Otherwise, it returns a true value and subsequent
411
calls to the fetch methods will return rows from the next
412
result set.
413
414
An Error (or subclass) exception is raised if the previous
415
call to executeXXX() did not produce any result set or no
416
call was issued yet.
417
418
.arraysize
419
420
This read/write attribute specifies the number of rows to
421
fetch at a time with fetchmany(). It defaults to 1 meaning
422
to fetch a single row at a time.
423
424
Implementations must observe this value with respect to
425
the fetchmany() method, but are free to interact with the
426
database a single row at a time. It may also be used in
427
the implementation of executemany().
428
429
.setinputsizes(sizes)
430
431
This can be used before a call to executeXXX() to
432
predefine memory areas for the operation's parameters.
433
434
sizes is specified as a sequence -- one item for each
435
input parameter. The item should be a Type Object that
436
corresponds to the input that will be used, or it should
437
be an integer specifying the maximum length of a string
438
parameter. If the item is None, then no predefined memory
439
area will be reserved for that column (this is useful to
440
avoid predefined areas for large inputs).
441
442
This method would be used before the executeXXX() method
443
is invoked.
444
445
Implementations are free to have this method do nothing
446
and users are free to not use it.
447
448
.setoutputsize(size[,column])
449
450
Set a column buffer size for fetches of large columns
451
(e.g. LONGs, BLOBs, etc.). The column is specified as an
452
index into the result sequence. Not specifying the column
453
will set the default size for all large columns in the
454
cursor.
455
456
This method would be used before the executeXXX() method
457
is invoked.
458
459
Implementations are free to have this method do nothing
460
and users are free to not use it.
461
462
463
Type Objects and Constructors
464
465
Many databases need to have the input in a particular format for
466
binding to an operation's input parameters. For example, if an
467
input is destined for a DATE column, then it must be bound to the
468
database in a particular string format. Similar problems exist
469
for "Row ID" columns or large binary items (e.g. blobs or RAW
470
columns). This presents problems for Python since the parameters
471
to the executeXXX() method are untyped. When the database module
472
sees a Python string object, it doesn't know if it should be bound
473
as a simple CHAR column, as a raw BINARY item, or as a DATE.
474
475
To overcome this problem, a module must provide the constructors
476
defined below to create objects that can hold special values.
477
When passed to the cursor methods, the module can then detect the
478
proper type of the input parameter and bind it accordingly.
479
480
A Cursor Object's description attribute returns information about
481
each of the result columns of a query. The type_code must compare
482
equal to one of Type Objects defined below. Type Objects may be
483
equal to more than one type code (e.g. DATETIME could be equal to
484
the type codes for date, time and timestamp columns; see the
485
Implementation Hints below for details).
486
487
The module exports the following constructors and singletons:
488
489
Date(year,month,day)
490
491
This function constructs an object holding a date value.
492
493
Time(hour,minute,second)
494
495
This function constructs an object holding a time value.
496
497
Timestamp(year,month,day,hour,minute,second)
498
499
This function constructs an object holding a time stamp
500
value.
501
502
DateFromTicks(ticks)
503
504
This function constructs an object holding a date value
505
from the given ticks value (number of seconds since the
506
epoch; see the documentation of the standard Python time
507
module for details).
508
509
TimeFromTicks(ticks)
510
511
This function constructs an object holding a time value
512
from the given ticks value (number of seconds since the
513
epoch; see the documentation of the standard Python time
514
module for details).
515
516
TimestampFromTicks(ticks)
517
518
This function constructs an object holding a time stamp
519
value from the given ticks value (number of seconds since
520
the epoch; see the documentation of the standard Python
521
time module for details).
522
523
Binary(string)
524
525
This function constructs an object capable of holding a
526
binary (long) string value.
527
528
529
STRING
530
531
This type object is used to describe columns in a database
532
that are string-based (e.g. CHAR).
533
534
BINARY
535
536
This type object is used to describe (long) binary columns
537
in a database (e.g. LONG, RAW, BLOBs).
538
539
NUMBER
540
541
This type object is used to describe numeric columns in a
542
database.
543
544
DATETIME
545
546
This type object is used to describe date/time columns in
547
a database.
548
549
ROWID
550
551
This type object is used to describe the "Row ID" column
552
in a database.
553
554
SQL NULL values are represented by the Python None singleton on
555
input and output.
556
557
Note: Usage of Unix ticks for database interfacing can cause
558
troubles because of the limited date range they cover.
559
560
561
Implementation Hints for Module Authors
562
563
* The preferred object types for the date/time objects are those
564
defined in the mxDateTime package. It provides all necessary
565
constructors and methods both at Python and C level.
566
567
* The preferred object type for Binary objects are the
568
buffer types available in standard Python starting with
569
version 1.5.2. Please see the Python documentation for
570
details. For information about the the C interface have a
571
look at Include/bufferobject.h and
572
Objects/bufferobject.c in the Python source
573
distribution.
574
575
* Starting with Python 2.3, module authors can also use the object
576
types defined in the standard datetime module for date/time
577
processing. However, it should be noted that this does not
578
expose a C API like mxDateTime does which means that integration
579
with C based database modules is more difficult.
580
581
* Here is a sample implementation of the Unix ticks based
582
constructors for date/time delegating work to the generic
583
constructors:
584
585
import time
586
587
def DateFromTicks(ticks):
588
return apply(Date,time.localtime(ticks)[:3])
589
590
def TimeFromTicks(ticks):
591
return apply(Time,time.localtime(ticks)[3:6])
592
593
def TimestampFromTicks(ticks):
594
return apply(Timestamp,time.localtime(ticks)[:6])
595
596
* This Python class allows implementing the above type
597
objects even though the description type code field yields
598
multiple values for on type object:
599
600
class DBAPITypeObject:
601
def __init__(self,*values):
602
self.values = values
603
def __cmp__(self,other):
604
if other in self.values:
605
return 0
606
if other < self.values:
607
return 1
608
else:
609
return -1
610
611
The resulting type object compares equal to all values
612
passed to the constructor.
613
614
* Here is a snippet of Python code that implements the exception
615
hierarchy defined above:
616
617
import exceptions
618
619
class Error(exceptions.StandardError):
620
pass
621
622
class Warning(exceptions.StandardError):
623
pass
624
625
class InterfaceError(Error):
626
pass
627
628
class DatabaseError(Error):
629
pass
630
631
class InternalError(DatabaseError):
632
pass
633
634
class OperationalError(DatabaseError):
635
pass
636
637
class ProgrammingError(DatabaseError):
638
pass
639
640
class IntegrityError(DatabaseError):
641
pass
642
643
class DataError(DatabaseError):
644
pass
645
646
class NotSupportedError(DatabaseError):
647
pass
648
649
In C you can use the PyErr_NewException(fullname,
650
base, NULL) API to create the exception objects.
651
652
653
Optional DB API Extensions
654
655
During the lifetime of DB API 2.0, module authors have often
656
extended their implementations beyond what is required by this DB
657
API specification. To enhance compatibility and to provide a clean
658
upgrade path to possible future versions of the specification,
659
this section defines a set of common extensions to the core DB API
660
2.0 specification.
661
662
As with all DB API optional features, the database module authors
663
are free to not implement these additional attributes and methods
664
(using them will then result in an AttributeError) or to raise a
665
NotSupportedError in case the availability can only be checked at
666
run-time.
667
668
It has been proposed to make usage of these extensions optionally
669
visible to the programmer by issuing Python warnings through the
670
Python warning framework. To make this feature useful, the warning
671
messages must be standardized in order to be able to mask them. These
672
standard messages are referred to below as "Warning Message".
673
674
Cursor Attribute .rownumber
675
676
This read-only attribute should provide the current 0-based
677
index of the cursor in the result set or None if the index cannot
678
be determined.
679
680
The index can be seen as index of the cursor in a sequence (the
681
result set). The next fetch operation will fetch the row
682
indexed by .rownumber in that sequence.
683
684
Warning Message: "DB-API extension cursor.rownumber used"
685
686
Connection Attributes .Error, .ProgrammingError, etc.
687
688
All exception classes defined by the DB API standard should be
689
exposed on the Connection objects are attributes (in addition
690
to being available at module scope).
691
692
These attributes simplify error handling in multi-connection
693
environments.
694
695
Warning Message: "DB-API extension connection.<exception> used"
696
697
Cursor Attributes .connection
698
699
This read-only attribute return a reference to the Connection
700
object on which the cursor was created.
701
702
The attribute simplifies writing polymorph code in
703
multi-connection environments.
704
705
Warning Message: "DB-API extension cursor.connection used"
706
707
Cursor Method .scroll(value[,mode='relative'])
708
709
Scroll the cursor in the result set to a new position according
710
to mode.
711
712
If mode is 'relative' (default), value is taken as offset to
713
the current position in the result set, if set to 'absolute',
714
value states an absolute target position.
715
716
An IndexError should be raised in case a scroll operation would
717
leave the result set. In this case, the cursor position is left
718
undefined (ideal would be to not move the cursor at all).
719
720
Note: This method should use native scrollable cursors, if
721
available , or revert to an emulation for forward-only
722
scrollable cursors. The method may raise NotSupportedErrors to
723
signal that a specific operation is not supported by the
724
database (e.g. backward scrolling).
725
726
Warning Message: "DB-API extension cursor.scroll() used"
727
728
Cursor Attribute .messages
729
730
This is a Python list object to which the interface appends
731
tuples (exception class, exception value) for all messages
732
which the interfaces receives from the underlying database for
733
this cursor.
734
735
The list is cleared by all standard cursor methods calls (prior
736
to executing the call) except for the .fetchXXX() calls
737
automatically to avoid excessive memory usage and can also be
738
cleared by executing "del cursor.messages[:]".
739
740
All error and warning messages generated by the database are
741
placed into this list, so checking the list allows the user to
742
verify correct operation of the method calls.
743
744
The aim of this attribute is to eliminate the need for a
745
Warning exception which often causes problems (some warnings
746
really only have informational character).
747
748
Warning Message: "DB-API extension cursor.messages used"
749
750
Connection Attribute .messages
751
752
Same as cursor.messages except that the messages in the list
753
are connection oriented.
754
755
The list is cleared automatically by all standard connection
756
methods calls (prior to executing the call) to avoid excessive
757
memory usage and can also be cleared by executing "del
758
connection.messages[:]".
759
760
Warning Message: "DB-API extension connection.messages used"
761
762
Cursor Method .next()
763
764
Return the next row from the currently executing SQL statement
765
using the same semantics as .fetchone(). A StopIteration
766
exception is raised when the result set is exhausted for Python
767
versions 2.2 and later. Previous versions don't have the
768
StopIteration exception and so the method should raise an
769
IndexError instead.
770
771
Warning Message: "DB-API extension cursor.next() used"
772
773
Cursor Method .__iter__()
774
775
Return self to make cursors compatible to the iteration protocol.
776
777
Warning Message: "DB-API extension cursor.__iter__() used"
778
779
Cursor Attribute .lastrowid
780
781
This read-only attribute provides the rowid of the last
782
modified row (most databases return a rowid only when a single
783
INSERT operation is performed). If the operation does not set
784
a rowid or if the database does not support rowids, this
785
attribute should be set to None.
786
787
The semantics of .lastrowid are undefined in case the last
788
executed statement modified more than one row, e.g. when
789
using INSERT with .executemany().
790
791
Warning Message: "DB-API extension cursor.lastrowid used"
792
793
794
Optional Error Handling Extension
795
796
The core DB API specification only introduces a set of exceptions
797
which can be raised to report errors to the user. In some cases,
798
exceptions may be too disruptive for the flow of a program or even
799
render execution impossible.
800
801
For these cases and in order to simplify error handling when
802
dealing with databases, database module authors may choose to
803
implement user defineable error handlers. This section describes a
804
standard way of defining these error handlers.
805
806
Cursor/Connection Attribute .errorhandler
807
808
Read/write attribute which references an error handler to call
809
in case an error condition is met.
810
811
The handler must be a Python callable taking the following
812
arguments: errorhandler(connection, cursor, errorclass,
813
errorvalue) where connection is a reference to the connection
814
on which the cursor operates, cursor a reference to the cursor
815
(or None in case the error does not apply to a cursor),
816
errorclass is an error class which to instantiate using
817
errorvalue as construction argument.
818
819
The standard error handler should add the error information to
820
the appropriate .messages attribute (connection.messages or
821
cursor.messages) and raise the exception defined by the given
822
errorclass and errorvalue parameters.
823
824
If no errorhandler is set (the attribute is None), the standard
825
error handling scheme as outlined above, should be applied.
826
827
Warning Message: "DB-API extension .errorhandler used"
828
829
Cursors should inherit the .errorhandler setting from their
830
connection objects at cursor creation time.
831
832
833
Frequently Asked Questions
834
835
The database SIG often sees reoccurring questions about the DB API
836
specification. This section covers some of the issues people
837
sometimes have with the specification.
838
839
Question:
840
841
How can I construct a dictionary out of the tuples returned by
842
.fetchxxx():
843
844
Answer:
845
846
There are several existing tools available which provide
847
helpers for this task. Most of them use the approach of using
848
the column names defined in the cursor attribute .description
849
as basis for the keys in the row dictionary.
850
851
Note that the reason for not extending the DB API specification
852
to also support dictionary return values for the .fetchxxx()
853
methods is that this approach has several drawbacks:
854
855
* Some databases don't support case-sensitive column names or
856
auto-convert them to all lowercase or all uppercase
857
characters.
858
859
* Columns in the result set which are generated by the query
860
(e.g. using SQL functions) don't map to table column names
861
and databases usually generate names for these columns in a
862
very database specific way.
863
864
As a result, accessing the columns through dictionary keys
865
varies between databases and makes writing portable code
866
impossible.
867
868
869
Major Changes from Version 1.0 to Version 2.0
870
871
The Python Database API 2.0 introduces a few major changes
872
compared to the 1.0 version. Because some of these changes will
873
cause existing DB API 1.0 based scripts to break, the major
874
version number was adjusted to reflect this change.
875
876
These are the most important changes from 1.0 to 2.0:
877
878
* The need for a separate dbi module was dropped and the
879
functionality merged into the module interface itself.
880
881
* New constructors and Type Objects were added for date/time
882
values, the RAW Type Object was renamed to BINARY. The
883
resulting set should cover all basic data types commonly
884
found in modern SQL databases.
885
886
* New constants (apilevel, threadlevel, paramstyle) and
887
methods (executemany, nextset) were added to provide better
888
database bindings.
889
890
* The semantics of .callproc() needed to call stored
891
procedures are now clearly defined.
892
893
* The definition of the .execute() return value changed.
894
Previously, the return value was based on the SQL statement
895
type (which was hard to implement right) -- it is undefined
896
now; use the more flexible .rowcount attribute
897
instead. Modules are free to return the old style return
898
values, but these are no longer mandated by the
899
specification and should be considered database interface
900
dependent.
901
902
* Class based exceptions were incorporated into the
903
specification. Module implementors are free to extend the
904
exception layout defined in this specification by
905
subclassing the defined exception classes.
906
907
Post-publishing additions to the DB API 2.0 specification:
908
909
* Additional optional DB API extensions to the set of
910
core functionality were specified.
911
912
913
Open Issues
914
915
Although the version 2.0 specification clarifies a lot of
916
questions that were left open in the 1.0 version, there are still
917
some remaining issues which should be addressed in future
918
versions:
919
920
* Define a useful return value for .nextset() for the case where
921
a new result set is available.
922
923
* Create a fixed point numeric type for use as loss-less
924
monetary and decimal interchange format.
925
926
927
Footnotes
928
929
[1] As a guideline the connection constructor parameters should be
930
implemented as keyword parameters for more intuitive use and
931
follow this order of parameters:
932
933
dsn Data source name as string
934
user User name as string (optional)
935
password Password as string (optional)
936
host Hostname (optional)
937
database Database name (optional)
938
939
E.g. a connect could look like this:
940
941
connect(dsn='myhost:MYDB',user='guido',password='234$')
942
943
[2] Module implementors should prefer 'numeric', 'named' or
944
'pyformat' over the other formats because these offer more
945
clarity and flexibility.
946
947
[3] If the database does not support the functionality required
948
by the method, the interface should throw an exception in
949
case the method is used.
950
951
The preferred approach is to not implement the method and
952
thus have Python generate an AttributeError in
953
case the method is requested. This allows the programmer to
954
check for database capabilities using the standard
955
hasattr() function.
956
957
For some dynamically configured interfaces it may not be
958
appropriate to require dynamically making the method
959
available. These interfaces should then raise a
960
NotSupportedError to indicate the non-ability
961
to perform the roll back when the method is invoked.
962
963
[4] a database interface may choose to support named cursors by
964
allowing a string argument to the method. This feature is
965
not part of the specification, since it complicates
966
semantics of the .fetchXXX() methods.
967
968
[5] The module will use the __getitem__ method of the parameters
969
object to map either positions (integers) or names (strings)
970
to parameter values. This allows for both sequences and
971
mappings to be used as input.
972
973
The term "bound" refers to the process of binding an input
974
value to a database execution buffer. In practical terms,
975
this means that the input value is directly used as a value
976
in the operation. The client should not be required to
977
"escape" the value so that it can be used -- the value
978
should be equal to the actual database value.
979
980
[6] Note that the interface may implement row fetching using
981
arrays and other optimizations. It is not
982
guaranteed that a call to this method will only move the
983
associated cursor forward by one row.
984
985
[7] The rowcount attribute may be coded in a way that updates
986
its value dynamically. This can be useful for databases that
987
return usable rowcount values only after the first call to
988
a .fetchXXX() method.
989
990
Acknowledgements
991
992
Many thanks go to Andrew Kuchling who converted the Python
993
Database API Specification 2.0 from the original HTML format into
994
the PEP format.
995
996
Copyright
997
998
This document has been placed in the Public Domain.
999
1000
1001
1002
Local Variables:
1003
mode: indented-text
1004
indent-tabs-mode: nil
1005
End:
Loggerhead is a web-based interface for Breezy
Version: 2.0.1