Python执行sql文件-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_42166308/article/details/110386352

该博客分享了一段Python代码，用于执行SQL文件并连接到MySQL数据库。通过pymysql和sqlalchemy库，实现了读取SQL脚本、逐条执行及数据库连接的自动化。适用于数据库管理和数据迁移场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用Python执行sql文件

直接上代码，粘贴下来配置相应参数后直接使用，喜欢的可以关注博主！！

# coding: utf-8
import pymysql
import warnings
import pandas as pd
from sqlalchemy import create_engine
warnings.filterwarnings('ignore')


DB_USERNAME = ''
DB_PASSWORD = ''
DB_HOST = ''
DB_PORT = '3306'
DB_NAME = 'information_schema'
DB_URI = 'mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8mb4' % (
    DB_USERNAME, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)  # ?charset=utf8mb4
engine = create_engine(DB_URI, encoding='utf8', echo=False)
conn = engine.connect()


def execute_main(database,filepath):
    try:
        db = pymysql.connect("183.84.12.75", "", "", database)
        c = db.cursor()
        with open(filepath,mode='r') as f:
            # 读取整个sql文件，以分号切割。[:-1]删除最后一个元素，也就是空字符串
            sql_list = f.read().split(';')[:-1]

            for x in sql_list:
                line=x.split('\n')
                L=''
                for y in line:
                    l=y.split('#',1)[0].split('-- ',1)[0]
                    L=L+' '+l

                # sql语句添加分号结尾
                sql_item = L+';'
                # print(sql_item)
                c.execute(sql_item)
                print("执行成功sql: %s"%sql_item)
    except Exception as e:
        print(e)
        print('执行失败sql: %s'%sql_item)
    finally:
        # 关闭mysql连接
        c.close()
        db.commit()
        db.close()


res = list(conn.execute('''select routine_name ,routine_schema ,routine_definition from `routines` ;'''))

# for x in res:
    # sql_res = x[2][5:][:-3]
    # filepath = r'C:\Users\admin\Desktop\自动化\sql文件'+'\\'+x[0]+'.sql'
    # with open(filepath,'w') as w:
    #     w.write(sql_res)

    # execute_main(x[1],filepath)

filepath = r'C:\Users\admin\Desktop\自动化\sql文件'+'\\'+'db_pd_店铺_销售日报'+'.sql'
execute_main('hdbi_database',filepath)