Python执行MYSQL SQL文件

admin2024-05-15  0

很多情况下我们需要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.executeDELIMITER支持不太好,需要手动切割出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()
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明原文出处。如若内容造成侵权/违法违规/事实不符,请联系SD编程学习网:675289112@qq.com进行投诉反馈,一经查实,立即删除!