Python执行数据库脚本

Python执行sql脚本

#!/usr/bin/env python3
# coding: utf-8
import pymysql
  
try:
    conn = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',port = 3306,db = 'test',charset = 'utf8')
    cur = conn.cursor()
    with open('test.sql',encoding='utf-8',mode='r') as file:
        # 读取整个sql文件，以分号切割。[:-1]删除最后一个元素，也就是空字符串
        sql_list = file.read().split(';')[:-1]
        for sql in sql_list:
            # 判断包含空行的
            if '\n' in sql:
                sql = sql.replace('\n', ' ')
            if '    ' in sql:
                sql = sql.replace('    ', '')
            # sql语句添加分号结尾
            sql_item = sql+';'
            #print(sql_item)
            cur.execute(sql_item)
            print("执行成功sql : %s"%sql_item)
except Exception as e:
    print(e)
    print('执行失败sql : %s'%sql_item)
finally:
    # 关闭mysql连接
    cur.close()
    conn.commit()
    conn.close()