Python执行MYSQL SQL文件

很多情况下我们需要Python来执行SQL文件，但是一般库没有提供这些功能，直接执行经常会出错，这里分析各种情况下执行SQL语句的处理。如果你没有时间的话，直接跳转查看[第三点](#3. 包含DELIMITER的语句)。

准备工作

这里采用**mysql-connector连接MySQL，如果还没有安装可以使用pip命令来安装mysql-connector**:

pip install mysql-connector-python
// 国内加速
pip install mysql-connector-python -i https://pypi.mirrors.ustc.edu.cn/simple/

可以通过以下例子连接数据库，具体例子可以查看官方教程：

import mysql.connector

with mysql.connector.connect(host='127.0.0.1', port=3306, user='root', password='123456') as db:

1.单条SQL语句

单条SQL语句直接执行即可：

sql = 'INSERT INTO t1 VALUES ()'
with db.cursor() as curosr:
    cursor.execute('INSERT INTO t1 VALUES ()')
db.commit()

2. 多条SQL语句

多条SQL语句需要设置multi为True，否则会出现mysql.connector.errors.DatabaseError: 2014(HY000): Commands out of sync; you can't run this command now错误。官方例子：

sql = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
with db.cursor() as curosr:
    for result in cursor.execute(operation, multi=True):
    	if result.with_rows:
            print("Rows produced by statement '{}':".format(
              result.statement))
            print(result.fetchall())
      	else:
			print("Number of rows affected by statement '{}': {}".format(
              result.statement, result.rowcount))
db.commit()

3. 包含DELIMITER的语句

很多情况下执行的SQL文件中会包含DELIMITER，然而 cursor.execute对DELIMITER支持不太好，需要手动切割出SQL语句，如果直接执行，会出现mysql.connector.errors.ProgrammingError: 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 'delimiter $$错误。参考帖子：

def execute_muti(cursor, sql_commands):
    queries = []
    delimiter = ';'
    query = ''
    for line in sql_commands.split('\n'):
        line = line.strip()
        if line.lower().startswith('delimiter'):  # Find special delimiters
            delimiter = line[10:].strip()
        else:
            query += line + '\n'
            if line.endswith(delimiter):
                query = query.strip()[:-len(delimiter)]
                queries.append(query)
                query = ''
    for query in queries:
        if not query.strip():
            continue
        results = cursor.execute(query, multi=True)  # Execute multiple statements
        for result in results:
            if result.with_rows:
                print("Rows produced by statement '{}':".format(result.statement))
                print(result.fetchall())
            else:
                print("Number of rows affected by statement '{}': {}".format(
                    result.statement,
                    result.rowcount
                ))

with db.cursor() as cursor:
    sql = 'delimiter $$\nCREATE PROCEDURE test()\nBEGIN\nSELECT * FROM test;\nEND $$\n' # 假设这个语句从文件中读取
    execute_muti(cursor, sql)
db.commit()