problem executing an SQL command in Python with sqlalchemy

Question

I'm trying to run an sql commands with sqlalchemy in python, all the queries I'm trying to do are running successfully besides one, and the error I get is about syntax. when I take the code the an sql environment it works successfully. Here is the code I'm trying to run (this part is working!):

con.execute("USE covid19_korea")
con.execute("SET @currdate := (select min(confirmed_date) from facts)")
con.execute("SET @enddate :=  (select DATE_ADD((select max(confirmed_date) from facts), INTERVAL 14 DAY))")

From this part it doesn't work:

query = """
        delimiter $$
        DROP PROCEDURE IF EXISTS BuildDate$$
        CREATE PROCEDURE BuildDate()
            BEGIN
                 WHILE @currdate < @enddate DO
                    INSERT INTO Dimension_Date (date, day, month, year, day_name)
                    VALUES (
                             @currdate, DAY(@currdate), MONTH(@currdate), YEAR(@currdate), DAYNAME(@currdate)
                            );
                    SET @currdate:= DATE_ADD(@currdate, INTERVAL 1 DAY);
                END WHILE;

            END$$
        CALL BuildDate();"""

con.execute(query)

and here is the error I get:

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 $$\n        DROP PROCEDURE IF EXISTS BuildDate$$\n        CREATE PROCEDU' at line 1")**
[SQL: 
        delimiter $$
        DROP PROCEDURE IF EXISTS BuildDate$$
        CREATE PROCEDURE BuildDate()
            BEGIN
                 WHILE @currdate < @enddate DO
                    INSERT INTO Dimension_Date (date, day, month, year, day_name)
                    VALUES (
                             @currdate, DAY(@currdate), MONTH(@currdate), YEAR(@currdate), DAYNAME(@currdate)
                            );
                    SET @currdate:= DATE_ADD(@currdate, INTERVAL 1 DAY);
                END WHILE;

            END$$
        CALL BuildDate();]
(Background on this error at: http://sqlalche.me/e/f405)

From the top of my head `delimiter ` is a mysql command line client command, not an actual SQL command. — Ilja Everilä, Apr 14 '20 at 09:45
Dropping off a reference that might be of help: https://stackoverflow.com/questions/10455547/delimiter-creating-a-trigger-in-sqlalchemy. As @Ilja Everilä points out, the delimiters is probably the issue here. — Jake Tae, Apr 14 '20 at 09:47
I also thought that the delimiter is the issue but didnt know how to handle with it :( — Oren Normand, Apr 14 '20 at 09:53
After getting rid of `delimiter $$`, send the DROP, CREATE, and CALL statements separately using `conn.execute()`. You may have to do some hand holding in case of calling the stored procedure, since SQLAlchemy's autocommit cannot know that it is a data changing operation: https://stackoverflow.com/questions/51561894/calling-stored-function-or-procedure-wont-insert-and-persist-changes — Ilja Everilä, Apr 14 '20 at 09:53
thank you!!! it now does not crash anymore, but from some reason it doesnt do the CALL statement.. thats how i seperated it: con.execute("DROP PROCEDURE IF EXISTS BuildDate") query = """CREATE PROCEDURE BuildDate() .... """ con.execute(query) con.execute("CALL BuildDate()") but when i look at the table in the db its empty, the function is not really called. — Oren Normand, Apr 14 '20 at 10:14
See my previous comment; it most likely does execute your CALL, but it does not commit the changes. SQLAlchemy's autocommit—if you're using it—relies on identifying statements that make changes, but it cannot identify a call to a stored procedure as such. — Ilja Everilä, Apr 14 '20 at 10:20

problem executing an SQL command in Python with sqlalchemy

0 Answers0