mysqldb.txt

python联接mysql驱动 python联接mysql驱动

====================
MySQLdb User's Guide
====================

.. contents::
..

Introduction
------------

MySQLdb is an thread-compatible interface to the popular MySQL
database server that provides the Python database API.

Installation
------------

The ``README`` file has complete installation instructions.


_mysql
------

If you want to write applications which are portable across databases,
use MySQLdb_, and 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.

.. _`MySQL documentation`: http://dev.mysql.com/doc/


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.

MySQL C API function mapping
............................

=================================== ==================================
      C API                         ``_mysql``
=================================== ==================================
 ``mysql_affected_rows()``          ``conn.affected_rows()``
 ``mysql_autocommit()``		    ``conn.autocommit()`` 
 ``mysql_character_set_name()``	    ``conn.character_set_name()``
 ``mysql_close()``                  ``conn.close()`` 
 ``mysql_commit()``		    ``conn.commit()``
 ``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_character_set_info()`` ``conn.get_character_set_info()``
 ``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()``		    various options to ``_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_rollback()``		    ``conn.rollback()``
 ``mysql_row_seek()``		    ``result.row_seek()`` 
 ``mysql_row_tell()``		    ``result.row_tell()`` 
 ``mysql_select_db()``		    ``conn.select_db()`` 
 ``mysql_set_character_set()``	    ``conn.set_character_set()``
 ``mysql_ssl_set()``                ``ssl`` option to ``_mysql.connect()``
 ``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()``
 ``mysql_warning_count()``          ``conn.warning_count()`` 
 ``CLIENT_*``			    ``MySQLdb.constants.CLIENT.*`` 
 ``CR_*``			    ``MySQLdb.constants.CR.*`` 
 ``ER_*``			    ``MySQLdb.constants.ER.*`` 
 ``FIELD_TYPE_*``		    ``MySQLdb.constants.FIELD_TYPE.*`` 
 ``FLAG_*``			    ``MySQLdb.constants.FLAG.*`` 
=================================== ==================================


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

.. _`option files`: http://dev.mysql.com/doc/mysql/en/Option_files.html

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

.. _PEP-249: http://www.python.org/peps/pep-0249.html

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.

MySQLdb
-------

MySQLdb is a thin Python wrapper around ``_mysql`` which makes it
compatible with the Python DB API interface (version 2).  In reality,
a fair amount of the code which implements the API is in ``_mysql``
for the sake of efficiency.

The DB API specification PEP-249_ should be your primary guide for
using this module. Only deviations from the spec and other
database-dependent things will be documented here.

Functions and attributes
........................

Only a few top-level functions and attributes are defined within
MySQLdb.

connect(parameters...)  
	 Constructor for creating a connection to the
	 database. Returns a Connection Object. Parameters are the
	 same as for the MySQL C API.  In addition, there are a few
	 additional keywords that correspond to what you would pass
	 ``mysql_options()`` before connecting. Note that some
	 parameters must be specified as keyword arguments! The
	 default value for each parameter is NULL or zero, as
	 appropriate. Consult the MySQL documentation for more
	 details. The important parameters are:

         host
            name of host to connect to. Default: use the local host 
            via a UNIX socket (where applicable)

         user
            user to authenticate as. Default: current effective user.

         passwd
            password to authenticate with. Default: no password.

         db
            database to use. Default: no default database.

	 port
	    TCP port of MySQL server. Default: standard port (3306).

         unix_socket
	    location of UNIX socket. Default: use default location or
            TCP for remote hosts.

         conv
            type conversion dictionary.  Default: a copy of
            ``MySQLdb.converters.conversions``

         compress
            Enable protocol compression. Default: no compression.

         connect_timeout
            Abort if connect is not completed within
            given number of seconds. Default: no timeout (?)

         named_pipe
            Use a named pipe (Windows). Default: don't.

         init_command
            Initial command to issue to server upon
            connection. Default: Nothing.

         read_default_file
            MySQL configuration file to read; see
            the MySQL documentation for ``mysql_options()``.

         read_default_group
            Default group to read; see the MySQL
            documentation for ``mysql_options()``.

         cursorclass
            cursor class that ``cursor()`` uses, unless
            overridden. Default: ``MySQLdb.cursors.Cursor``.  *This
            must be a keyword parameter.*

         use_unicode
            If True, CHAR and VARCHAR and TEXT columns are returned as
            Unicode strings, using the configured character set. It is
            best to set the default encoding in the server
            configuration, or client configuration (read with
            read_default_file).  If you change the character set after
            connecting (MySQL-4.1 and later), you'll need to put the
            correct character set name in connection.charset.

	    If False, text-like columns are returned as normal strings,
	    but you can always write Unicode strings.

	    *This must be a keyword parameter.*

	 charset
	    If present, the connection character set will be changed
	    to this character set, if they are not equal. Support for
	    changing the character set requires MySQL-4.1 and later
	    server; if the server is too old, UnsupportedError will be
	    raised. This option implies use_unicode=True, but you can
	    override this with use_unicode=False, though you probably
	    shouldn't.

	    If not present, the default character set is used.

	    *This must be a keyword parameter.*

	 sql_mode
	    If present, the session SQL mode will be set to the given
	    string. For more information on sql_mode, see the MySQL
	    documentation. Only available for 4.1 and newer servers.

	    If not present, the session SQL mode will be unchanged.

	    *This must be a keyword parameter.*