[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Creating Your Own Functions in MySQL for Python, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
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
 |
Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications
|
(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:
- Change the delimiter.
- Pass the function with the usual semicolons to indicate the end of the line.
- Change the delimiter back to a semicolon.
- 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;
(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:
- Setting Up Python Development Environment on Mac OS X [Article]
- Objects in Python [Article]
- Python LDAP Applications: Part 1 - Installing and Configuring the Python-LDAP Library and Binding to an LDAP Directory [Article]
- Python Multimedia [Book]
- Scaling your Application Across Nodes with Spring Python's Remoting [Article]
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
|
|
Post new comment