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)

    

        
Unlock access to the largest independent learning library in Tech for FREE!
        

            

                
Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.
                
Renews at €14.99/month. Cancel anytime
            
            

                                    
                        
                            START FREE TRIAL
                        
                    
                            
        
    

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;