dbtest.py
The Python script below was written to demonstrate using Python to access a MySQL database on my hosted account on godaddy.com.
01: #!/usr/bin/python 02: import cgitb; # CGI traceback module 03: cgitb.enable() 04: 05: print "Content-Type: Text/html" 06: 07: print 08: print "<html>" 09: print "<head><title>MySQL Database Connection Test</title></head>" 10: print "<body>" 11: print "<h1>MySQL Database Connection Test</h1>" 12: 13: print "<p>" 14: 15: try: 16: import MySQLdb 17: import _mysql_exceptions as DB_EXC 18: print "Imported MySQLdb module successfully." 19: except ImportError, e: 20: print "Error import MySQLdb module" 21: 22: print "<p>trying connection ...<p>" 23: 24: try: 25: cxn = MySQLdb.connect( 26: host="hostname", 27: user="username", 28: db="dbname", 29: passwd="password") 30: print "Connection succeeded" 31: except MySQLdb.Error, e: 32: print "Connection failed." 33: print "<p>Error %d: %s" % (e.args[0], e.args[1]) 34: import sys 35: sys.exit(1) 36: 37: 38: cursor = cxn.cursor() 39: cursor.execute ("SELECT VERSION()") 40: row = cursor.fetchone() 41: print "<p>server version:", row[0] 42: cursor.close() 43: cxn.close() 44: 45: print "<p>Goodbye" 46: 47: print "</body></html>"
Lines 2-3: The cgitb
module stands for CGI Traceback, and
it makes debugging CGI applications much easier. It needs to be enabled (line 3) to turn exceptions in the script
into HTML-formatted tracebacks.
Lines 15-20: The code attempts to load the MySQL database adapter module MySQLdb. If this fails we generate an error message. When this module was first written, it ended after line 20, and was used to verify the existence of the database adaptor on the host system.
Lines 24-35: This portion of the code tests our ability to connect to an existing database.
The connection is established by invoking the connec() method of the
MySQLdb driver with the proper connection parameters. These include the
hostname where the server is running, the username and password for your
MySQL account, and the name of the database that you want to use.
The connect() argument list syntax varies among drivers; for MySQLdb, the
arguments are allowed to be given in name = value format, which has the
advantage that you can specify them in any order.
If the connect() call succeeds, it returns a connection object that serves
as the basis for further interaction with MySQL. If the call fails, it
raises an exception. We got the following output by commenting out the
hostname:
trying connection ...
Connection failed.
Error 2002: Can't connect to local MySQL server through socket '/usr/local/mysql-5.0/data/mysql.sock' (2)
Lines 38-43: After the connection object has been obtained, this section invokes its cursor() method to create a cursor object for processing statements. The script uses this cursor to issue a SELECT VERSION() statement, which returns a string containing server version information. The cursor object's execute() method sends the statement to the server and fetchone() retrieves a row as a tuple. For the statement shown here, the tuple contains a single value, which the script prints.
The final output of the script was:
Imported MySQLdb module successfully.
trying connection ...
Connection succeeded
server version: 5.0.45-log
Goodbye
Maintained by John Loomis, updated Sun Feb 10 17:06:16 2008