MySQL Stored Procedure Programming

MySQLdb Basics

In this section we’ll review the basic methods provided in the Python MySQLdb extension for establishing a connection to a MySQL server and processing simple SQL statements. These methods provide a foundation that we can use when working with stored programs. If you are already familiar with the MySQLdb extension, then you might like to skip forward to "Using Stored Programs with MySQLdb,” later in the chapter.

Creating a Connection

Before we can use MySQLdb, we need to import the module. We can then use the connect() method of the base MySQLdb class to create a connection object. The connect() method takes five arguments—host, user, passwd, db, and port—which identify the MySQL server, account, and database to which we intend to connect. Each of the arguments is optional, with sensible default values (localhost for the hostname, for instance).

Example 16-1 illustrates the basic technique.

Example 16-1. Connecting to MySQL from Python

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "root",
                        passwd = "secret",
                        db = "mysql",
                        port=3306)

Usually we will want to retrieve connection details from the command line. Python includes a powerful and useful command-line option parser that allows us to do this. Example 16-2 shows how to retrieve MySQL connection details from the command line and set up a connection.

Example 16-2. Getting connection details from the command line

import MySQLdb from optparse import OptionParser parser = OptionParser( ) parser.add_option("-u","--username", ...