User Tools

Site Tools


python:mysqldb

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

python:mysqldb [2013/03/16 17:40]
python:mysqldb [2013/03/16 17:40] (current)
Line 1: Line 1:
 +=== Python and MySQLdb ===
 +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.
 +
 +
 +<code python>
 +#​!/​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)
 +
 +</​code>​
  
python/mysqldb.txt ยท Last modified: 2013/03/16 17:40 (external edit)