Python Adventures

Problem

You want to interact with a MySQL database from your Python script.

Solution

First of all, you need to install the following package:

sudo apt-get install python-mysqldb

Then try the following basic script to check if everything is OK:

#!/usr/bin/env python

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version: ", row[0]
cursor.close ()
conn.close ()

Example:

We have a .csv file with two columns: symbol and name. Iterate through the lines and insert each line in a database table as a record.

#!/usr/bin/env python

import MySQLdb

f1 = open('./NYSE.csv',  'r')
# A line looks like this:
# ZLC;    Zale Corporation

conn = MySQLdb.connect(host = "localhost",
                       user = "user",
                       passwd = "passwd",
                       db = "table")
cursor = conn.cursor()

for line in f1:
    pieces = map(str.strip, line.split(';'))
    #print "'%s' => '%s'" % (pieces[0], pieces[1])
    query = "INSERT INTO symbol_name (symbol, name) VALUES (\"%s\", \"%s\")" % (pieces[0], pieces[1])
    #print query
    cursor.execute(query)

f1.close()

conn.commit()
cursor.close ()
conn.close ()

Links

There are lots of Python-MySQL tutorials on the net. Let’s see some of them:

• http://www.kitebird.com/articles/pydbapi.html
• http://zetcode.com/databases/mysqlpythontutorial/
• http://www.tutorialspoint.com/python/python_database_access.htm