通过MySQLdb创建函数

Question

我该如何在Python中使用MySQLdb库定义一个包含多个语句的函数或过程呢？

举个例子：

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

这会产生以下错误信息：

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

如果我把相同的SQL直接复制到mysql的命令行客户端，它就能正常工作。

Answer 1

根据@AaronS的评论，这段脚本会读取一个SQL文件，把它拆分成独立的SQL命令，并处理它找到的任何分隔符。

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

Answer 2

为了补充Bill Karwin的回答，下面这段Python代码可以用来正确执行包含分隔符的字符串，比如数据库创建脚本。

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)

这段代码会一次执行一个用分隔符分开的语句，并在需要的时候更改分隔符。

Answer 3

DELIMITER 命令是 MySQL 的一个内置功能，只能在 MySQL 的命令行工具（还有 MySQL 查询浏览器）中使用。如果你通过其他方式直接执行 SQL 语句，其实不需要用到 DELIMITER。

DELIMITER 的主要作用是帮助你避免在写 CREATE FUNCTION 语句时产生混淆，因为这个语句里面可能会包含分号。分号在 MySQL 中默认是用来结束一个 SQL 语句的，所以在命令行工具中，如果不改变结束符，MySQL 会误认为你写的语句已经结束了。为了提交一个函数（或者触发器、过程）的主体，你需要把结束符改成其他字符。

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

而通过 API 提交 SQL 语句时，通常一次只提交一个语句，这样就不会产生混淆了——接口会知道你函数定义中的分号并不是用来结束整个 CREATE FUNCTION 语句的。所以在这种情况下，就不需要用 DELIMITER 来改变结束符了。