很多情況下我們需要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.connectorwith 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 delimitersdelimiter = 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():continueresults = cursor.execute(query, multi=True) # Execute multiple statementsfor 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()
