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
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<META NAME="GENERATOR" CONTENT="SGML-Tools 1.0.9">
<TITLE>MySQLdb: a Python interface for MySQL: Using and extending</TITLE>
<LINK HREF="MySQLdb-3.html" REL=previous>
<LINK HREF="MySQLdb.html#toc4" REL=contents>
</HEAD>
<BODY>
Next
<A HREF="MySQLdb-3.html">Previous</A>
<A HREF="MySQLdb.html#toc4">Contents</A>
<HR>
<H2><A NAME="usage"></A> <A NAME="s4">4. Using and extending</A></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 <CODE>
<A HREF="MySQLdb-3.html#connect()">connect()</A>
</CODE> are completely implementation-dependent.
<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>For an example of this, see the author's
<A HREF="http://dustman.net/andy/python">SQLDict module</A>, which allows standard queries to be
defined and accessed using an object which looks like a
dictionary, and reads/writes user-defined objects.
<P>Because MySQLdb's Connection and Cursor objects are written in Python,
you can easily derive your own subclasses. There are several Cursor
classes in MySQLdb.cursors:
<P>
<DL>
<DT><B>BaseCursor</B><DD><P>The base class for Cursor objects.
This does not raise Warnings.
<P>
<DT><B>CursorWarningMixIn</B><DD><P>Causes the Warning exception to be raised
on queries which produce warnings.
<P>
<DT><B>CursorStoreResultMixIn</B><DD><P>Causes the Cursor to use the
<CODE>mysql_store_result()</CODE> function to get the query result. The
entire result set is stored on the client side.
<P>
<DT><B>
<A NAME="SSCursor"></A> CursorUseResultMixIn</B><DD><P>Causes the cursor to use the
<CODE>mysql_use_result()</CODE> function to get the query result. The
result set is stored on the server side and is transferred row by row
using fetch operations.
<P>
<DT><B>CursorTupleRowsMixIn</B><DD><P>Causes the cursor to return rows
as a tuple of the column values.
<P>
<DT><B>CursorDictRowsMixIn</B><DD><P>Causes the cursor to return rows
as a dictionary, where the keys are column names and the values
are column values. Note that if the column names are not unique,
i.e., you are selecting from two tables that share column names,
some of them will be rewritten as <EM>table.column</EM>.
This can be avoided by using
the SQL <CODE>AS</CODE> keyword. (This is yet-another reason not to use
<CODE>*</CODE> in SQL queries, particularly where <CODE>JOIN</CODE> is involved.
<P>
<DT><B>Cursor</B><DD><P>The default cursor class. This class is composed
of <CODE>CursorWarningMixIn, CursorStoreResultMixIn, CursorTupleRowsMixIn,</CODE>
and <CODE>BaseCursor</CODE>, i.e. it raises <CODE>Warning</CODE>, uses
<CODE>mysql_store_result()</CODE>, and returns rows as tuples.
<P>
<DT><B>DictCursor</B><DD><P>Like <CODE>Cursor</CODE> except it returns rows as
dictionaries.
<P>
<DT><B>SSCursor</B><DD><P>A "server-side" cursor. Like <CODE>Cursor</CODE> but uses
<CODE>CursorUseResultMixIn</CODE>.
Use only if you are dealing with potentially large result sets.
<P>
<DT><B>SSDictCursor</B><DD><P>Like <CODE>SSCursor</CODE> except it returns rows as
dictionaries.
<P>
<DT><B>XXXCursorNW</B><DD><P>> Cursors with the "NW" suffix do not raise Warnings.
<P>
</DL>
<P>For an example of how to use these classes,
read the code. If you need something more exotic than this,
you will have to roll your own.
<HR>
Next
<A HREF="MySQLdb-3.html">Previous</A>
<A HREF="MySQLdb.html#toc4">Contents</A>
</BODY>
</HTML>
|
