Python : Accesssing a MySQL Database

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

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



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.0.html).