1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
|
<!-- X-URL: http://www.python.org/sigs/db-sig/DatabaseAPI.html -->
<BASE HREF="http://www.python.org/sigs/db-sig/DatabaseAPI.html">
<HTML>
<!-- THIS PAGE IS AUTOMATICALLY GENERATED. DO NOT EDIT. -->
<!--User-specified headers:
Title: Python Database API
Wide-page:
Meta:
Banner:
Banner-alt: Python
Author: db-sig-owner<BR>@python.org
-->
<HEAD>
<TITLE>Python Database API</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000"
LINK="#0000BB" VLINK="#551A8B" ALINK="#FF0000">
<TABLE WIDTH="100%" BORDER=0 CELLSPACING=0 CELLPADDING=0>
<TR>
<TD WIDTH=150 VALIGN=CENTER BGCOLOR="#99CCFF">
<CENTER><A HREF="../../"
><IMG BORDER=0 SRC="../../pics/PyBanner014.gif"
></A></CENTER>
</TD>
<TD WIDTH=15 BGCOLOR="#99CCFF"> </TD><!--spacer-->
<TD WIDTH="90%" BGCOLOR="#99CCFF">
<TABLE WIDTH="100%" BORDER=0 CELLSPACING=0 CELLPADDING=0
COLS=4 ROWS=2 BGCOLOR="#99CCFF">
<TR>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../.././">Home</A></TD>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../../search/">Search</A></TD>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../../download/">Download</A></TD>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../../doc/">Documentation</A></TD>
</TR>
<TR>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../../Help.html">Help</A></TD>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../../psa/">Community</A></TD>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../../sigs/">SIGs</A></TD>
<TD WIDTH="25%" BGCOLOR="#99CCFF"><A HREF="../../download/Contributed.html">Modules</A></TD>
</TR>
</TABLE>
</TD>
</TR>
<TR>
<TD WIDTH=150 VALIGN=TOP BGCOLOR="#99CCFF">
<TABLE WIDTH="100%" BORDER=0 CELLSPACING=0 CELLPADDING=0
BGCOLOR="#99CCFF">
<!--User insert Other-links starts here-->
<TR><TD BGCOLOR="#003366"><B><FONT COLOR="#FFFFFF">Database SIG</FONT></B></TD></TR>
<TR><TD BGCOLOR="#99CCFF"><A HREF="./">Index</A>
<TR><TD BGCOLOR="#99CCFF"><A HREF="status.html">Status</A>
<TR><TD BGCOLOR="#99CCFF"><b>API specification</b>
<TR><TD BGCOLOR="#99CCFF"><A HREF="http://www.python.org/mailman/listinfo/db-sig"
>Subscribing</A>
<TR><TD BGCOLOR="#99CCFF"><A HREF="http://www.python.org/pipermail/db-sig.html"
>Archives</A>
<TR><TD BGCOLOR="#99CCFF"><A HREF="../">About Python SIGs</A>
<TR><TD BGCOLOR="#99CCFF">
<!--User insert Other-links ends here-->
<TR>
<TD BGCOLOR="#003366">
<FONT COLOR="#FFFFFF"><b>Email us</b></FONT>
</TD>
</TR>
<TR>
<TD BGCOLOR="#99CCFF">
<A HREF="mailto:db-sig-owner@python.org">db-sig-owner<BR>@python.org</A>
<P>
<CENTER>
<A HREF="../../psa/Logo.html"
><IMG SRC=../../pics/PythonPoweredSmall.gif
WIDTH=55 HEIGHT=22 BORDER=0></A>
</CENTER>
</TD>
</TR>
</TABLE>
</TD>
<TD WIDTH=15> </TD><!--spacer-->
<TD VALIGN=TOP WIDTH="90%">
<BR>
<!--User part of page starts here-->
<H3>Python Database API</H3>
This API has been defined to encourage similarity between the Python
modules that are used to access databases. By doing this, we hope to
achieve a consistency leading to more easily understood modules,
code that is generally more portable across databases, and a broader
reach of database connectivity from Python.
<P>
This interface specification consists of several items:
<P>
<UL>
<LI> <A HREF="#module">Module Interface</A>
<LI> <A HREF="#connection">Connection Objects</A>
<LI> <A HREF="#cursor">Cursor Objects</A>
<LI> <A HREF="#dbi">DBI Helper Objects</A>
</UL>
<P>
Comments and questions about this specification may be directed to
the <A HREF="mailto:db-sig@python.org">SIG on Tabular Databases in Python</A>.
<P>
See the main <A HREF="index.html">index</A> for information about modules
that use this interface.
<P>
<HR>
<P>
This specification document was last updated on: April 9, 1996. It
will be known as Version 1.0 of this specification.
<P>
<I>Note: The ODBC module implements a newer version of this specification.
Some additional error types were added to DBI. A spec update is due.</I>
<P>
<HR>
<A NAME="module"><H3>Module Interface</H3></A>
The database interface modules should typically be named with something
terminated by <STRONG>db</STRONG>. Existing examples are:
<CODE>oracledb</CODE>, <CODE>informixdb</CODE>, and <CODE>pg95db</CODE>.
These modules should export several names:
<P>
<DL>
<DT> <I>modulename</I><VAR>(connection_string)</VAR>
<DD> Constructor for creating a connection to the database. Returns a
<I>Connection Object</I>.
<P>
<DT> <STRONG>error</STRONG>
<DD> Exception raise for errors from the database module.
</DL>
<P>
<A NAME="connection"><H3>Connection Objects</H3></A>
Connections Objects should respond to the following methods:
<P>
<DL>
<DT> <STRONG>close</STRONG><VAR>()</VAR>
<DD> Close the connection now (rather than whenever __del__ is called).
The connection will be unusable from this point forward; an exception will
be raised if any operation is attempted with the connection.
<P>
<DT> <STRONG>commit</STRONG><VAR>()</VAR>
<DD> Commit any pending transaction to the database.
<P>
<DT> <STRONG>rollback</STRONG><VAR>()</VAR>
<DD> Roll the database back to the start of any pending transaction.
<P>
<DT> <STRONG>cursor</STRONG><VAR>()</VAR>
<DD> Return a new <I>Cursor Object</I>. An exception may be thrown if the
database does not support a cursor concept.
<P>
<DT> <STRONG>callproc</STRONG><VAR>([params])</VAR>
<DD>
<I>Note: this method is not well-defined yet.</I><BR>
Call a stored database procedure with the given (optional) parameters.
Returns the result of the stored procedure.<BR>
<P>
<DT> <I>all Cursor Object attributes and methods</I>
<DD> For databases that do not have cursors and for simple applications that
do not require the complexity of a cursor, a Connection Object should
respond to each of the attributes and methods of the Cursor Object.
Databases that have cursor can implement this by using an implicit, internal
cursor.
<P>
</DL>
<P>
<A NAME="cursor"><H3>Cursor Objects</H3></A>
These objects represent a database cursor, which is used to manage the context
of a fetch operation.
<P>
Cursor Objects should respond to the following methods and attributes:
<P>
<DL>
<DT> <STRONG>arraysize</STRONG>
<DD> This read/write attribute specifies the
number of rows to fetch at a time with <CODE>fetchmany()</CODE>. This value
is also used
when inserting multiple rows at a time (passing a tuple/list of tuples/lists
as the params value to <CODE>execute()</CODE>). This attribute will
default to a single row.
<P>
Note that the arraysize is optional and is merely provided for higher
performance database interactions. Implementations should observe it
with respect to the <CODE>fetchmany()</CODE> method, but are free to
interact with
the database a single row at a time.
<P>
<DT> <STRONG>description</STRONG>
<DD> This read-only attribute is a
tuple of 7-tuples. Each 7-tuple contains information describing each
result column: <CODE>(name, type_code, display_size, internal_size,
precision, scale, null_ok)</CODE>. This attribute will be
<CODE>None</CODE> for operations that do not
return rows or if the cursor has not had an operation invoked via the
<CODE>execute()</CODE> method yet.
<P>
The <CODE>type_code</CODE> is one of the <STRONG>dbi</STRONG> values
specified in the section below.
<P>
<I>Note: this is a bit in flux. Generally, the first two items of
the 7-tuple will always be present; the others may be database specific.</I>
<P>
<DT> <STRONG>close</STRONG><VAR>()</VAR>
<DD> Close the cursor now (rather than whenever __del__ is called).
The cursor will be unusable from this point forward; an exception will
be raised if any operation is attempted with the cursor.
<P>
<DT> <STRONG>execute</STRONG><VAR>(operation [,params])</VAR>
<DD> Execute (prepare) a database operation (query or command). Parameters
may be provided (as a sequence (e.g. tuple/list)) and will be bound to
variables in the
operation. Variables are specified in a database-specific notation that
is based on the index in the parameter tuple (position-based rather than
name-based).
<P>
The parameters may also be specified as a sequence of sequences
(e.g. a list of tuples) to insert multiple rows in a single operation.
<P>
A reference to the operation will be retained by the cursor. If the same
operation object is passed in again, then the cursor can optimize its
behavior. This is most effective for algorithms where the same operation
is used, but different parameters are bound to it (many times).
<P>
For maximum efficiency when reusing an operation, it is best to use the
setinputsizes() method to specify the parameter types and sizes ahead of
time. It is legal for a parameter to not match the predefined information;
the implementation should compensate, possibly with a loss of efficiency.
<P>
Using SQL terminology, these are the possible result values from the
execute() method:
<UL>
<LI> If the statement is DDL (e.g. CREATE TABLE), then 1 is returned.
<LI> If the statement is DML (e.g. UPDATE or INSERT), then the
number of rows affected is returned (0 or a positive integer).
<LI> If the statement is DQL (e.g. SELECT), None is returned, indicating
that the statement is not really complete until you use one of the
<STRONG>fetch</STRONG> methods.
</UL>
<P>
<DT> <STRONG>fetchone</STRONG><VAR>()</VAR>
<DD> Fetch the next row of a query result, returning a single tuple.
<P>
<DT> <STRONG>fetchmany</STRONG><VAR>([size])</VAR>
<DD>Fetch the next set of rows of a query result, returning as a list of
tuples. An empty list is returned when no more rows are available. The
number of rows to fetch is specified by the parameter. If it is
<CODE>None</CODE>,
then the cursor's arraysize determines the number of rows to be fetched.
<P>
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 <CODE>fetchmany()</CODE> call to the next.
<P>
<DT> <STRONG>fetchall</STRONG><VAR>()</VAR>
<DD>Fetch all rows of a query result, returning as a list of tuples. Note
that the cursor's arraysize attribute can affect the performance of this
operation.
<P>
<DT> <STRONG>setinputsizes</STRONG><VAR>(sizes)</VAR>
<DD>
<I>Note: this method is not well-defined yet.</I><BR>
This can be used before a call to <CODE>execute()</CODE> to predefine
memory areas for
the operation's parameters. sizes is specified as a tuple -- 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
<CODE>None</CODE>, then no predefined memory area will be reserved for
that column (this is useful to avoid predefined areas for large inputs).
<P>
This method would be used before the <CODE>execute()</CODE> method
is invoked.
<P>
Note that this method is optional and is merely provided for higher
performance
database interaction. Implementations are free to do nothing and users
are free to not use it.
<P>
<DT> <STRONG>setoutputsize</STRONG><VAR>(size [,col])</VAR>
<DD>
<I>Note: this method is not well-defined yet.</I><BR>
Set a column buffer size for fetches of large columns (e.g. LONG).
The column
is specified as an index into the result tuple. Using a column of
<CODE>None</CODE> will set the default size for all large columns in
the cursor.
<P>
This method would be used before the <CODE>execute()</CODE> method
is invoked.
<P>
Note that this method is optional and is merely provided for higher
performance
database interaction. Implementations are free to do nothing and users
are free to not use it.
<P>
</DL>
<A NAME="dbi"><H3>DBI Helper Objects</H3></A>
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
<CODE>execute()</CODE> 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.
<P>
To overcome this problem, the <STRONG>dbi</STRONG> module was created.
This module specifies some basic database interface types for working
with databases. There are two classes: <STRONG>dbiDate</STRONG> and
<STRONG>dbiRaw</STRONG>. These are simple container classes that
wrap up a value. When passed to the database modules, the module can then
detect that the input parameter is intended as a DATE or a RAW. For
symmetry, the database modules will return DATE and RAW columns as
instances of these classes.
<P>
A Cursor Object's <CODE>description</CODE> attribute returns information
about each of the result columns of a query. The <CODE>type_code</CODE>
is defined to be one of five types exported by this module:
<STRONG>STRING</STRONG>, <STRONG>RAW</STRONG>, <STRONG>NUMBER</STRONG>,
<STRONG>DATE</STRONG>, or <STRONG>ROWID</STRONG>.
<P>
The module exports the following names:
<P>
<DL>
<DT> <STRONG>dbiDate</STRONG><VAR>(value)</VAR>
<DD> This function constructs a <CODE>dbiDate</CODE> instance that holds
a date value. The value should be specified as an integer number of
seconds since the "epoch" (e.g. time.time()).
<P>
<DT> <STRONG>dbiRaw</STRONG><VAR>(value)</VAR>
<DD> This function constructs a <CODE>dbiRaw</CODE> instance that holds
a raw (binary) value. The value should be specified as a Python string.
<P>
<DT> <STRONG>STRING</STRONG>
<DD> This object is used to describe columns in a database that are
string-based (e.g. CHAR).
<P>
<DT> <STRONG>RAW</STRONG>
<DD> This object is used to describe (large) binary columns in a database
(e.g. LONG RAW, blobs).
<P>
<DT> <STRONG>NUMBER</STRONG>
<DD> This object is used to describe numeric columns in a database.
<P>
<DT> <STRONG>DATE</STRONG>
<DD> This object is used to describe date columns in a database.
<P>
<DT> <STRONG>ROWID</STRONG>
<DD> This object is used to describe the "Row ID" column in a database.
<P>
</DL>
<P>
<!--User part of page ends here-->
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
|
