execute many sql statements at the same time with sqlalchemy

Question

I can execute MySQL statements one by one with sqlalchemy.

from sqlalchemy.sql import text

ipass = "xxxx"
mysql_ip = "xxxx"

link = create_engine("mysql+pymysql://root:{}@{}:3306/?charset=utf8".format(ipass,mysql_ip))

sql1 = text("use dbname;")
sql2 = text("truncate table table_name;")
sql3 = text("alter table table_name auto_increment=1;")

link.execute(sql1)
link.execute(sql2)
link.execute(sql3)

This works fine. I want all three mysql statements to run in one execute command:

sql = text("""
use dbname;
truncate table table_name;
alter table table_name auto_increment=1;
""")
result = link.execute(sql)

The error message returns:

You have an error in your SQL syntax...

Is there no way to batch multiple SQL statements in sqlalchemy?

How to add client_flag=CLIENT.MULTI_STATEMENTS in create_engine statement then?

Answer 1

@scrapy, based on this code, you can add client_flag=CLIENT.MULTI_STATEMENTS flag this way:

from pymysql.constants import CLIENT
from sqlalchemy import create_engine

...

create_engine(
    f"mysql+pymysql://root:{ipass}@{mysql_ip}:3306/?charset=utf8",
    connect_args={"client_flag": CLIENT.MULTI_STATEMENTS}
)

...