python - Python pymysql : cannot run multiple statements

Question

I am working on a project which needs to read a input.sql file and source all the commands in it. It was working fine for all other statements, but for procedures it is not working.

You can try to replicate it yourself using these inputs.

CREATE TABLE IF NOT EXISTS products( 
Prod_id INT AUTO_INCREMENT PRIMARY KEY, 
Name VARCHAR(255) NOT NULL, 
Price INT NOT NULL, 
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Insert into products(Name,Price) Values('soap',1);
Insert into products(Name,Price) Values('toilet paper',2);
Insert into products(Name,Price) Values('Choco taco',3);

CREATE PROCEDURE GetAllProducts() 
BEGIN     
SELECT * FROM products; 
END;

But while I am executing a create procedure and call statements, I am unable to do so. Can anyone please help me out?

a few statements as below but, it is not working

with connection.cursor() as cursor:

    for statement in script.split(';'): 

        if len(statement) > 0:

            cursor.execute(statement + ';')

I understand that the issue is because of the ; delimiter, But how can I make sure the whole procedure gets executed?

Cursor of pymysql seems to execute one statement at a time and doesn't allow multiple execution.?

标签： pythonmysqlpython-3.xpymysql

Answer 1

因为您要拆分;，所以该过程将作为两个单独的语句执行：

BEGIN
SELECT * FROM products;

和

END;

这两个都是无效的 SQL。