Using MySql 5.0 stored procedures with MySQLdb

Using MySql 5.0 stored procedures with MySQLdb

When I wanted to try out MySQL 5.0 stored procedures I didn't find too much on the web. This was a few months ago and there may be some better tutorials out there now but I figured I would share some of my tricks.

Why use stored procedures in the first place? While stored procedures give a you a performance boost (I have not benchmarked so I can't say how much) the argument I usually use when making the case for using stored procedures is abstraction. One can make changes to a stored procedure and not have to touch the calling code just as long as you don't break the interface. There have been many instances where I have avoided a code change - QA cycle - rollout just by adding an additional field to a select statement in a stored procedure.

Step 0: Install and configure MySql 5.0
MySql is at version 5.0.21 at the time of this writing. Installing MySql can be tricky, and going into all the detail of installing MySql is beyond the scope of this article, but here are some tips...

I usually install from source. The key is to follow the directions provided in the install notes and make sure that the directory the MySql installs to is owned by "mysql:mysql". Once the permissions are correct and the database has been installed, mysql usually fires right up.

Step 1: Installing MySQLdb.
Get the latest version of MySQLdb (1.2.1_p2 when I wrote this) and install...
$> tar zxfv MySQL-python-1.2.1_p2.tar.gz && cd MySQL-python-1.2.1_p2/
#> python setup.py install

Step 2: Create some tables and insert some data

mysql> use test;
mysql> create table products (
 productid int unsigned not null primary key auto_increment,
 categoryid int unsigned not null,
 productname varchar(80) not null,
 description varchar(255) not null,
 statuscd tinyint(1) unsigned not null,
 createdt datetime not null,
 index cat_prod_idx 
  (categoryid, productid, productname, description, statuscd)
 ) type=innodb;

mysql> create table categories (
 categoryid int unsigned not null primary key auto_increment,
 category varchar(80) not null,
 description varchar(255) not null,
 statuscd tinyint(1) unsigned not null,
 createdt datetime not null
 ) type=innodb;

mysql> insert into categories (category, description, statuscd, createdt) 
values ('cool category', 'these are all cool products', 1, now());

mysql> insert into categories (category, description, statuscd, createdt) 
values ('lame category', 'these are the not so cool products', 1, now());

mysql> insert into products (categoryid, productname, description, statuscd, createdt) 
values (1, 'slackware linux', 'this is my distro of choice.', 1, now());

mysql> insert into products (categoryid, productname, description, statuscd, createdt) 
values (1, 'MicroSoft Windows', 'see category for description.', 2, now());

Step 3: Build the 4 basic types of queries (select, insert, update and delete)

Select stored procedures are pretty simple the things to note are:
1. It is good to only bring back fields that you need 'select *'s' are evil
2. I prefixed the input parameter with an underscore so that it is easily identified by MySql and the next person that has to read this sp.

mysql> delimiter //
mysql> create procedure usp_get_products_by_category
 (
  _categoryid int unsigned
 )
 begin
  select
   p.productid,
   p.productname,
   p.description,
   c.category,
   c.description
  from 
   products p
   inner join categories c on p.categoryid = c.categoryid
  where
   p.categoryid = _categoryid
   and p.statuscd = 1
   and c.statuscd = 1;
 end;
 //
mysql> delimiter ;

When writing an insert sp you always want to return the id of the record you just created hence the "select LAST_INSERT_ID()". This means that you will get a one record result set back after you call the insert sp and the first and only field in the the record will be the id.

mysql> delimiter //
mysql> create procedure usp_ins_product
 (
  _categoryid int unsigned,
  _productname varchar(80),
  _description varchar(255),
  _statuscd tinyint(1) unsigned
 )
 begin
  insert into products
   (categoryid, productname, description, statuscd, createdt)
  values
   (_categoryid, _productname, _description, _statuscd, now());
  select LAST_INSERT_ID();
 end;
 //
mysql> delimiter ;

When I write my update and delete stored procedures I usually let MySql do it's thing. I always check the number of rows updated through the MySQLdb cursor object:
"count = curs.execute(stmt)"

mysql> delimiter //
mysql> create procedure usp_upd_productstatus
 (
  _productid int unsigned,
  _statuscd tinyint(1) unsigned
 )
 begin
  update products
  set statuscd = _statuscd
  where
   productid = _productid;
 end;
 //
mysql> delimiter ;

mysql> delimiter //
mysql> create procedure usp_del_product
 (
  _productid int unsigned
 )
 begin
  delete from products where productid = _productid;
 end;
 //
mysql> delimiter ;

Step 4: Testing it all out!

Fire up a python interpreter and ...

>>> import MySQLdb
>>> from MySQLdb.constants import CLIENT
>>> cnn =  MySQLdb.connect(host='localhost', user='USER', passwd='PASS', db='test', client_flag=CLIENT.MULTI_STATEMENTS)
>>> cnn.autocommit(1)
>>> curs = cnn.cursor()
>>> count = curs.execute('call usp_upd_productstatus(%d, %d)' % (1,0)) # update
>>> count = curs.execute('call usp_get_products_by_category(%d)' % (2)) # select
>>> curs.fetchall()
>>> curs.execute("call usp_ins_product(1, 'FreeBSD', 'Another great OS', 1)")
>>> curs.fetchall() # this returns the id of the new 'FreeBSD' record
>>> count = curs.execute('call usp_del_product(%d)' % (2)) # delete 
>>> curs.close()
>>> cnn.close()

The INNODB storage engine supports row level locking which means writes won't tie up the entire table. Because I created the tables using the 'type=innodb' I had to set the autocommit flag to true in the connection otherwise my queries won't get commited.