Python and MySQL

Although PHP is used a lot for SQL scripts, Python is a fine alternative.

The commonly used SQL library in Python is MySQLdb.  MySQLdb is an thread-compatible interface to the popular MySQL database server that provides the Python database API. (from the documentation)

An example showing the guts of a Python MySQL script.

import MySQLdb

conn = MySQLdb.connect (host = "localhost", user = "root", passwd = "admin", db = "databasename")

db = conn.cursor()

db.execute ( "SELECT * FROM table1")

data = db.fetchall()
#do something with the data

db.close ()

conn.commit ()

conn.close ()

Looking over the code example:

After the import is a connect request to an SQL database.  The database in on the localhost and has the default username and password.  The the actual database name must be specified.  MySQL databases can have multiple databases on one host.

Note that on all the code accessing data or running commands on the database should be inside an exception or "try except" statement.  A standard form follows:

import MySQLdb #added import for exit

import sys #added import for exit

try:

   some MySQL command

except MySQLdb.Error, e:

   print ";Error %d: %s" % (e.args[0], e.args[1])

   sys.exit (1)

The next MySQL command is the cursor method which creates a cursor object.  Most people like to make the db object the connection and the cursor object as such.  But I like to think of the connection as a connection and the cursor as the db itself so I name them appropriately.  I'm not sure what else the cursor is used for.

To run a SQL statement you use the execute method.  The parameter is a string with the standard string print formating such as "%s %f" %(string,float). However you must use the SQL VALUE keyword.  An example follows:

db.execute ("INSERT INTO incident (incident_type, session, lat, lon, alt) \
VALUES (%d, \"%s\", \"%s\", \"%s\", \"%s\")" % \(incident_type[i],sessions[i],locations[i][0],locations[i][1],locations[i][2]))

So basically you can see that use of MySQLdb ends up being an exercise in string creation in SQL command format.  Note that in the above example the SQL keywords are in all caps.  That is by tradition.

After a SQL statement executes there is usually some output besides updating of the database.  To get the output into our Python program we need one of the 3 read methods.  In the above example fetchall() is used.  The other read methods are fetchone() and rowcount.

The fetchall method grabs all the results in a list which is easy to use in Python.  The fetchone method grabs the "next: row in the output.  Rowcount is a read only count of the rows in the output.  The last two methods are probably not used much as it is easy to iterate a list in Python.

What is with the Ls??? You may see data that should be "60" appear as "60L" after you read it out of a database.  When MySQLdb converts a value from SQL unsigned integer to Python it must use a long integer to avoid overflows.  To show this conversion an "L" is appended to the value.

Also note that the data read from the database is not in the order that it was entered.  Use the ORDER BY keyword in your SQL statements to sort data appropriately.

Next in the example the cursor object is closed.  This is important when doing server side scripts so it is included although in this example it is a bit superfluous.

Next comes the important commit statement.  Without a commit all the changes you make and records you add will be lost when the database connection is closed.  Note that if you wish to do several operations and not lose your original data, the rollback method can be used.  The format would be as follows:

db.rollback()

Lastly we close our connection to the database with the close method.

Note that the commands executed are all standard SQL statements.  For a primer on SQL statements install the MySQL package and run the MySQL Command Line Client.  After you create a few tables and run a few queries you will be on your way.

Another great resource if it is installed on your SQL server is phpMyAdmin,  The package is installed on some ISPs servers such as 1and1's Linux hosting packages. Using phpMyAdmin it is easy to build tables and populate them with data.  After the tables are created there is an option to get the complete SQL statement to replicate the tables with the data which is handy if you need to send a copy of a test database to a friend without giving him access to your database server.