Pass stored procedure definition to singlestore/memsql via SQLAlchemy-CodePudding

I have a stored procedure stored (ha) in file stored_procedure.sql. If I open this file in a SQL editor software, I can define the procedure and call it in either Python or SQL. The procedure looks like:

CREATE OR REPLACE PROCEDURE 
test_proc (some_date VARCHAR(10)) 
RETURNS INT AS
  BEGIN
    INSERT INTO db.test
    -- join TABLE 1 and TABLE 2 for final results
    SELECT some_date;
RETURN 1;
END //
DELIMITER ;

However, I would like for Python to be able to read in the procedure file, and pass the stored procedure to the database (if stored procedure gets updated but not re-ran manually, code picks up the re-defining of the procedure).

When I read the stored procedure into a text stream and try to execute:

from sqlalchemy import create_engine
conn = create_engine(parameters_to_connect_to_database)
statement = open('stored_procedure.sql').read()

trans = conn.begin()
conn.execute(statement)
trans.commit()

I the following syntax error for conn.execute(statement):

ProgrammingError: (pymysql.err.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 OR REPLACE PROCEDURE

I know the sql script doesn't actually have a syntax error in it because it runs ok manually if I'm in a sql editor software. If I swap the stored procedure file with a file that has a create table or insert into statement in it, no error is returned and insertion takes successfully.

How can I edit the above to execute both files with a SQL statement contained in them, as well as files with stored procedures written in them?

CodePudding user response：

DELIMITER is not an SQL statement. It is a command to help the MySQL shell avoid confusion about how to interpret ; when it can appear inside the stored procedure definition which is itself part of a CREATE PROCEDURE statement. When running a CREATE PROCEDURE statement outside of the shell, DELIMITER is not required (or valid).

This fails:

import sqlalchemy as sa

engine = sa.create_engine("mysql pymysql://scott:tiger@localhost:3307/mydb")

drop_sp = "DROP PROCEDURE IF EXISTS test_proc"
create_sp = """\
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`()
BEGIN
   SELECT 'this is a test';
END //
DELIMITER ;
"""

with engine.begin() as conn:
    conn.exec_driver_sql(drop_sp)
    conn.exec_driver_sql(create_sp)

but this works

import sqlalchemy as sa

engine = sa.create_engine("mysql pymysql://scott:tiger@localhost:3307/mydb")

drop_sp = "DROP PROCEDURE IF EXISTS test_proc"
create_sp = """\
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`()
BEGIN
   SELECT 'this is a test';
END
"""

with engine.begin() as conn:
    conn.exec_driver_sql(drop_sp)
    conn.exec_driver_sql(create_sp)