通过MySQLdb创建函数

DELIMITER 命令是一个内置的MySQL shell客户端,只有该程序(和MySQL查询浏览器)才能识别它。没有必要使用

分隔符 CREATE FUNCTION 语句,当语句本身可以包含分号字符时。这在shell客户机中很重要,默认情况下,分号终止SQL语句。为了提交函数体(或触发器或过程),需要将语句终止符设置为其他字符。

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

分隔符 .

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)

根据@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()