Creating Your Own Functions in MySQL for Python

MySQL for Python

September 2010


Integrating MySQL and Python can bring a whole new level of productivity to your applications. This practical tutorial shows you how with examples and explanations that clarify even the most difficult concepts.


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.)


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:


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


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:

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;


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.


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 @@:


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:


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:


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

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));
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:


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

RETURN outstring;

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


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;

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"

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))

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));
SET i = i + 1;

RETURN outstring;


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

def MakeStatement():

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


"""return statement

If we save it as, 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?


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

Further resources on this subject:

Books to Consider

comments powered by Disqus

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free