python执行sql文件

发布时间：2019-09-03 09:27:58编辑：auto阅读（2821）

最近遇到一对需要执行的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"])

关键字：