python执行sql文件

最近遇到一对需要执行的sql文件，sql文件内是insert 语句。如下：

INSERT INTO hs_his.stock_industry VALUES ('采掘', '000006', '深振业A');INSERT INTO hs_his.stock_industry VALUES ('采掘', '000409', '山东地矿');INSERT INTO hs_his.stock_industry VALUES ('采掘', '000552', '靖远煤电');INSERT INTO hs_his.stock_industry VALUES ('采掘', '000571', '新大洲A');INSERT INTO hs_his.stock_industry VALUES ('采掘', '000629', '*ST钒钛');INSERT INTO hs_his.stock_industry VALUES ('采掘', '000655', '金岭矿业');

本来需要插入的表并不多，仅七八张，手动执行下也很快。但是实施人员给过来的sql文件，一张表的数据根据数据量硬生生生成了近10个文件。文件多了，若手动执行，很容易出现遗漏或者重复操作，造成错误。
由于文件内结构比较单一，故用脚本实现。代码如下：

def execute_sql(conn, cur, path=r"D:\个人"):    """执行指定目录下的.sql文件"""    os.chdir(path)    for each in os.listdir("."):        count = 0   #读取行数        sql = ""    #拼接的sql语句        if "hisdatastock_replace.sql" in each:            with open(each, "r", encoding="utf-8") as f:                for each_line in f.readlines():                    # 过滤数据                    if not each_line or each_line == "\n":                        continue                    # 读取2000行数据，拼接成sql                    elif count < 2000:                        sql += each_line                        count += 1                    # 读取达到2000行数据，进行提交，同时，初始化sql，count值                    else:                        cur.execute(sql)                        conn.commit()                        sql = each_line                        count = 1                # 当读取完毕文件，不到2000行时，也需对拼接的sql 执行、提交                if sql:                    cur.execute(sql)                    conn.commit()

以上execute_sql函数，会默认执行入参path路径下，所有文件名包含“.sql”文件。
如下，写一个连接配置数据的类。

class Connect_mysql:    """Get Configuration and Connect to Mysql!"""    def __init__(self):        setup_logging()    def get_config(self,file_name="config"):        """Get Configuration!"""        with open(file_name, "r", encoding="utf-8") as f:            config = json.load(f)        return config    def conn_mysql(self, host, port, user, password, database, charset="utf8"):        """Connetct to Mysql."""        logger = logging.getLogger(self.__class__.__name__)        try:            conn = pymysql.connect(host=host, port=port, user=user, password=password,  database=database, charset=charset)            cur = conn.cursor()            return conn, cur        except Exception as e:            logger.info('Connect to mysql Error!')            logger.error(e)

get_config函数是获取数据库配置。文件”config“数据库配置如下：

{    "dev":{        "host":"aaa",        "port":3306,        "user":"aaa",        "password":"aaa",        "database":"aaa",        "charset":"utf8"        },    "localhost":{        "host":"localhost",        "port":3306,        "user":"root",        "password":"123456",        "database":"ifs_test1",        "charset":"utf8"        },    "sit":{        "host":"ccc",        "port":3306,        "user":"ccc",        "password":"ccc",        "database":"ccc",        "charset":"utf8"        }}

运行代码：

conn_sql = Connect_mysql()config = conn_sql.get_config()  # get configuration# Connect to mysql，若需切换数据库，只要替换“dev”为所需数据库即可conn, cur = conn_sql.conn_mysql(config["dev"]["host"], config["dev"]["port"], config["dev"]["user"],                                config["dev"]["password"], config["dev"]["database"], config["dev"]["charset"])