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
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<HTML>
<HEAD>
<META name="generator" content="HyperText package (Python)">
<STYLE type="text/css">
BODY { background-color: white; color: black }
CAPTION { font: italic }
.C { font-family: typewriter }
.Py { font-family: typewriter; color: #004000 }</STYLE>
<TITLE>MySQLdb: a Python interface for MySQL</TITLE>
<META name="keywords" content="MySQL,Python"></HEAD>
<BODY>
<H1>MySQLdb: a Python interface for MySQL</H1>
<P>Brought to you by <A href="mailto:adustman@comstar.net">Andy Dustman</A> and <A href="http://www.comstar.net/">Comstar.Net, Inc.</A>.</P>
<P>Please read the <A href="license.py">licensing agreement</A> with it's lack of warranty
statement.</P>
<P><A href=".">Download</A> the friggin' thing.</P>
<H2>Overview</H2>
<P>MySQLdb is an interface to the popular <A href="http://www.mysql.net/mirrors.html">MySQL</A> database server for <A href="http://www.python.org/">Python</A>. The design goals are:</P>
<UL>
<LI>Compliance with Python <A href="http://www.python.org/topics/database/DatabaseAPI-2.0.html">database API version 2.0</A></LI>
<LI>Thread-safety</LI>
<LI>Thread-friendliness (threads will not block each other)</LI>
<LI>Compatibility with MySQL 3.22</LI></UL>
<P>This module should be mostly compatible with an older interface
written by Joe Skinner and others. However, the older version
is a) not thread-friendly, b) written for MySQL 3.21, c)
apparently not actively maintained. No code from that version
is used in MySQLdb. MySQLdb is distributed free of charge
under a license derived from the Python license.</P>
<P>Notes: MySQL 3.22.11 is known NOT to work. Only versions
3.22.19 and up are known to work. If you have an older version
you should seriously consider upgrading for it's own sake.
Some older versions may work due to some recent patches.
It ought to work with 3.23 (currently under development) but
has not been tested.</P>
<P>This module requires Python 1.5.2. Earlier versions will not
work, because support for C long longs is required by MySQL.
Thanks to Nikolas Kauer for pointing this out.</P>
<P>This version has been tested against MySQL-3.22.25, which seems
to have a strange bug when handling TIME columns. For this
reason, there is presently no type converter for TIME columns
(the value is returned as a string).</P>
<P>The type converter dictionary is no longer stored within the
<TT>_mysql</TT> module. See below for more details.</P>
<P>If you work out
an installation routine for Windows, please contact the author.</P>
<P>This module works better if you have the <A href="http://starship.skyport.net/~lemburg/mxDateTime.html">DateTime</A>
module, but will function without it.</P>
<P>The web page documentation may be slightly ahead of the
latest release and may reflect features of the next release.</P>
<H2>_mysql -- low-level interface</H2>
<P>If you want to write applications which are portable across
databases, avoid using this module directly. <TT>_mysql</TT> provides an
interface which mostly implements the MySQL C API. For more
information, see the MySQL documentation, section 18.
The documentation for this module is intentionally weak
because you probably should use the higher-level <TT>MySQLdb</TT> module. If you really need it, use the
standard MySQL docs and transliterate as necessary.</P>
<P>The C API has been wrapped in an object-oriented way. The
only MySQL data structures which are implemented are the
<TT>MYSQL</TT> (database connection handle) and <TT>MYSQL_RES</TT> (result handle) types. In general, any
function which takes <TT>MYSQL *mysql</TT> as an
argument is now a method of the connection object, and
any function which takes <TT>MYSQL_RES *result</TT>
as an argument is a method of the result object.
Functions requiring none of the MySQL data structures
are implemented as functions in the module. Functions
requiring one of the other MySQL data structures are
generally not implemented. Deprecated functions are not
implemented. In all cases, the <TT>mysql_</TT> prefix
is dropped from the name.</P>
<DIV align="center">
<TABLE border="1"><CAPTION>MySQL C API function mapping</CAPTION>
<TR>
<TH>C API</TH>
<TH>_mysql</TH></TR>
<TR>
<TD>
<P class="C">mysql_affected_rows()</P></TD>
<TD>
<P class="Py">conn.affected_rows()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_close()</P></TD>
<TD>
<P class="Py">conn.close()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_connect()</P></TD>
<TD>
<P class="Py">_mysql.connect()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_data_seek()</P></TD>
<TD>
<P class="Py">result.data_seek()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_debug()</P></TD>
<TD>
<P class="Py">_mysql.debug()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_dump_debug_info</P></TD>
<TD>
<P class="Py">conn.dump_debug_info()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_escape_string()</P></TD>
<TD>
<P class="Py">_mysql.escape_string()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_fetch_row()</P></TD>
<TD>
<P class="Py">
<DIV class="Py">result.fetch_row()
<BR>result.fetch_rows()
<BR>result.fetch_all_rows()</DIV></P></TD></TR>
<TR>
<TD>
<P class="C">mysql_get_client_info()</P></TD>
<TD>
<P class="Py">_mysql.get_client_info()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_get_host_info()</P></TD>
<TD>
<P class="Py">conn.get_host_info()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_get_proto_info()</P></TD>
<TD>
<P class="Py">conn.get_proto_info()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_get_server_info()</P></TD>
<TD>
<P class="Py">conn.get_server_info()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_info()</P></TD>
<TD>
<P class="Py">conn.info()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_insert_id()</P></TD>
<TD>
<P class="Py">conn.insert_id()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_list_dbs()</P></TD>
<TD>
<P class="Py">conn.list_dbs()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_list_fields()</P></TD>
<TD>
<P class="Py">conn.list_fields()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_list_processes()</P></TD>
<TD>
<P class="Py">conn.list_processes()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_list_tables()</P></TD>
<TD>
<P class="Py">conn.list_tables()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_num_fields()</P></TD>
<TD>
<P class="Py">result.num_fields()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_num_rows()</P></TD>
<TD>
<P class="Py">result.num_rows()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_ping()</P></TD>
<TD>
<P class="Py">conn.ping()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_query()</P></TD>
<TD>
<P class="Py">conn.query()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_real_connect()</P></TD>
<TD>
<P class="Py">_mysql.connect()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_real_query()</P></TD>
<TD>
<P class="Py">conn.query()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_row_seek()</P></TD>
<TD>
<P class="Py">result.row_seek()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_row_tell()</P></TD>
<TD>
<P class="Py">result.row_tell()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_select_db()</P></TD>
<TD>
<P class="Py">conn.select_db()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_stat()</P></TD>
<TD>
<P class="Py">conn.stat()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_store_result()</P></TD>
<TD>
<P class="Py">conn.store_result()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_thread_id()</P></TD>
<TD>
<P class="Py">conn.thread_id()</P></TD></TR>
<TR>
<TD>
<P class="C">mysql_use_result()</P></TD>
<TD>
<P class="Py">conn.use_result()</P></TD></TR>
<TR>
<TD>
<P class="C">CLIENT_*</P></TD>
<TD>
<P class="Py">_mysql.CLIENT.*</P></TD></TR>
<TR>
<TD>
<P class="C">CR_*</P></TD>
<TD>
<P class="Py">_mysql.CR.*</P></TD></TR>
<TR>
<TD>
<P class="C">ER_*</P></TD>
<TD>
<P class="Py">_mysql.ER.*</P></TD></TR>
<TR>
<TD>
<P class="C">FIELD_TYPE_*</P></TD>
<TD>
<P class="Py">_mysql.FIELD_TYPE.*</P></TD></TR>
<TR>
<TD>
<P class="C">FLAG_*</P></TD>
<TD>
<P class="Py">_mysql.FLAG.*</P></TD></TR></TABLE></DIV>
<H2>MySQLdb -- DB API interface</H2>
<P><TT>MySQLdb</TT> is a thin Python wrapper around <TT>_mysql</TT> which makes it compatible with the Python
DB API interface (version 2). In reality, a fair amount
of the code which implements the API is in <TT>_mysql</TT> for the sake of efficiency.</P>
<P><A href="http://www.python.org/topics/database/DatabaseAPI-2.0.html">The DB API specification</A> should be your primary guide for
using this module. Only deviations from the spec and other
database-dependent things will be documented here.
Note that all symbols from <TT>_mysql</TT> are imported
into this module. Mostly these are the required exceptions
the constant classes, and a very few functions.</P>
<DL>
<DT><B>connect(parameters...)</B></DT>
<DD>
<P>Constructor for creating a connection to the database.
Returns a Connection Object. Parameters are the same as
for the MySQL C API. Note that all parameters must be
specified as keyword arguments! The default value for
each parameter is NULL or zero, as appropriate. Consult
the MySQL documentation for more details. The important
parameters are:</P>
<P>
<DL>
<DT><B>host</B></DT>
<DD>name of host to connect to</DD>
<DT><B>user</B></DT>
<DD>user to authenticate as</DD>
<DT><B>passwd</B></DT>
<DD>password to authenticate with</DD>
<DT><B>db</B></DT>
<DD>database to use</DD>
<DT><B>conv</B></DT>
<DD>type conversion dictionary</DD></DL></P></DD>
<DT><B>apilevel</B></DT>
<DD>
<P>String constant stating the supported DB API level. '2.0'</P></DD>
<DT><B>threadsafety</B></DT>
<DD>
<P>Integer constant stating the level of thread safety
the interface supports. Set to 1, which means:
Threads may share the module, but not connections.
This is the practice recommended by the MySQL
documentation. However, it should be safe to share
a connection between two threads provided only one
thread at a time uses it (i.e. a mutex is employed).
Note that this is only safe if the threads are using.
<TT>mysql_store_result()</TT> as opposed to <TT>mysql_use_result()</TT>. The latter is not recommended
for threaded applications. See the MySQL documentation
for more details.</P></DD>
<DT><B>paramstyle</B></DT>
<DD>
<P>String constant stating the type of parameter marker
formatting expected by the interface. Set to
'format' = ANSI C printf format codes,
e.g. '...WHERE name=%s'. If a mapping object is used
for <TT>conn.execute()</TT>, then the interface
actually uses 'pyformat' = Python extended format codes,
e.g. '...WHERE name=%(name)s'. However, the API does not
presently allow the specification of more than one style
in <TT>paramstyle</TT>.</P>
<P>Compatibility note: The older MySQLmodule uses a similar
parameter scheme, but requires that quotes be placed
around format strings which will contain strings, dates,
and similar character data. This is not necessary for
MySQLdb. It is recommended that <TT>%s</TT> (and not
<TT>'%s'</TT>) be used for all
parameters, regardless of type. The interface performs
all necessary quoting.</P></DD>
<DT><B>type_conv</B></DT>
<DD>
<P>A dictionary mapping MySQL types (from <TT>FIELD_TYPE.*</TT>) to callable Python objects
(usually functions) which convert from a string to
the desired type. This is initialized with
reasonable defaults for most types. When creating a
Connection object, you can pass your own type converter
dictionary as a keyword parameter. Otherwise, it uses a
copy of <TT>type_conv</TT> which is safe
to modify on a per-connection basis. The dictionary
includes some of
the factory functions from the
<A href="http://starship.skyport.net/~lemburg/mxDateTime.html">DateTime</A> module, if it is available.
Several non-standard types (SET, ENUM) are
returned as strings, which is how MySQL returns all
columns. Note: <TT>TIME</TT> columns are returned as
strings presently. This should be a temporary condition.</P></DD></DL>
<H3>Connection Objects</H3>
<DL>
<DT><B>commit()</B></DT>
<DD>
<P>MySQL does not support transactions, so this method
successfully does nothing.</P></DD>
<DT><B>rollback()</B></DT>
<DD>
<P>MySQL does not support transactions, so this method
is not defined. <EM>Note that the older MySQLmodule
does define this method, which sucessfully does
nothing. This is dangerous behavior, as a succesful
rollback indicates that the current transaction was
backed out, which is not true, and fails to notify
the programmer that the database now needs to be
cleaned up by other means.</EM></P></DD>
<DT><B>cursor(parameters...)</B></DT>
<DD>
<P>MySQL does not support cursors; however, cursors are
fairly easily emulated. Any positional or keyword
arguments are passed to the cursor constructor.</P></DD>
<DT><B>db</B></DT>
<DD>
<P>The <TT>_mysql</TT> connection object. This may be
used in case it is necessary to employ some
MySQL-specific functions.</P></DD>
<DT><B>CursorClass</B></DT>
<DD>
<P>The class used to create a new cursor with
<TT>conn.cursor()</TT>. If you subclass the Connection
object, you will probably want to change this.</P></DD></DL>
<H3>Cursor Objects</H3>
<P>Cursor objects support some parameters when created, usually
passed by <TT>conn.cursor()</TT>. They are also attributes
of the cursor, but it is probably best to not mess with them.</P>
<DL>
<DT><B>name=''</B></DT>
<DD>Names the cursor, which is pretty useless.</DD>
<DT><B>use=0</B></DT>
<DD>Cursor objects normally employ <TT>mysql_store_result()</TT>. Setting this value to a
true value will cause it to use
<TT>mysql_use_result()</TT> instead. See the MySQL
documentation for more information.</DD>
<DT><B>warnings=1</B></DT>
<DD>If true, detects warnings and raises the <TT>Warning</TT> exception.</DD></DL>
<P>While it is possible to create Cursor objects with the
class constructor, this is not recommended, so they are
hidden as _Cursor objects.</P>
<DL>
<DT><B>callproc()</B></DT>
<DD>
<P>Not implemented.</P></DD>
<DT><B>nextset()</B></DT>
<DD>
<P>Not implemented.</P></DD>
<DT><B>setinputsizes()</B></DT>
<DD>
<P>Does nothing, successfully.</P></DD>
<DT><B>setoutputsizes()</B></DT>
<DD>
<P>Does nothing, successfully.</P></DD>
<DT><B>execute(query[,parameters])</B></DT>
<DT><B>executemany(query,[,parameters])</B></DT>
<DD>
<P>These methods work as described in the API. However,
it should be noted that the interface relies on the
<TT>__str__</TT> method of each parameter. That is,
each parameter is converted to a string when passed
to MySQL. This presents a problem for the various
date and time columns: <TT>__str__</TT> for
DateTime objects includes fractional seconds, which
MySQL (up to 3.22.20a, at least), considers illegal
input, and so zeros the field.</P></DD>
<DT><B>format_DATE(d)</B></DT>
<DT><B>format_TIME(d)</B></DT>
<DT><B>format_TIMESTAMP(d)</B></DT>
<DD>
<P>These functions all take a DateTime object as input and
return an appropriately formatted string. They are
intended for use with the <TT>executeXXX()</TT> methods.</P></DD></DL>
<H2>General Design Notes</H2>
<P>In general, it is probably wise to not directly interact
with the DB API except for small applicatons.
Databases, even SQL databases, vary widely
in capabilities and may have non-standard features. The DB API
does a good job of providing a reasonably portable interface
but some methods are non-portable. Specifically, the parameters
accepted by <TT>connect()</TT> are completely
implementation-dependent.</P>
<P>If you
believe your application may need to run on several
different databases, the author recommends the following
approach, based on personal experience: Write a simplified
API for your application which implements the specific
queries and operations your application needs to perform.
Implement this API
as a base class which should be have few database
dependencies, and then derive a subclass from this
which implements the necessary dependencies. In this way,
porting your application to a new database should be a
relatively simple matter of creating a new subclass,
assuming the new database is reasonably standard.</P>
<H2>License</H2>
<P><PRE>
Copyright 1999 by Comstar.net, Inc., Atlanta, GA, US.
All Rights Reserved
Permission to use, copy, modify, and distribute this software and its
documentation for any purpose and without fee is hereby granted,
provided that the above copyright notice appear in all copies and that
both that copyright notice and this permission notice appear in
supporting documentation, and that the name of Comstar.net, Inc.
or COMSTAR not be used in advertising or publicity pertaining to
distribution of the software without specific, written prior permission.
COMSTAR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE,
INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO
EVENT SHALL COMSTAR BE LIABLE FOR ANY SPECIAL, INDIRECT OR
CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
PERFORMANCE OF THIS SOFTWARE.
</PRE></P>
<P>$Id: MySQL_doc.py,v 1.5 1999/07/20 04:04:20 adustman Exp $</P></BODY></HTML>
|
