Previously I made a  blog post regarding installing mod_python. Now I will share with you how to query mysql tables with Python and MySQLdb.

Installing MySQLdb

If we want to check if mysqldb is already installed, type ‘python’ on the commandline and then, type ‘import MySQLdb’. If it does not respond to an error, it means you have it already installed.

Checking MySQLdb

If it does, then you have to download MySQLdb from sourceforget.net,  decompress the file, change to the decompressed directory, and then type ‘sudo python setup.py install’. Installation instructions can also be read on the README file available in the package.

I assume you have already MySQL Server installed, so we can skip that.

Grocery Store, Python and MySQLdb

Let’s say we have a situation that we have to make a list of items of our stock in a Grocery Store.

We create a MySQL table with this SQL statement and populate it with our items list,

{code}

CREATE TABLE items (
id int(11) auto_increment,
item_code varchar(100),
item_desc varchar(100)
);

INSERT INTO items VALUES(‘CM2344′,’apple’),
(‘CM2531′,’pineapple’),
(‘CM1452′,’watermelons’),
(‘CM2442′,’oranges’);
{/code}

Now, let’s make a python script that will fetch the items list from MySQL output it in an HTML table.

{code}
from mod_python import apache
def mysqlconnect(tmonth,tyear):
import MySQLdb
db = MySQLdb.connect(“localhost”,”root”,”",”getzdb”)
cursor = db.cursor()
sql = “SELECT item_code,item_desc FROM test”

cursor.execute(sql)
results = cursor.fetchall()
db.close()
return results

def index(req,tmonth,tyear):
req.content_type = “text/html”
results = mysqlconnect(tmonth,tyear)
req.write(“<table border=\”1\” cellpadding=\”2\” cellspacing=\”2\” width=\”100%\”>”)
req.write(“<tr><td>ITEM NO</td><td>ITEM DESC</td></tr>”)
for i in results:

req.write(“<tr><td>”+i[0]+”</td><td>”+i[1]+”</td></tr>”)
req.write(“</table>”)
{/code}

The output should be like this:

Python Items List of Grocery List