Okay, so you want to use _mysql anyway. Here are some examples.
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 USE a
database. Chances are you need to supply more
information.:
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 connect()
can take. For this reason, I prefer to use keyword 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.
Generally speaking, putting passwords in your code is not such a good
idea:
db=_mysql.connect(host="outhouse",db="thangs",read_default_file="~/.my.cnf")
This does what the previous example does, but gets the username and
password and other parameters from ~/.my.cnf (UNIX-like systems). Read
about option files
for more details.
So now you have an open connection as db 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
db.query():
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 DB API specification PEP-249 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 (how)
tells it how the row should be
represented. By default, it is zero which means, return as a tuple.
how=1 means,
return it as a dictionary, where the keys are the
column names, or table.column
if there are two columns with the
same name (say, from a join). how=2 means the same as how=1
except that the keys are always table.column; this is for
compatibility with the old Mysqldb module.
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 maxrows.
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, _mysql
can do this for you. (And MySQLdb
does do this for you.) To have automatic type conversion done, you
need to create a type converter dictionary, and pass this to
connect() as
the conv
keyword parameter.
The keys of conv
should be MySQL column types, which in the
C API are FIELD_TYPE_*.
You can get these values like this:
from MySQLdb.constants import FIELD_TYPE
By default, any column type that can't be found in conv is
returned as a string, which works for a lot of stuff. For our
purposes, we probably want this:
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 db=_mysql.connect(conv=my_conv...),
the
results will come back ((3,
2, 0),), which
is what you would
expect.