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.
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( )|
connection = MySQL.connect(host)
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.
conn = MySQL.connect(`carthage.imaginary.com');
|Method: connection.selectdb( )|
Selects the database against which you intend to operate.
|Method: connection.do( )|
results = connection.do(sql)
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.
results = conn.do(`SELECT title, year FROM movies'); row1 = results;
|Method: connection.query( )|
statement_handle = connection.query(sql)
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.
hndl = conn.query(`SELECT title, year FROM movies');
|Method: statement_handle.affectedrows( )|
rowcount = statement_handle.affectedrows()
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.
rowcount = hndl.affectedrows()
|Method: statement_handle.numrows( )|
rowcount = statement_handle.numrows()
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.
rowcount = hndl.numrows()
|Method: statement_handle.fields( )|
list = statement_handle.fields()
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
flds = hndl.fields(); for column in flds: name = column; table = column; type = column; size = columns; mods = column;
|Method: statement_handle.fetchrows( )|
list = statement_handle.fetchrows(rownum)
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.
rows = hndl.fetchrows(-1); for row in rows: col1 = row; col2 = row;
|Method: connection.listdbs( )|
dbs = connection.listdbs()
Provides a Python list of databases available on the database server.
dbs = conn.listdbs()
Copyright © 2001 O'Reilly & Associates. All rights reserved.