A Complete Example

In this section we will present a complete Python program that uses a stored procedure to report on the status and configuration of a MySQL server through a web interface.

The stored procedure we will use is shown in Example 16-29. It takes as an (optional) argument a database name, and reports on the objects within that database as well as a list of users currently connected to the server, server status variables, server configuration variables, and a list of databases contained within the server. It contains one OUT parameter that reports the server version.

Example 16-29. The stored procedure for our complete Python example

CREATE PROCEDURE sp_mysql_info
 (in_database VARCHAR(60),
 OUT server_version VARCHAR(100))
 READS SQL DATA
BEGIN

 DECLARE db_count INT;

 SELECT @@version
 INTO server_version;

 SELECT 'Current processes active in server' as table_header;
 SHOW full processlist;

 SELECT 'Databases in server' as table_header;

 SHOW databases;

 SELECT 'Configuration variables set in server' as table_header;
 SHOW global variables;
 SELECT 'Status variables in server' as table_header;
 SHOW global status;

 SELECT COUNT(*)
 INTO db_count
 FROM information_schema.schemata s
 WHERE schema_name=in_database;
 IF (db_count=1) THEN
 SELECT CONCAT('Tables in database ',in_database) as table_header;
 SELECT table_name
 FROM information_schema.tables
 WHERE table_schema=in_database;
 END IF;

END$$

The number and type of result sets is unpredictable, since a list of database objects is generated only if a database matching the stored procedure's first parameter is found on the server.

Prior to every major result set, the stored procedure generates a one-row "title" as a result set. This result set is identified by the column title table_header.

In this example we are going to use the Apache mod_python module to run Python code from within an Apache web page. mod_python allows the Apache web server to run Python code without having to execute an external Python program. You can find out more about downloading and configuring mod_python at http://www.modpython.org.

The HTML part of our web page is shown in Example 16-30. It displays an HTML form that asks for MySQL server connection details, including a database name.

Example 16-30. HTML form for mod_python example

MySQL Server Statistics

Enter MySQL Server Details

Enter your database connection details below:

Host:
Port:
Username:
Password:
Database:

 

 

The most important part of this HTML is the action="form.py/call_sp" portion of the FORM tag. This tells Apache that when the form is submitted, the Python program form.py should be executed with the function call_sp() as the entry point. All of the input values of the form are passed to the Python function as arguments.

Figure 16-1 shows the data entry form created by this HTML.

Example 16-31 shows the Python code that is invoked when the user clicks the Submit Query button.

Figure 16-1. mod_python input form

 

Example 16-31. Python code for our mod_python example

"+ 36 "MySQL Server status and statistics"+ 37 ""+ 38 "

MySQL Server status and statistics

") 39 html_out.append("Host: %s
" % mhost) 40 html_out.append("Port: %s
" % mport) 41 html_out.append("Version: %s
" % mysql_version) 42 html_out.append("".join(html_tables)) 43 44 html_out.append("

1 import MySQLdb
2
3 def call_sp(mhost,musername,mpassword,mdatabase,mport):
4 html_tables=[]
5 html_out=[]
6
7 try:
8 conn = MySQLdb.connect (host = mhost,
9 user = musername,
10 passwd =mpassword,
11 db = mdatabase,
12 port=int(mport))
13
14 csr1=conn.cursor( );
15 csr1.execute("call sp_mysql_info(%s,@server_version)",(mdatabase))
16 while True:
17 rows=csr1.fetchall( )
18 col_desc=csr1.description
19 if col_desc<>None: #Make sure there is a result
20 if (col_desc[0][0] == "table_header"):
21 #This is a special result set that contains a header only
22 html="

%s

" % rows[0][0] 23 else: 24 html=html_table(col_desc,rows) 25 html_tables.append(html) 26 if csr1.nextset( )==None: 27 break 28 #Get stored procedure output parameter 29 csr1.execute("SELECT @server_version") 30 row=csr1.fetchone( ) 31 mysql_version=row[0] 32 csr1.close( ) 33 34 #Build up the html output 35 html_out.append("

") 45 return "".join(html_out) 46 47 except MySQLdb.Error, e: 48 return "MySQL Error %d: %s" % (e.args[0], e.args[1]) 49 50 def html_table(col_desc,rows): 51 # Create HTML table out of cursor.description and cursor.fetchall 52 html_out=[] 53 html_out.append('') 54 for col in col_desc: 55 html_out.append('' % col[0]) 56 html_out.append('') 57 for row in rows: 58 html_out.append('') 59 for col in row: 60 html_out.append('' % col) 61 html_out.append('') 62 html_out.append('

%s

%s

') 63 s=' '.join(html_out) 64 return s

There are two main functions in this Python code:

 

call_sp()

Invokes the stored procedure to generate the MySQL server status report. This is the routine referred to in the action clause of the

tag within the calling HTML.

 

html_table()

A utility function that creates an HTML table from a MySQLdb cursor result set.

Let's start with the call_sp() routine:

Line(s)	Explanation
812	Call the MySQLdb.connect( ) method to create the MySQL connection using the parameters specified on the HTML form.
1415	Create and execute a cursor that invokes the stored procedure.
16	The WHILE loop that commences on this line will iterate through all of the result sets in the stored procedure. The loop ends on line 27.
1718	On line 17 we use fetchall( ) to retrieve all the rows in the current result set. On line 18 we retrieve the column details for that result set.
2022	If the title for the first column in the result set is table_header, then this result set contains a heading for a subsequent result set. In that case, we generate an HTML header consisting of the single row and column returned within the result set.
2324	Otherwise, create an HTML table to represent the result set. This is done using the other function in the filehtml_table ( )which we will discuss shortly.
2627	Request the next result set. If there are no further result sets, we issue break to terminate the loop that commenced on line 16. Otherwise, the loop repeats and we process the next result set.
2932	Retrieve the value of the OUT parameter. On line 29 we issue a SELECT to retrieve the user variable that contains the stored procedure OUT variable. When we called the stored procedure on line 15, we specified @server_version for the second (OUT) parameter. Now we issue a SELECT to retrieve the value of that parameter.
3545	So far, we have stored HTML that we want to generate into an array called html_tables. Now we construct the final HTML to return to the calling form. Lines 3541 add the initial HTML output into an array html_out. In line 42 we add the HTML generated from the result sets to that HTML. Finally, we return all of the HTML to the calling form on line 45.

The second functionhtml_table( )generates an HTML table when passed the results of the cursor.description and cursor.fetchall output. We call this in our main program on line 24 when we encounter a result set that we need to format as a HTML table:

Line(s)	Explanation
50	The routine takes two arguments. The first (col_desc) is a columns.description structure as returned by the description() method of the cursor object. The second is a results structure as would be returned by the fetchall() method.
5455	Loop through the rows in the col_desc parametereach row representing a column in the result setand generate HTML to create a title row for our HTML table.
5760	Generate the bulk of the HTML table. The loop on line 57 iterates through the rows in the result set. The loop on line 59 iterates through the columns in a specific row. On line 60 we generate the HTML for a specific value (for a particular column in a particular row).
6364	Consolidate all of the HTML fragmentsstored in the html_out array into a single string, which is returned to the calling function.

Figure 16-2 shows the output from our mod_python example.

Figure 16-2. Output from our mod_python example