无法通过Python执行mysql脚本 - 'CREATE FUNCTION' 或 'CREATE TRIGGER

Question

我在使用之前写的SQL脚本时遇到了一些麻烦。我想用Python代码读取并执行这些脚本。这里是我简单的MySQL脚本，里面的DELIMITER命令已经去掉了：

DROP FUNCTION IF EXISTS `employee_db`.`func_create_token`;

CREATE FUNCTION `employee_db`.`func_create_token`() RETURNS VARCHAR(100) DETERMINISTIC
BEGIN
    DECLARE `chars` VARCHAR(26);
    DECLARE `charLen` INT;
    DECLARE `randomPassword` VARCHAR(100);
    
    SET `chars` = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    SET `charLen` = LENGTH(`chars`);
    SET `randomPassword` = '';

    WHILE LENGTH(`randomPassword`) < 12 DO
        SET `randomPassword` = CONCAT(`randomPassword`, SUBSTRING(`chars`, CEILING(RAND() * `charLen`), 1));
    END WHILE;

    RETURN `randomPassword`;
END;

我可以直接执行这个脚本，函数也能成功创建，没问题。但是，当我尝试在Python中读取这个SQL文件并执行时，总是会出现一些我解决不了的错误。下面是我写的Python函数：

# Function to execute sql file
def execute_sql_script(path_to_sql: os.PathLike[str], title: str) -> None:
    
    # Read sql file from folder `sql`
    try:
        with open(path_to_sql, 'r') as f:
            queries : list = f.read().split(';')
            queries : list = [q.strip() for q in queries if q.strip()]
        f.close()
    except FileNotFoundError as f:
        print(color.BOLD + color.RED + f'File couldn\'t be read. Please check error: {e}' + color.END)
        
    # Execute sql script with progress bar
    try:
        with alive_bar(len(queries), dual_line = True, title = title, force_tty = True) as bar:
            for _, query in enumerate(queries):
                sleep(.05)
                cursor.execute(query)
                bar()
        cursor.commit()
    except Error as e:
        print(color.BOLD + color.RED + f'Error in executing sql script. Check error: {e}' + color.END)

错误信息并没有提供太多帮助：

Create Function - Employee |███▋⚠︎                                   | (!) 1/11 [
[1m[91mError in executing sql script. Check error: 1064 (42000): 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 '' at line 3[0m

有趣的是，其他创建表的脚本执行得非常顺利，没有任何异常。

Create Function - Employee |████████████████████████████████████████| 27/27 [100

mysql 数据库错误数据库编程 sql脚本 create function create trigger sql执行函数创建

无法通过Python执行mysql脚本 - 'CREATE FUNCTION' 或 'CREATE TRIGGER

1 个回答

撰写回答