Unable to execute mysql script - 'CREATE FUNCTION' or 'CREATE TRIGGER' via Python

I do have troubles with my sql scripts which I've wrote priorely and try to read with my python code and also execute it. Here my simple mysql script, where the DELIMITER command is removed:

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;

I'm able to execute it directly and the function is created. No problems so far. But trying to read the sql file in python and executing it then, leads always to errors which I somehow can't solve. Below you see the python function:

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

The error message is not very helpful:

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

Curiously the execution of every other script which creates the tables does work fantastically and without any exceptions.

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

You read the SQL script, split that input into individual SQL statements on the ';' character, and execute each of these statements.

But MySQL routines contain a ; character in their body. If you split the input on the ; character, you get this:

CREATE FUNCTION `employee_db`.`func_create_token`() RETURNS VARCHAR(100) DETERMINISTIC
BEGIN
    DECLARE `chars` VARCHAR(26);

That's not a complete CREATE FUNCTION statement. It terminates prematurely at the first ; character, because that's what you told it to split the input on. The SQL parser expects the BEGIN to have an END, but it doesn't.

This is why simply splitting on ; is not sufficient. You would need to keep track of any BEGIN...END blocks, including nested blocks.

You would also need to parse the input to make sure you aren't finding a ; character that's part of a string literal, or inside a comment, or even part of an SQL identifier.

You'd basically need a full-blown SQL parser, not simply f.read().split(';').

A second alternative is to do what the MySQL client does when it reads an SQL script: you'd need to support the DELIMITER command, and use delimiters in your SQL script that are not ;, and not any other character found in the body of the stored routine. You'd need to parse the input line by line, until you found the current delimiter (since it won't be ;).

The third alternative is to forget about writing code that "runs" an SQL script, and instead just fork a sub-process to run the mysql client, which already does the parsing necessary.

It's up to you. You can spend hours or days writing a parser that does this in Python. Or you could run the mysql client and be done with your task in five minutes.