Create function through MySQLdb

Question

How can I define a multi-statement function or procedure in using the MySQLdb lib in python?

Example:

import MySQLdb

db = MySQLdb.connect(db='service')

c = db.cursor()

c.execute("""DELIMITER //
CREATE FUNCTION trivial_func (radius float) 
    RETURNS FLOAT

    BEGIN
    IF radius > 1 THEN
        RETURN 0.0;
    ELSE
        RETURN 1.0;
    END IF;
END //

DELIMITER ;""")

Which creates the following traceback:

Traceback (most recent call last):
  File "proof.py", line 21, in <module>
    DELIMITER ;""")
  File "build/bdist.macosx-10.5-i386/egg/MySQLdb/cursors.py", line 173, in execute
  File "build/bdist.macosx-10.5-i386/egg/MySQLdb/connections.py", line 35, in defaulterrorhandler
_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 //\nCREATE FUNCTION trivial_func (radius float) \n    RETURNS FLOAT\n\n   ' at line 1")

If I copy the same SQL directly into a mysql shell client, it works as expected

Bill Karwin · Accepted Answer · 2009-04-13 22:15:55Z

21

The DELIMITER command is a MySQL shell client builtin, and it's recognized only by that program (and MySQL Query Browser). It's not necessary to use DELIMITER if you execute SQL statements directly through an API.

The purpose of DELIMITER is to help you avoid ambiguity about the termination of the CREATE FUNCTION statement, when the statement itself can contain semicolon characters. This is important in the shell client, where by default a semicolon terminates an SQL statement. You need to set the statement terminator to some other character in order to submit the body of a function (or trigger or procedure).

CREATE FUNCTION trivial_func (radius float) 
    RETURNS FLOAT

    BEGIN
    IF radius > 1 THEN
        RETURN 0.0; <-- does this semicolon terminate RETURN or CREATE FUNCTION?
    ELSE
        RETURN 1.0;
    END IF;
END

Since the API typically allows you to submit one SQL statement at a time, there's no ambiguity -- the interface knows that any semicolons inside the body of your function definition don't terminate the whole CREATE FUNCTION statement. So there's no need to change the statement terminator with DELIMITER.

edited Apr 13, 2009 at 22:15

answered Apr 13, 2009 at 22:10

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bryan Hunt Over a year ago

unless you need to. for example run a database creation script. is there any way to just pass raw SQL to MySql?

Bill Karwin Over a year ago

@BryanHunt, you can't submit an arbitrary SQL script through the dynamic SQL API, because several commands are recognized only by the mysql client, not the server SQL parser.

Aaron Swan · Accepted Answer · 2016-04-29 02:53:58Z

11

To add to the answer by Bill Karwin, the following python code sample can be used to properly execute a string where DELIMITER is used, such as a database creation script.

import MySQLdb

db = MySQLdb.connect(db='service')
cursor = db.cursor()

dbString = """DELIMITER //
CREATE FUNCTION trivial_func (radius float) 
RETURNS FLOAT

BEGIN
IF radius > 1 THEN
    RETURN 0.0;
ELSE
    RETURN 1.0;
END IF;
END //

DELIMITER ;"""

# Find special delimiters
delimiters = re.compile('DELIMITER *(\S*)',re.I)
result = delimiters.split(dbString)

# Insert default delimiter and separate delimiters and sql
result.insert(0,';') 
delimiter = result[0::2]
section   = result[1::2]

# Split queries on delimiters and execute
for i in range(len(delimiter)):
    queries = section[i].split(delimiter[i])
    for query in queries:
        if not query.strip():
            continue
        cursor.execute(query)

This will execute one delimited statement at a time, changing delimiters when needed.

edited Apr 29, 2016 at 2:53

answered Jun 5, 2013 at 22:27

Aaron Swan

1,3461 gold badge14 silver badges20 bronze badges

1 Comment

Lundy Over a year ago

This did the trick for me. I also needed to add query = query.replace('%', '%%'), to escape % characters in my .sql file.

DenisH · Accepted Answer · 2015-02-06 14:39:21Z

Based on the comment from @AaronS. This script will read in an SQL file, split it into discrete SQL commands and cope with whatever delimiters it finds.

    queries = []
    delimiter = ';'
    query = ''
    with open('import.sql', 'r') as f:
        for line in f.readlines():
            line = line.strip()
            if line.startswith('DELIMITER'):
                delimiter = line[10:]
            else:
                query += line+'\n'
                if line.endswith(delimiter):
                    # Get rid of the delimiter, remove any blank lines and add this query to our list
                    queries.append(query.strip().strip(delimiter))
                    query = ''

    for query in queries:
        if not query.strip():
            continue
        cursor.execute(query)
    cursor.close()

nayaku · Accepted Answer · 2024-05-15 06:54:25Z

0

Based on the comment from @DenisH. It can solve multiple statements specified in one SQL command string.

def execute_muti(cursor, sql_commands):
    queries = []
    delimiter = ';'
    query = ''
    for line in sql_commands.split('\n'):
        line = line.strip()
        if line.lower().startswith('delimiter'):  # Find special delimiters
            delimiter = line[10:].strip()
        else:
            query += line + '\n'
            if line.endswith(delimiter):
                query = query.strip()[:-len(delimiter)]
                queries.append(query)
                query = ''
    for query in queries:
        if not query.strip():
            continue
        results = cursor.execute(query, multi=True)
        for result in results:
            if result.with_rows:
                print("Rows produced by statement '{}':".format(result.statement))
                print(result.fetchall())
            else:
                print("Number of rows affected by statement '{}': {}".format(
                    result.statement,
                    result.rowcount
                ))

The following example shows how to use it:

import mysql.connector

with mysql.connector.connect(host='localhost', port=3306, user='root', password='test') as db:
    with db.cursor() as cursor:
        sql = 'INSERT YOUR SQL CODE HERE!!'
        execute_muti(cursor, sql)
    db.commit()

edited May 15, 2024 at 6:54

answered May 15, 2024 at 6:48

nayaku

12 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Create function through MySQLdb

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related