Easy Python: multi-threading MySQL queries [updated]

There are many times when writing an application that single threaded database operations are simply too slow. In these cases it’s a matter of course that you’ll use multi-threading or forking to spawn secondary processes to handle the database actions. In this simple example for Python multi-threading you’ll see the how simple it is to improve the performance of your python app.

#!/usr/bin/env python
'''
@author: Matt Reid
@date: 2012-01-20
@purpose: example to show thread-safe MySQL connection pooling with multi-threaded SQL execution
@license: new BSD: http://goo.gl/Gndt6
@requires: Python 2.6, MySQL Server 5.0+, MySQL-python connector
@SQL table create statement:
  CREATE TABLE `test` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `col_a` varchar(255) DEFAULT NULL,
  `col_b` varchar(255) DEFAULT NULL,
  `col_c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
'''

import threading
import sys
import MySQLdb
import pdb

class threader(threading.Thread):
    def __init__(self,db):
        threading.Thread.__init__(self)
        self.db = db
    def run(self):
        run_insert(self.db)

def run_insert(db):
    sql = "INSERT INTO `test` (`id`,`col_a`,`col_b`,`col_c`) VALUES (NULL,'0','0','0');"
    print "thread executing sql:%s"%(sql)
    try:
        cursor = db.cursor()
        cursor.execute(sql)
        cursor.close()
        db.commit()
    except:
        print "insert failed"

    return

def init_thread(): 
    backgrounds = []
    for db in connections:
        print "connection: %s"%(db)
        background = threader(db)
        background.start()
        backgrounds.append(background)

    for background in backgrounds:
        background.join()

def main():
    try:
        init_thread()
    except:
        print "failed to initiate threads"
    sys.exit(0)

if __name__ == "__main__":
    mysql_host = "localhost" 
    mysql_user = "root" 
    mysql_pass = "password" 
    mysql_port = int(3306)
    mysql_db = "test" 
    threads = int(4) #quantity of execution threads and size of connection pool

    connections = []
    for thread in range(threads):
        try:
            connections.append(MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db, port=mysql_port))
            #pdb.set_trace()

        except MySQLdb.Error, e:
            print "Error %d: %s"%(e.args[0], e.args[1])
            pdb.set_trace()            
            sys.exit (1)

    main()
    

Output:

$> python ./test.py 
connection: <_mysql.connection open to 'localhost' at 2389850>
thread executing sql:INSERT INTO `test` (`id`,`col_a`,`col_b`,`col_c`) VALUES (NULL,'0','0','0');
connection: <_mysql.connection open to 'localhost' at 240da70>
thread executing sql:INSERT INTO `test` (`id`,`col_a`,`col_b`,`col_c`) VALUES (NULL,'0','0','0');
connection: <_mysql.connection open to 'localhost' at 24c6d70>
thread executing sql:INSERT INTO `test` (`id`,`col_a`,`col_b`,`col_c`) VALUES (NULL,'0','0','0');
connection: <_mysql.connection open to 'localhost' at 23c6290>
thread executing sql:INSERT INTO `test` (`id`,`col_a`,`col_b`,`col_c`) VALUES (NULL,'0','0','0');

* this script has been updated

7 thoughts on “Easy Python: multi-threading MySQL queries [updated]

  1. Hey, I’ve read this article on Planet MySQL recently and bookmarked it. I’ve finally opened sqlexamples.org to the public which I’m slowly want to introduce to the open source community. What do you personally think about publishing such interesting snippets on sqlexamples.org? We want to build an archive of free (as in free speech) sql and nosql related examples of all kinds. To make this dream come true, we also give users a fair chance to get something back for their work. This is seriously no spam and no fake:

    http://sqlexamples.org/content/new-star-born-sqlexamplesorg-goes-online

    Thank you very much!

  2. admin says:

    Sounds like a fun project. I’ll keep it in mind for future posts. :)

  3. Jay Pipes says:

    You will run into performance problems with the Python threading module because of the GIL. I’d recommend looking at either Eventlet for non-blocking asynchronous calls (eventlet.GreenPool and similar) or using the multiprocessing Python 2.6+ module.

    Cheers,
    jay

  4. netmouse says:

    self.tx= ???

  5. vind says:

    There are many mistakes in this code.
    THis code is not able to initialize threads too.

  6. admin says:

    Good catch. The script has been updated and tested. The original post was a paste from a testing script instead of the formal script.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>