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 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 "
") 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("
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
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Language Fundamentals
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Error Handling
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
Transaction Management
MySQL Built-in Functions
Stored Functions
Triggers
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development