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

Question

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're trying to create a function. You can read it and execute it line-by-line. It seems to be dying because you're asking it to execute the command "CREATE FUNCTION ... DETERMINISTIC" all by itself. — Frank Yellin
– Frank Yellin, Commented Mar 26, 2024 at 1:01
You're splitting the sql code into sections, and then trying to execute each section as its own separate script. Why are you doing that? — John Gordon
– John Gordon, Commented Mar 26, 2024 at 1:31

Bill Karwin · Accepted Answer · 2024-03-26 02:50:03Z

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.

Hi @BillKarwin, thank you very much for your support. I've found a very good solution for the problem thanks to your support. Thumbs up and have a nice day. You restored my hope in finishing my project :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related