Python Cookbook

Accessing 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

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