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