Book HomeMySQL and mSQLSearch this book

Chapter 20. Python Reference

Contents:

Module: MySQL
Module: mSQL

There are actually a handful of Python modules running around that support database access against MySQL and mSQL. They are very similar in most respects. This chapter provides the API specification for two common modules. It is, however, important to note an approaching unification of Python database APIs under a single API being specified by the Python Database SIG. mSQL currently has no support for this API.

20.1. Module: MySQL

The entry point into the MySQL module is via the MySQL.connect() method. The return value from this method represents a connection to a MySQL database that you can use for all of your MySQL operations.

Method: MySQL.connect( )

Signature

connection = MySQL.connect(host)

Synopsis

Connects to the MySQL database engine on the specified server. If you call connect() with no arguments, it will connect you to the MySQL database engine on the local machine. It returns a Python object representing a connection to a MySQL database.

Example

conn = MySQL.connect(`carthage.imaginary.com');
Method: connection.selectdb( )

Signature

connection.selectdb(database)

Synopsis

Selects the database against which you intend to operate.

Example

connection.selectdb(`test');
Method: connection.do( )

Signature

results = connection.do(sql)

Synopsis

Sends the specified SQL statement to the currently selected database for execution. The results are returned as a list of lists where each list represents a single row. The method is also used for updates -- you just do not process the return value.

Example

results = conn.do(`SELECT title, year FROM movies');
row1 = results[0];
Method: connection.query( )

Signature

statement_handle = connection.query(sql)

Synopsis

Like the do() method, this method sends the specified SQL statement to the currently selected database. Unlike the do() method, this method returns a statement handler object that encapsulates data about the results of the SQL query as well as the results themselves.

Example

hndl = conn.query(`SELECT title, year FROM movies');
Method: statement_handle.affectedrows( )

Signature

rowcount = statement_handle.affectedrows()

Synopsis

Assuming the results of the SQL represented by this statement handler came from an UPDATE, DELETE, or INSERT, this method returns the number of rows actually modified by that statement.

Example

rowcount = hndl.affectedrows()
Method: statement_handle.numrows( )

Signature

rowcount = statement_handle.numrows()

Synopsis

Assuming the results of the SQL represented by this statement handler came from a SELECT, this method provides the number of rows in the result set.

Example

rowcount = hndl.numrows()
Method: statement_handle.fields( )

Signature

list = statement_handle.fields()

Synopsis

Provides meta-information about the columns in the results returned by this query. The list is actually a list of lists. Each member of the returned list is a list of meta-information about a specific column. In other words, the returned list will have one member for each column in the result set. The first member of the list represents the first column, the second member the second column, and so on.

The meta-data for each column is a list of five elements:

  • A string containing the column name

  • A string containing the name of the table from which the column came

  • A string with the name of the SQL datatype for the column

  • An int containing the size of the column

  • A string containing the column modifies such as NOTNULL

Example

flds = hndl.fields();
for column in flds:
name = column[0];
table = column[1];
type = column[2];
size = columns[3];
mods = column[4];
Method: statement_handle.fetchrows( )

Signature

list = statement_handle.fetchrows(rownum)

Synopsis

Fetches the row values of the specified row number associated with the result set represented by the statement handler. If you pass -1 as an argument, this method will return a list of all the rows. For each row in the list, a row is represented by a list whose number of elements equals the number of columns in the result set. The first element represents the first column value, the second element the second column, and so on.

Example

rows = hndl.fetchrows(-1);
for row in rows:
    col1 = row[0];
    col2 = row[1];
Method: connection.listdbs( )

Signature

dbs = connection.listdbs()

Synopsis

Provides a Python list of databases available on the database server.

Example

dbs = conn.listdbs()
Method: connection.listtables( )

Signature

tables = connection.listtables();

Synopsis

Provides a Python list of tables stored in the selected database.

Example

tables = conn.listtables();


Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.