25.05.2009

This is a short tutorial about the mysql, inside python. To be able to connection to a mysql database, you need to install (if you don't have already) the MySQLdb module. Start the python command line interface (run python in console) and enter import MySQLdb. If you get some errors, then you need to install the module. Otherwise, everything is just fine.

Our goal is to create a script who will create a table and populate it with random data; along with that, we'll get some timing around the insert function.
The class will initialize a mysql connection in the constructor, and create a cursor for later usage. This implies that you already have a working mysql server, a test database with an user with access to it. Check the variables starting with db* to see my test data.

create_table method will just create a small table with 3 fields, and insert_records will insert a specified number of records, with random data strings. We'll be using some random techniques there and also timeit, to find out how long will it take.

Just parse the code, it's very simple. In the future it's possible to add more content here, so check it from time to time if you're interested.

#!/usr/bin/env python2.4
# -*- coding: utf8 -*-
 
# MySQLdb Python
# python 2.4.3 
 
import MySQLdb
import sys
import random
from timeit import Timer
 
class MySQLDemo:
    ''' '''
    dbhost = 'localhost' # host
    dbport = 3306 # mysql port
    dbuser = 'pythonuser' # user
    dbpass = 'CKa:PE,465a4BaJr' # password
    dbdata = 'test' # database
 
    # -------------------------------------------------------------------------
    def __init__(self):
        ''' creates a connection at the initialization moment
        and also a cursor used later '''
 
        try:
            # creates a connection
            self.conn = MySQLdb.connect(  host = self.dbhost,
                                        port = self.dbport,
                                        user = self.dbuser,
                                        passwd = self.dbpass
                                    )
            self.cursor = self.conn.cursor()
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            sys.exit (1)
 
    # -------------------------------------------------------------------------
    def create_table(self):
        ''' create an example table '''
        sql = """CREATE TABLE IF NOT EXISTS animals(
                id MEDIUMINT UNSIGNED AUTO_INCREMENT,
                animalName VARCHAR(255) NOT NULL,
                animalArea VARCHAR(255) NOT NULL,
                PRIMARY KEY (id),
                INDEX (animalName , animalArea)
                ) ENGINE = InnoDB """
 
        print sql
        try:
            self.cursor.execute("USE "+self.dbdata)
            self.cursor.execute(sql)
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            sys.exit (1)
 
    # -------------------------------------------------------------------------
    def insert_records(self, howmany):
        ''' Insert random records '''
        self.cursor.execute("USE "+self.dbdata)
 
        for i in range(howmany):
            ''' Generate an animal name of 255 char '''
            name = ''.join([random.choice('ABCDEFGHIJLKMNOPRSTUVXYZ') for x in xrange(255)])
 
            ''' Generate an animal place of 255 char '''
            place = ''.join([random.choice('ABCDEFGHIJLKMNOPRSTUVXYZ') for x in xrange(255)])
 
            self.cursor.execute("INSERT INTO animals (animalName, animalArea) VALUES (%s, %s)",(name, place))
            #print "Insert statement number: %d" % i
 
        # commit here or enable autocommit
        self.conn.commit()
 
 
 
if __name__ == "__main__":
    mysqlob = MySQLDemo()
 
    print "Creates a table (if not exists) called animals"
    mysqlob.create_table()
    print "Done."
 
    print "Inserting 1000 records"
    t = Timer("mysqlob.insert_records(1000)", "from MySQLDemo import MySQLDemo;mysqlob = MySQLDemo()")
    print "Done in %0.2f sec " % t.timeit(1)