MySQLdb: a Python interface for MySQL:

2. _mysql module

If you want to write applications which are portable across databases, avoid using this module directly. _mysql 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 MySQLdb module. If you really need it, use the standard MySQL docs and transliterate as necessary.

2.1 MySQL C API translation

The MySQL C API has been wrapped in an object-oriented way. The only MySQL data structures which are implemented are the MYSQL (database connection handle) and MYSQL_RES (result handle) types. In general, any function which takes MYSQL *mysql as an argument is now a method of the connection object, and any function which takes MYSQL_RES *result 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 mysql_ prefix is dropped from the name. Most of the conn methods listed are also available as MySQLdb Connection object methods. Their use is non-portable.

Starting with MySQLdb-0.9.2, the connection and result objects are subclassable types if you have at least Python-2.2.

MySQL C API function mapping
C API	`_mysql`
`mysql_affected_rows()`	`conn.affected_rows()`
`mysql_close()`	`conn.close()`
`mysql_connect()`	`_mysql.connect()`
`mysql_data_seek()`	`result.data_seek()`
`mysql_debug()`	`_mysql.debug()`
`mysql_dump_debug_info`	`conn.dump_debug_info()`
`mysql_escape_string()`	`_mysql.escape_string()`
`mysql_fetch_row()`	`result.fetch_row()`
`mysql_get_client_info()`	`_mysql.get_client_info()`
`mysql_get_host_info()`	`conn.get_host_info()`
`mysql_get_proto_info()`	`conn.get_proto_info()`
`mysql_get_server_info()`	`conn.get_server_info()`
`mysql_info()`	`conn.info()`
`mysql_insert_id()`	`conn.insert_id()`
`mysql_num_fields()`	`result.num_fields()`
`mysql_num_rows()`	`result.num_rows()`
`mysql_options()`	`_mysql.connect()`
`mysql_ping()`	`conn.ping()`
`mysql_query()`	`conn.query()`
`mysql_real_connect()`	`_mysql.connect()`
`mysql_real_query()`	`conn.query()`
`mysql_real_escape_string()`	`conn.escape_string()`
`mysql_row_seek()`	`result.row_seek()`
`mysql_row_tell()`	`result.row_tell()`
`mysql_select_db()`	`conn.select_db()`
`mysql_stat()`	`conn.stat()`
`mysql_store_result()`	`conn.store_result()`
`mysql_thread_id()`	`conn.thread_id()`
`mysql_thread_safe_client()`	`conn.thread_safe_client()`
`mysql_use_result()`	`conn.use_result()`
`CLIENT_*`	`MySQLdb.constants.CLIENT.*`
`CR_*`	`MySQLdb.constants.CR.*`
`ER_*`	`MySQLdb.constants.ER.*`
`FIELD_TYPE_*`	`MySQLdb.constants.FIELD_TYPE.*`
`FLAG_*`	`MySQLdb.constants.FLAG.*`

2.2 Some _mysql examples

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. Maybe this will work for you, if you have set up a configuration file, i.e. (~/.my.cnf). 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.

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 pydoc module is great for this.

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 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 (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.

Next Previous Contents