![]()
|
Next
Previous
Contents
2. _mysql moduleIf you want to write applications which are portable across databases,
avoid using this module directly. 2.1 MySQL C API translationThe MySQL C API has been wrapped in an object-oriented way. The only
MySQL data structures which are implemented are the Starting with MySQLdb-0.9.2, the connection and result objects are subclassable types if you have at least Python-2.2.
2.2 Some _mysql examplesOkay, so you want to use The simplest possible database connection is:
import _mysql db=_mysql.connect() This creates a connection to the MySQL server running on the local
machine using the standard UNIX socket (or named pipe on Windows),
your login name (from the USER
environment variable), no password, and does not
db=_mysql.connect("localhost","joebob","moonpie","thangs") This creates a connection to the MySQL server running on the local machine via a UNIX socket (or named pipe), the user name "joebob", the password "moonpie", and selects the initial database "thangs". We haven't even begun to touch upon all the parameters
db=_mysql.connect(host="localhost",user="joebob", passwd="moonpie",db="thangs") This does exactly what the last example did, but is arguably easier to read. But since the default host is "localhost", and if your login name really was "joebob", you could shorten it to this: db=_mysql.connect(passwd="moonpie",db="thangs") UNIX sockets and named pipes don't work over a network, so if you specify a host other than localhost, TCP will be used, and you can specify an odd port if you need to (the default port is 3306): db=_mysql.connect(host="outhouse",port=3307,passwd="moonpie",db="thangs") If you really had to, you could connect to the local host with TCP by specifying the full host name, or 127.0.0.1. 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 So now you have an open connection as db.query("""SELECT spam, eggs, sausage FROM breakfast WHERE price < 5""") There's no return value from this, but exceptions can be raised. The exceptions are defined in a separate module, _mysql_exceptions ,
but _mysql exports them. Read the
DB API specification to find out what they are, or you can use
the catch-all MySQLError .
At this point your query has been executed and you need to get the results. You have two options: r=db.store_result() # ...or... r=db.use_result() Both methods return a result object. What's the difference? store_result() 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 LIMIT clause to your
query, to limit the number of rows returned. The other is to use
use_result() , 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 all the rows. Generally I
recommend using store_result() unless your result set is really
huge and you can't use LIMIT for some reason.
Now, for actually getting real results: >>> r.fetch_row() (('3','2','0'),) This might look a little odd. The first thing you should know is, fetch_row() takes some additional parameters. The first one is,
how many rows (maxrows ) should be returned. By default, it
returns one row. It may return fewer rows than you asked for, but
never more. If you set maxrows=0 , it returns all rows of the
result set. If you ever get an empty tuple back, you ran out of rows.
The second parameter ( 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 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, The keys of
from MySQLdb.constants import FIELD_TYPE By default, any column type that can't be found in
my_conv = { FIELD_TYPE.LONG: int } This means, if it's a FIELD_TYPE_LONG , call the builtin
int() function on it. Note that FIELD_TYPE_LONG is an
INTEGER column, which corresponds to a C long , which is also
the type used for a normal Python integer. But beware: If it's really
an UNSIGNED INTEGER column, this could cause overflows. For this
reason, MySQLdb actually uses long() to do the
conversion. But we'll ignore this potential problem for now.
Then if you use Next Previous Contents |