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
|
<!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: _mysql module</TITLE>
<LINK HREF="MySQLdb-3.html" REL=next>
<LINK HREF="MySQLdb-1.html" REL=previous>
<LINK HREF="MySQLdb.html#toc2" REL=contents>
</HEAD>
<BODY>
<A HREF="MySQLdb-3.html">Next</A>
<A HREF="MySQLdb-1.html">Previous</A>
<A HREF="MySQLdb.html#toc2">Contents</A>
<HR>
<H2><A NAME="_mysql"></A> <A NAME="s2">2. _mysql module</A></H2>
<P>If you want to write applications which are portable across databases,
avoid using this module directly. <CODE>_mysql</CODE> provides an
interface which mostly implements the MySQL C API. For more
information, see the MySQL documentation. The documentation for this
module is intentionally weak because you probably should use the
higher-level
<A HREF="MySQLdb-3.html#MySQLdb">MySQLdb</A>
module. If you really need it, use the
standard MySQL docs and transliterate as necessary.
<P>
<H2><A NAME="ss2.1">2.1 MySQL C API translation</A>
</H2>
<P>The MySQL C API has been wrapped in an object-oriented way. The only
MySQL data structures which are implemented are the <CODE>MYSQL</CODE>
(database connection handle) and <CODE>MYSQL_RES</CODE> (result handle)
types. In general, any function which takes <CODE>MYSQL *mysql</CODE> as
an argument is now a method of the connection object, and any function
which takes <CODE>MYSQL_RES *result</CODE> 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 <CODE>mysql_</CODE> prefix is dropped from the name. Most of the
<CODE>conn</CODE> methods listed are also available as MySQLdb Connection
object methods. Their use is non-portable.
<P>
<CENTER><TABLE BORDER><TR><TD>
<BR>
C API </TD><TD> <CODE>_mysql</CODE></TD></TR><TR><TD>
<CODE>mysql_affected_rows()</CODE> </TD><TD> <CODE>conn.affected_rows()</CODE></TD></TR><TR><TD>
<CODE>mysql_close()</CODE> </TD><TD> <CODE>conn.close()</CODE></TD></TR><TR><TD>
<CODE>mysql_connect()</CODE> </TD><TD> <CODE>_mysql.connect()</CODE></TD></TR><TR><TD>
<CODE>mysql_data_seek()</CODE> </TD><TD> <CODE>result.data_seek()</CODE></TD></TR><TR><TD>
<CODE>mysql_debug()</CODE> </TD><TD> <CODE>_mysql.debug()</CODE></TD></TR><TR><TD>
<CODE>mysql_dump_debug_info</CODE> </TD><TD> <CODE>conn.dump_debug_info()</CODE></TD></TR><TR><TD>
<CODE>mysql_escape_string()</CODE> </TD><TD> <CODE>_mysql.escape_string()</CODE></TD></TR><TR><TD>
<CODE>mysql_fetch_row()</CODE> </TD><TD> <CODE>result.fetch_row()</CODE></TD></TR><TR><TD>
<CODE>mysql_get_client_info()</CODE> </TD><TD> <CODE>_mysql.get_client_info()</CODE></TD></TR><TR><TD>
<CODE>mysql_get_host_info()</CODE> </TD><TD> <CODE>conn.get_host_info()</CODE></TD></TR><TR><TD>
<CODE>mysql_get_proto_info()</CODE> </TD><TD> <CODE>conn.get_proto_info()</CODE></TD></TR><TR><TD>
<CODE>mysql_get_server_info()</CODE> </TD><TD> <CODE>conn.get_server_info()</CODE></TD></TR><TR><TD>
<CODE>mysql_info()</CODE> </TD><TD> <CODE>conn.info()</CODE></TD></TR><TR><TD>
<CODE>mysql_insert_id()</CODE> </TD><TD> <CODE>conn.insert_id()</CODE></TD></TR><TR><TD>
<CODE>mysql_list_dbs()</CODE> </TD><TD> <CODE>conn.list_dbs()</CODE></TD></TR><TR><TD>
<CODE>mysql_list_fields()</CODE> </TD><TD> <CODE>conn.list_fields()</CODE></TD></TR><TR><TD>
<CODE>mysql_list_processes()</CODE> </TD><TD> <CODE>conn.list_processes()</CODE></TD></TR><TR><TD>
<CODE>mysql_list_tables()</CODE> </TD><TD> <CODE>conn.list_tables()</CODE></TD></TR><TR><TD>
<CODE>mysql_num_fields()</CODE> </TD><TD> <CODE>result.num_fields()</CODE></TD></TR><TR><TD>
<CODE>mysql_num_rows()</CODE> </TD><TD> <CODE>result.num_rows()</CODE></TD></TR><TR><TD>
<CODE>mysql_options()</CODE> </TD><TD> <CODE>_mysql.connect()</CODE></TD></TR><TR><TD>
<CODE>mysql_ping()</CODE> </TD><TD> <CODE>conn.ping()</CODE></TD></TR><TR><TD>
<CODE>mysql_query()</CODE> </TD><TD> <CODE>conn.query()</CODE></TD></TR><TR><TD>
<CODE>mysql_real_connect()</CODE> </TD><TD> <CODE>_mysql.connect()</CODE></TD></TR><TR><TD>
<CODE>mysql_real_query()</CODE> </TD><TD> <CODE>conn.query()</CODE></TD></TR><TR><TD>
<CODE>mysql_real_escape_string()</CODE> </TD><TD> <CODE>conn.escape_string()</CODE></TD></TR><TR><TD>
<CODE>mysql_row_seek()</CODE> </TD><TD> <CODE>result.row_seek()</CODE></TD></TR><TR><TD>
<CODE>mysql_row_tell()</CODE> </TD><TD> <CODE>result.row_tell()</CODE></TD></TR><TR><TD>
<CODE>mysql_select_db()</CODE> </TD><TD> <CODE>conn.select_db()</CODE></TD></TR><TR><TD>
<CODE>mysql_stat()</CODE> </TD><TD> <CODE>conn.stat()</CODE></TD></TR><TR><TD>
<CODE>mysql_store_result()</CODE> </TD><TD> <CODE>conn.store_result()</CODE></TD></TR><TR><TD>
<CODE>mysql_thread_id()</CODE> </TD><TD> <CODE>conn.thread_id()</CODE></TD></TR><TR><TD>
<CODE>mysql_use_result()</CODE> </TD><TD> <CODE>conn.use_result()</CODE></TD></TR><TR><TD>
<CODE>CLIENT_*</CODE> </TD><TD> <CODE>MySQLdb.constants.CLIENT.*</CODE></TD></TR><TR><TD>
<CODE>CR_*</CODE> </TD><TD> <CODE>MySQLdb.constants.CR.*</CODE></TD></TR><TR><TD>
<CODE>ER_*</CODE> </TD><TD> <CODE>MySQLdb.constants.ER.*</CODE></TD></TR><TR><TD>
<CODE>FIELD_TYPE_*</CODE> </TD><TD> <CODE>MySQLdb.constants.FIELD_TYPE.*</CODE></TD></TR><TR><TD>
<CODE>FLAG_*</CODE> </TD><TD> <CODE>MySQLdb.constants.FLAG.*</CODE>
<CAPTION>MySQL C API function mapping</CAPTION>
</TD></TR></TABLE></CENTER>
<P>
<H2><A NAME="ss2.2">2.2 Some _mysql examples</A>
</H2>
<P>Okay, so you want to use <CODE>_mysql</CODE> anyway. Here are some examples.
<P>
<P>The simplest possible database connection is:
<P>
<HR>
<PRE>
import _mysql
db=_mysql.connect()
</PRE>
<HR>
<P>This creates a connection to the MySQL server running on the local
machine using the standard UNIX socket, your login name (from the USER
environment variable), no password, and does not <CODE>USE</CODE> a database.
Maybe this will work for you, if you have set up a configuration file,
i.e. (<CODE>~/.my.cnf</CODE>). Chances are you need to supply more
information.
<P>
<HR>
<PRE>
db=_mysql.connect("localhost","joebob","moonpie","thangs")
</PRE>
<HR>
<P>This creates a connection to the MySQL server running on the local
machine <EM>using TCP on the standard port (3306)</EM>, the user name
"joebob", the password "moonpie", and selects the initial database
"thangs".
<P>
<P>We haven't even begun to touch upon all the parameters <CODE>connect()</CODE>
can take, and you'll notice that if you use positional parameters, you
basically are stuck with using TCP, which is not as quite as fast as
using a UNIX socket. (Of course, you have to use TCP anyway if it's a
remote host.) For this reason, I prefer to use keyword parameters:
<P>
<HR>
<PRE>
db=_mysql.connect(host="localhost",user="joebob",
passwd="moonpie",db="thangs")
</PRE>
<HR>
This does exactly what the last example did, but is arguably easier
to read. Now if you really did want to use that UNIX socket, and your
login name really was "joebob", you could shorten it to this:
<HR>
<PRE>
db=_mysql.connect(passwd="moonpie",db="thangs")
</PRE>
<HR>
There are some other parameters you can use, and most of them aren't
needed, except for one, which we'll get to momentarily. For the rest,
read the built-in documentation. Python 2.1's <CODE>pydoc</CODE> module is
great for this.
<P>
<P>So now you have an open connection as <CODE>db</CODE> and want to do a
query. Well, there are no cursors in MySQL, and no parameter
substitution, so you have to pass a complete query string to
<CODE>db.query()</CODE>:
<HR>
<PRE>
db.query("""SELECT spam, eggs, sausage FROM breakfast
WHERE price < 5""")
</PRE>
<HR>
There's no return value from this, but exceptions can be raised. The
exceptions are defined in a separate module, <CODE>_mysql_exceptions</CODE>,
but <CODE>_mysql</CODE> exports them. Read the
<A HREF="http://www.python.org/topics/database/DatabaseAPI-2.0.html">DB API specification</A> to find out what they are, or you can use
the catch-all <CODE>MySQLError</CODE>.
<P>
<P>At this point your query has been executed and you need to get the
results. You have two options:
<HR>
<PRE>
r=db.store_result()
# ...or...
r=db.use_result()
</PRE>
<HR>
Both methods return a result object. What's the difference?
<CODE>store_result()</CODE> returns the entire result set to the client
immediately. If your result set is really large, this could be a
problem. One way around this is to add a <CODE>LIMIT</CODE> clause to your
query, to limit the number of rows returned. The other is to use
<CODE>use_result()</CODE>, which keeps the result set in the server and sends
it row-by-row when you fetch. This does, however, tie up server
resources, and it ties up the connection: You cannot do any more
queries until you have fetched <EM>all</EM> the rows. Generally I
recommend using <CODE>store_result()</CODE> unless your result set is really
huge and you can't use <CODE>LIMIT</CODE> for some reason.
<P>
<P>Now, for actually getting real results:
<HR>
<PRE>
>>> r.fetch_row()
(('3','2','0'),)
</PRE>
<HR>
This might look a little odd. The first thing you should know is,
<CODE>fetch_row()</CODE> takes some additional parameters. The first one is,
how many rows (<CODE>maxrows</CODE>) should be returned. By default, it
returns one row. It may return fewer rows than you asked for, but
never more. If you set <CODE>maxrows=0</CODE>, it returns all rows of the
result set. If you ever get an empty tuple back, you ran out of rows.
<P>
<P>
<P>The second parameter (<CODE>how</CODE>) tells it how the row should be
represented. By default, it is zero which means, return as a tuple.
<CODE>how=1</CODE> means, return it as a dictionary, where the keys are the
column names, or <CODE>table.column</CODE> if there are two columns with the
same name (say, from a join). <CODE>how=2</CODE> means the same as <CODE>how=1</CODE>
except that the keys are <EM>always</EM> <CODE>table.column</CODE>; this is for
compatibility with the old <CODE>Mysqldb</CODE> module.
<P>
<P>OK, so why did we get a 1-tuple with a tuple inside? Because we
implicitly asked for one row, since we didn't specify <CODE>maxrows</CODE>.
<P>
<P>The other oddity is: Assuming these are numeric columns, why are
they returned as strings? Because MySQL returns all data as strings
and expects you to convert it yourself. This would be a real pain in
the ass, but in fact, <CODE>_mysql</CODE> can do this for you. (And
<CODE>MySQLdb</CODE> does do this for you.) To have automatic type conversion
done, you need to create a type converter dictionary, and pass this
to <CODE>connect()</CODE> as the <CODE>conv</CODE> keyword parameter.
<P>
<P>The keys of <CODE>conv</CODE> should be MySQL column types, which in the
C API are <CODE>FIELD_TYPE_*</CODE>. You can get these values like this:
<P>
<HR>
<PRE>
from MySQLdb.constants import FIELD_TYPE
</PRE>
<HR>
<P>By default, any column type that can't be found in <CODE>conv</CODE> is
returned as a string, which works for a lot of stuff. For our
purposes, we probably want this:
<P>
<HR>
<PRE>
my_conv = { FIELD_TYPE.LONG: int }
</PRE>
<HR>
This means, if it's a <CODE>FIELD_TYPE_LONG</CODE>, call the builtin
<CODE>int()</CODE> function on it. Note that <CODE>FIELD_TYPE_LONG</CODE> is an
<CODE>INTEGER</CODE> column, which corresponds to a C <CODE>long</CODE>, which is also
the type used for a normal Python integer. But beware: If it's really
an <CODE>UNSIGNED INTEGER</CODE> column, this could cause overflows. For this
reason, <CODE>MySQLdb</CODE> actually uses <CODE>long()</CODE> to do the
conversion. But we'll ignore this potential problem for now.
<P>
<P>Then if you use <CODE>db=_mysql.connect(conv=my_conv...)</CODE>, the
results will come back <CODE>((3, 2, 0),)</CODE>, which is what you would
expect.
<P>
<HR>
<A HREF="MySQLdb-3.html">Next</A>
<A HREF="MySQLdb-1.html">Previous</A>
<A HREF="MySQLdb.html#toc2">Contents</A>
</BODY>
</HTML>
|
