python连接mysql执行任意sql文件

python连接mysql执行任意sql文件功能主要以下：

连接mysql

执行任意mysql

程序打包任意python3环境可以执行

源代码：

import pymysql
import sys
import pandas as pd
from tabulate import tabulate

class Con(object):
    def __init__(self,host,user,password,database,port):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = int(port)

    def sql_exec(self):
        # 打开数据库连接
        conn = pymysql.connect(host=self.host,user=self.user,password=self.password,database=self.database,port=self.port)
        try:
            # 使用cursor()方法获取操作游标
            cursor = conn.cursor()
            args = sys.argv
            sql_file_path = args[1]
            sql_file = open(sql_file_path, 'r+')
            sql_commands = sql_file.readlines()
            for sql_command in sql_commands:
                if sql_command.strip():
                    cursor.execute(sql_command)
                    conn.commit()
                    data = cursor.fetchall()
                    column_names = [i[0] for i in cursor.description]
                    header = [i[0] for i in cursor.description]
                    df = pd.DataFrame(list(data), columns=header)  # pd.DataFrem 对列表具有更好的兼容性
                    print(df.to_string())

                    #print(sql_command)
                    #print(tabulate(result, headers=column_names, tablefmt="grid"))
                    #for i in result:
                     #   print(i)
            print("SQL文件执行完毕")
        finally:
            # 关闭数据库连接
            cursor.close()
            conn.close()
            print("MySQL连接已关闭")

if __name__ == '__main__':
    host="127.0.0.1"
    user="ceshi"
    password="123456"
    database="ceshi"
    port="3306"
    s=Con(host,user,password,database,port)
    s.sql_exec()