Creating Your Own Functions in MySQL for Python

Exclusive offer: get 50% off this eBook here
MySQL for Python

MySQL for Python — Save 50%

Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications

€23.99    €12.00
by Albert Lukaszewski, PhD | September 2010 | MySQL Open Source

Comparing MySQL's string functions to Python's, you will notice that Python supports the capitalize() and capwords() functions. These capitalize the initial letter of the string and the first letter of each word, respectively. MySQL has no built-in capability to do this. It either returns all uppercase, all lowercase, or the original format of the string value. To put the onus of capitalization on the MySQL server, we need to define our own functions.

In this article, Albert Lukaszewski, PhD, author of MySQL for Python, we will cover the following topics:

  • Hello()
  • Capitalise()
  • DELIMITER

 

MySQL for Python

MySQL for Python

Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications

  • Implement the outstanding features of Python's MySQL library to their full potential
  • See how to make MySQL take the processing burden from your programs
  • Learn how to employ Python with MySQL to power your websites and desktop applications
  • Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios
  • A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server

Read more about this book

(For more resources on Phython see here.)

Hello()

To create a function, we necessarily have to go back to the CREATE statement. As in a Python function definition, MySQL expects us to declare the name of the function as well as any arguments it requires. Unlike Python, MySQL also wants the type of data that will be received by the function. The beginning of a basic MySQL function definition looks like this:

CREATE FUNCTION hello(s CHAR(20))

MySQL then expects to know what kind of data to return. Again, we use the MySQL data type definitions for this.

RETURNS CHAR(50)

This just tells MySQL that the function will return a character string of 50 characters or less.

If the function will always perform the same task, it is best for the sake of performance to include the keyword DETERMINISTIC next. If the behavior of the function varies, use the keyword NON-DETERMINISTIC. If no keyword is set for the characteristic of the function, MySQL defaults to NON-DETERMINISTIC.

You can learn more about the characteristic keywords used in function definitions at:
http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

Finally comes the meat of the function definition. Here we can set variables and perform any calculations that we want. For our basic definition, we will simply return a concatenated string:

RETURN CONCAT('Hello, ', s, '!');

The function obviously concatenates the word 'Hello' with whatever argument is passed to it and appends an exclamation point at the end. To call it we use SELECT as with the other functions:

mysql> SELECT hello('world') as Greeting;

Capitalise()

A function to capitalize every initial letter in a string follows the same pattern. The main point of the function is to walk through the string, character by character, and use UPPER() on every character that does not follow a letter.

DELIMITER

Obviously, we need a way to pass the entire function to MySQL without having any of the lines evaluated until we call it. To do this, we use the keyword DELIMITER. DELIMITER allows users to tell MySQL to evaluate lines that end in the character(s) we set. So the process for complex function definitions becomes:

  1. Change the delimiter.
  2. Pass the function with the usual semicolons to indicate the end of the line.
  3. Change the delimiter back to a semicolon.
  4. Call the function.

The DELIMITER keyword allows us to specify more than one character as the line delimiter. So in order to ensure we don't need to worry about our code inadvertently conflicting with a line delimiter, let's make the delimiter @@:

DELIMITER @@

The function definition

From here, we are free to define a function to our specification. The definition line will read as follows:

CREATE FUNCTION `Capitalise`(instring VARCHAR(1000))

The function will return a character string of similar length and variability:

RETURNS VARCHAR(1000)

When MySQL functions extend beyond the simplest calculations, such as hello(), MySQL requires us to specify the beginning and ending of the function. We do that with the keywords BEGIN and END. So let's begin the function:

BEGIN

Next, we need to declare our variables and their types using the keyword DECLARE:

DECLARE i INT DEFAULT 1;
DECLARE achar, imark CHAR(1);
DECLARE outstring VARCHAR(1000) DEFAULT LOWER(instring);

The DEFAULT keyword allows us to specify what should happen if outstring should fail for some reason.

Next, we define a WHILE loop:

WHILE i <= CHAR_LENGTH(instring) DO

The WHILE loop obviously begins with a conditional statement based on the character length of instring. The resulting action begins with the keyword DO. From here, we set a series of variables and express what should happen where a character follows one of the following:

blank space & '' _ ? ; : ! , - / ( .

The operational part of the function looks like this:

SET achar = SUBSTRING(instring, i, 1);
SET imark = CASE WHEN i = 1 THEN ' '
ELSE SUBSTRING(instring, i - 1, 1) END CASE;
IF imark IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-
', '/', '(', '.') THEN SET outstring = INSERT(outstring, i, 1, UPPER(achar));
END IF;
SET i = i + 1;

Much of this code is self-explanatory. It is worth noting, however, that the apodosis of any conditional in MySQL must end with the keyword END. In the case of IF, we use END IF.

In the second SET statement, the keyword CASE is an evaluative keyword that functions similar to the try...except structure in Python. If the WHEN condition is met, the empty THEN apodosis is executed. Otherwise, the ELSE exception applies and the SUBSTRING function is run. The CASE structure ends with END CASE. MySQL will equally recognize the use of END instead.

The subsequent IF clause evaluates whether imark, defined as the character before achar, is one of the declared characters. If it is, then that character in instring is replaced with its uppercase equivalent in outstring.

After the IF clause is finished, the loop is incremented by one. After the entire string is processed, we then end the WHILE loop with:

END WHILE;

After the function's operations are completed, we return the value of outstring and indicate the end of the function:

RETURN outstring;
END@@

Finally, we must not forget to return the delimiter to a semicolon:

DELIMITER ;

It is worth noting that, instead of defining a function in a MySQL session we can define it in a separate file and load it on the fly with the SOURCE command. If we save the function to a file called capfirst.sql in a directory temp, we can source it relatively:
We can also use:
SOURCE /home/skipper/temp/capfirst.sql;

MySQL for Python Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications
Published: September 2010
eBook Price: €23.99
Book Price: €38.99
See more
Select your format and quantity:

Read more about this book

(For more resources on Phython see here.)

Calling the function

With the function loaded into memory, we can then call it:

mysql> SELECT Capitalise('we have a meeting a.s.a.p.');

Of course, we would not normally write like this. However, we can call the function as part of a SELECT statement, just like any other MySQL function.

mysql> SELECT CONCAT(Capitalise('we '), 'have a meeting ', Capitalise('a.
s.a.p.')) as Message

Defining the function in Python

As you can guess by now, calling the function in Python is as simple as passing it through cursor.execute(). If we have a cursor defined as cursor, we can pass the last example of the previous section as follows:

statement = "SELECT CONCAT(Capitalise('we '), 'have a meeting ',
Capitalise('a.s.a.p.')) as Message"
cursor.execute(statement)

We then proceed to fetchall() the results.

Defining the function as a Python value

Defining the function is a bit different from calling it. If you try to pass the function we previously defined through Python as a value of statement, you get a programming error that reads something like this:

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER @@\n\nCREATE FUNCTION `Capitalise`(instring varchar(1000))\n\tRETURNS VARC' at line 1")

The problem is the DELIMITER statement. If we pull those and define statement as follows, we will have no problems (thanks to William Chiquito for the following code):

statement = """
CREATE FUNCTION `Capitalise`(instring varchar(1000))
RETURNS VARCHAR(1000)
BEGIN

DECLARE i INT DEFAULT 1;
DECLARE achar, imark CHAR(1);
DECLARE outstring VARCHAR(1000) DEFAULT LOWER(instring);

WHILE i <= CHAR_LENGTH(instring) DO
SET achar = SUBSTRING(instring, i, 1);
SET imark = CASE WHEN i = 1 THEN ' '
ELSE SUBSTRING(instring, i - 1, 1) END;
IF imark IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/',
'(', '.') THEN SET outstring = INSERT(outstring, i, 1, UPPER(achar));
END IF;
SET i = i + 1;
END WHILE;

RETURN outstring;

END;
"""

Putting function definitions into your code increases the amount of runtime resources needed and can make maintenance quite onerous. Instead, we can save it in a separate file. Note that MySQL for Python does not allow the use of MySQL's SOURCE command. So one must use alternative means to the same effect.

Sourcing the MySQL function as a Python module

We can take the preceding code and source it as a Python module. Begin it with the following:

#!/usr/bin/env python
## This shebang is for a Linux machine. Adjust your shebang line
accordingly

def MakeStatement():

statement = """
CREATE FUNCTION `CapMe`(instring varchar(1000))
...
and end it:
...

END;

"""return statement

If we save it as capfirst.py, we can reference it as follows:
import capfirst
...
statement = capfirst.MakeStatement()

Sourcing the function as MySQL code

It may seem a bit excessive to put Python's function trappings around a variable assignment. A simpler way of affecting the same results is to read the MySQL file into memory as the value of statement and then pass that value through execute():

statement = open("/home/skipper/temp/capfirst.sql").read()
runit = cursor.execute(statement)

The effect is the same and the function is created in the end. Similar tactics can be used whenever you need to source a MySQL file (for example, the dump of a database).

Room to grow

This function does essentially what we want it to do: capitalizes the first letter of every word in the string. However, it still has a few drawbacks that should be addressed:

  • What happens when you evaluate the string "we have a meeting a.s.a.p. in Brussels"? How would you rectify it?
  • How would you handle a string such as "we were 'discussing' just that"?
  • How do you write a function to load the function when you need it and get rid of it when you don't?
  • The current function emulates the capwords() function of Python. How would you simulate Python's capitalize() function?

Summary

In this article we learnt about creating your own functions in MySQL for Python.


Further resources on this subject:


MySQL for Python Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications
Published: September 2010
eBook Price: €23.99
Book Price: €38.99
See more
Select your format and quantity:

About the Author :


Albert Lukaszewski, PhD

Albert Lukaszewski is a principal consultant for Lukaszewski Consulting Services in southeast Scotland. He has programmed computers for nearly 30 years. Much of his experience is related to text processing, database systems, and natural language processing (NLP). Currently he consults on database applications for companies in the financial and publishing industries.

Books From Packt


Python 3 Object Oriented Programming
Python 3 Object Oriented Programming

Matplotlib for Python Developers
Matplotlib for Python Developers

Python Testing: Beginner's Guide
Python Testing: Beginner's Guide

Expert Python Programming
Expert Python Programming

Spring Python 1.1
Spring Python 1.1

wxPython 2.8 Application Development Cookbook: RAW
wxPython 2.8 Application Development Cookbook: RAW

Plone 3 Multimedia
Plone 3 Multimedia

Practical Plone 3: A Beginner's Guide to Building Powerful Websites
Practical Plone 3: A Beginner's Guide to Building Powerful Websites


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software