Credit: Mark Nenadov
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( )
You can get the MySQLdb
module from http://sourceforge.net/projects/mysql-python.
It is a plain and simple implementation of the Python DB API 2.0 that
is suitable for all Python versions from 1.5.2 to 2.2.1 and MySQL
Versions 3.22 to 4.0.
As with all other Python DB API implementations, 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) 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 until, when you
are done, you 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 problem—the
MySQLdb
module emulates them on your behalf quite
transparently. 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 the
fetchall
method to obtain all results as a sequence of tuples—one tuple
per row in the result. There are many refinements you can use, but
these basic elements of the Python DB API’s
functionality already suffice for many tasks.
The Python/MySQL interface module (http://sourceforge.net/projects/mysql-python); the Python DB API (http://www.python.org/topics/database/DatabaseAPI-2.0.html).