لطفا منتظر باشید ...
Recipe 7.9. Accesssing a MySQL Database
Credit: Mark Nenadov
Problem
You need to access a MySQL database.
Solution
The MySQLdb module makes this task extremely easy:
import MySQLdb
# Create a connection object, then use it to create a cursor
con = MySQLdb.connect(host="127.0.0.1", port=3306,
user="joe", passwd="egf42", db="tst")
cursor = con.cursor( )
# Execute an SQL string
sql = "SELECT * FROM Users"
cursor.execute(sql)
# Fetch all results from the cursor into a
sequence and close the connection
results = cursor.fetchall( )
con.close( )
Discussion
MySQLdb is at http://sourceforge.net/projects/mysql-python.
It is a plain and simple implementation of the Python DB API 2.0 that
is suitable for Python 2.3 (and some older versions, too) and MySQL
versions 3.22 to 4.0. MySQLdb, at the time of this
writing, did not yet officially support Python 2.4. However, if you
have the right C compiler installation to build Python extensions (as
should be the case for all Linux, Mac OS X, and other Unix users, and
many Windows developers), the current version of
MySQLdb does in fact build from sources, install,
and work just fine, with Python 2.4. A newer version of
MySQLdb is in the works, with official support for
Python 2.3 or later and MySQL 4.0 or later.As with all other Python DB API implementations (once you have
downloaded and installed the needed Python extension and have the
database engine it needs up and running), you start by importing the
module and calling the connect function with
suitable parameters. The keyword parameters you can pass when calling
connect depend on the database involved:
host (defaulting to the local host),
user, passwd (password), and
db (name of the database) are typical. In the
recipe, I explicitly pass the default local host's
IP address and the default MySQL port (3306), just to show that you
can specify parameters explicitly even when you're
passing their default values (e.g., to make your source code clearer
and more readable and maintainable).The connect function returns a connection object,
and you can proceed to call methods on this object; when you are
done, call the close method. The method you most
often call on a connection object is cursor, which
returns a cursor object, which is what you use to send SQL commands
to the database and fetch the commands' results. The
underlying MySQL database engine does not in fact support SQL
cursors, but that's no problemthe
MySQLdb module emulates them on your behalf, quite
transparently, for the limited cursor needs of the Python DB API 2.0.
Of course, this doesn't mean that you can use SQL
phrases like WHERE CURRENT OF
CURSOR with a database that does not offer
cursors! Once you have a cursor object in hand,
you can call methods on it. The recipe uses the
execute method to execute an SQL statement, and
then the fetchall method to obtain all results as
a sequence of tuplesone tuple per row in the result. You can
use many refinements, but these basic elements of the Python DB
API's functionality already suffice for many tasks.
See Also
The Python-MySQL interface module (http://sourceforge.net/projects/mysql-python);
the Python DB API (http://www.python.org/topics/database/DatabaseAPI-2.0l);
DB API documentation in Python in a Nutshell.
