繼上篇（二）利用json將excel內(nèi)容生成json文件)后，我們已經(jīng)實(shí)現(xiàn)了從excel中讀取內(nèi)容生成json文件丧靡，如果讀出的data數(shù)組有中文，需要特別注意encoding的問題籽暇，并且通過json.dump()生成的json是標(biāo)準(zhǔn)的帶雙引號(hào)的json格式温治，這對(duì)后面解析json也提供了方便。在實(shí)際工作中戒悠，往往也會(huì)有很多場(chǎng)景是接收一些json再在數(shù)據(jù)庫(kù)中進(jìn)行持久化的熬荆，比如在編寫寫網(wǎng)絡(luò)爬蟲應(yīng)用時(shí)，就會(huì)用到本章節(jié)所述的知識(shí)點(diǎn)绸狐。
sqlite是一個(gè)短小精干的功能強(qiáng)大的數(shù)據(jù)庫(kù)卤恳，使用python操作sqlite的語(yǔ)法與操作oracle、mysql類似六孵。為實(shí)現(xiàn)疫情數(shù)據(jù)的持久化纬黎，這里采用sqlite進(jìn)行示意。
1劫窒，創(chuàng)建sqlite數(shù)據(jù)庫(kù)文件
python中自帶了sqlite本今，無需做pip install，可直接這樣操作：
import sqlite3
conn = sqlite3.connect("system.db.sqlite")
會(huì)在當(dāng)前py所在的文件夾下創(chuàng)建system.db.sqlite的文件主巍，此文件既是sqlite3數(shù)據(jù)庫(kù)的數(shù)據(jù)文件冠息。
2，創(chuàng)建員工疫情數(shù)據(jù)庫(kù)表
可以在sqlite admin工具創(chuàng)建孕索，也可以用python創(chuàng)建：
# 創(chuàng)建一個(gè)游標(biāo) curson
cursor = conn.cursor()
# 執(zhí)行一條語(yǔ)句,創(chuàng)建 day_temperature 表
sql = "create table day_temperature (id integer PRIMARY KEY autoincrement, input_date date," \
"dep varchar(60), name varchar(60), is_base varchar(60), city varchar(60), leave_date varchar(60), leave_date_v varchar(60)" \
", back_date varchar(60), address varchar(100), out varchar(60), isOK varchar(60), temperature float, flag1 varchar(60)" \
", flag2 varchar(60), flag3 varchar(60), flag4 varchar(60), flag4_v varchar(60)" \
", v_1 varchar(60), v_2 varchar(60), can_duty varchar(60), can_duty_v varchar(60)" \
", v_3 varchar(60))"
cursor.execute(sql)
3逛艰，創(chuàng)建完數(shù)據(jù)庫(kù)表，然后通過read_json.py文件的data_dict()方法解析的json文件：
start = read_json.JSONReader('ALL2.19.json1')
data = start.data_dict()
得到data[]數(shù)據(jù)搞旭，遍歷data[]形成insert的sql：
for user in data:
dep = user['所在科室']
name = user['姓 名']
is_base = user['是否在漢（填寫是或否）']
city = user['目前所在城市（填寫城市名）']
leave_date = user['離漢時(shí)間/同行人員（從1月22日0時(shí)后開始填寫散怖，如：1月23日/妻子菇绵、女兒）']
leave_date_v = user['離漢交通工具及目的地（填寫類別/車次、航班號(hào)镇眷、私家車牌號(hào)）']
back_date = user['預(yù)計(jì)回漢時(shí)間/同行人員/交通工具（如：1月22日/妻子咬最、女兒/飛機(jī)）']
address = user['當(dāng)日所在具體地址（精確到小區(qū)/街道）']
out = user['當(dāng)日是否外出/外出場(chǎng)所（填寫是或否及詳細(xì)地點(diǎn)）']
isOK = user['是否健康/當(dāng)日體溫（℃）（填寫是或否及當(dāng)日最高體溫）']
temperature = str(re.findall(r"\d+\.?\d*", isOK)[0])
flag1 = user['有無接觸確診（疑似）病例史（填寫有或無/接觸人員情況/接觸時(shí)間）']
flag2 = user['是否普通病例（填寫是或否/癥狀）']
flag3 = user['是否疑似病例（填寫是或否/癥狀）']
flag4 = user['是否確診病例（填寫是或否/癥狀）']
flag4_v = user['確診時(shí)間/機(jī)構(gòu)（填寫確診時(shí)間及診斷機(jī)構(gòu)名稱）']
v_1 = user['目前采取措施（詳細(xì)填寫采取措施、方法）']
v_2 = user['備 注（未盡事項(xiàng)欠动，可備注進(jìn)行說明）']
# can_duty = user['是否能正常到崗']
# can_duty_v = user['未能正常到崗原因']
# v_3 = user['到漢后是否需申請(qǐng)集中隔離']
print(temperature)
i = i+1
# 插入一條記錄
# sql = "insert into day_temperature (input_date,dep, name, is_base,city,leave_date,leave_date_v,back_date," \
# "address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2," \
# "can_duty,can_duty_v,v_3) values (" + "\'"+now_date + "\',\'"+dep + "\',\'" + name + "\',\'" + is_base + "\',\'" + city + "\',\'" + leave_date + "\',\'" + leave_date_v + "\',\'" + back_date + "\',\'" + address + "\',\'" + out + "\',\'" + isOK + "\',\'" + temperature + "\',\'" + flag1 + "\',\'" + flag2 + "\',\'" + flag3 + "\',\'" + flag4 + "\',\'" + flag4_v + "\',\'" + v_1 + "\',\'" + v_2 + "\',\'" + can_duty + "\',\'" + can_duty_v + "\',\'" + v_3 + "\')"
# print(sql)
# cursor.execute(sql)
# 用參數(shù)也可以
line_paras = [now_date, dep, name, is_base, city, leave_date, leave_date_v, back_date, address, out, isOK,
temperature, flag1, flag2, flag3, flag4, flag4_v, v_1, v_2, 'can_duty', 'can_duty_v', 'v_3']
sql = "insert into day_temperature (input_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,can_duty,can_duty_v,v_3) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cursor.execute(sql,[now_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,'can_duty','can_duty_v','v_3'])
此處永乌，在形成sql時(shí)，可以用笨方法具伍，一個(gè)參數(shù)一個(gè)參數(shù)的組裝翅雏，也可以用cursor.execute方法中使用sql的參數(shù)數(shù)組：
sql_paras = []
在for循環(huán)中，形成參數(shù)數(shù)組：
# 用參數(shù)也可以
line_paras = [now_date, dep, name, is_base, city, leave_date, leave_date_v, back_date, address, out, isOK,
temperature, flag1, flag2, flag3, flag4, flag4_v, v_1, v_2, 'can_duty', 'can_duty_v', 'v_3']
sql = "insert into day_temperature (input_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,can_duty,can_duty_v,v_3) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cursor.execute(sql,[now_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,'can_duty','can_duty_v','v_3'])
針對(duì)大量的insert操作人芽，數(shù)據(jù)庫(kù)會(huì)有吞吐和效率的問題望几，一般可以使用cursor.executemany(sql, sql_paras)方法——注意是在for循環(huán)外執(zhí)行：
sql = "insert into day_temperature (input_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,can_duty,can_duty_v,v_3) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cursor.executemany(sql, sql_paras)
4，執(zhí)行完建表和插數(shù)據(jù)后萤厅，記得關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)橄妆。
# 關(guān)閉游標(biāo)：
cursor.close()
# 提交事物
conn.commit()
# 關(guān)閉連接
conn.close()
5，有關(guān)sqlite的基本操作介紹完畢祈坠，下面是sqlite文件夾下data_to_db.py文件的代碼：
import re
import sqlite3
from datetime import datetime
import read_json
conn = sqlite3.connect("system.db.sqlite")
# 創(chuàng)建一個(gè)游標(biāo) curson
cursor = conn.cursor()
# 執(zhí)行一條語(yǔ)句,創(chuàng)建 day_temperature 表
# sql = "create table day_temperature (id integer PRIMARY KEY autoincrement, input_date date," \
# "dep varchar(60), name varchar(60), is_base varchar(60), city varchar(60), leave_date varchar(60), leave_date_v varchar(60)" \
# ", back_date varchar(60), address varchar(100), out varchar(60), isOK varchar(60), temperature float, flag1 varchar(60)" \
# ", flag2 varchar(60), flag3 varchar(60), flag4 varchar(60), flag4_v varchar(60)" \
# ", v_1 varchar(60), v_2 varchar(60), can_duty varchar(60), can_duty_v varchar(60)" \
# ", v_3 varchar(60))"
# cursor.execute(sql)
now_date = datetime.now().strftime('%Y-%m-%d') # 格式為str
start = read_json.JSONReader('ALL2.19.json1')
data = start.data_dict()
sql_paras = []
i = 0
for user in data:
print(i)
dep = user['所在科室']
name = user['姓 名']
is_base = user['是否在漢（填寫是或否）']
city = user['目前所在城市（填寫城市名）']
leave_date = user['離漢時(shí)間/同行人員（從1月22日0時(shí)后開始填寫，如：1月23日/妻子矢劲、女兒）']
leave_date_v = user['離漢交通工具及目的地（填寫類別/車次赦拘、航班號(hào)、私家車牌號(hào)）']
back_date = user['預(yù)計(jì)回漢時(shí)間/同行人員/交通工具（如：1月22日/妻子芬沉、女兒/飛機(jī)）']
address = user['當(dāng)日所在具體地址（精確到小區(qū)/街道）']
out = user['當(dāng)日是否外出/外出場(chǎng)所（填寫是或否及詳細(xì)地點(diǎn)）']
isOK = user['是否健康/當(dāng)日體溫（℃）（填寫是或否及當(dāng)日最高體溫）']
temperature = str(re.findall(r"\d+\.?\d*", isOK)[0])
flag1 = user['有無接觸確診（疑似）病例史（填寫有或無/接觸人員情況/接觸時(shí)間）']
flag2 = user['是否普通病例（填寫是或否/癥狀）']
flag3 = user['是否疑似病例（填寫是或否/癥狀）']
flag4 = user['是否確診病例（填寫是或否/癥狀）']
flag4_v = user['確診時(shí)間/機(jī)構(gòu)（填寫確診時(shí)間及診斷機(jī)構(gòu)名稱）']
v_1 = user['目前采取措施（詳細(xì)填寫采取措施躺同、方法）']
v_2 = user['備 注（未盡事項(xiàng)，可備注進(jìn)行說明）']
# can_duty = user['是否能正常到崗']
# can_duty_v = user['未能正常到崗原因']
# v_3 = user['到漢后是否需申請(qǐng)集中隔離']
print(temperature)
i = i+1
# 插入一條記錄
# sql = "insert into day_temperature (input_date,dep, name, is_base,city,leave_date,leave_date_v,back_date," \
# "address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2," \
# "can_duty,can_duty_v,v_3) values (" + "\'"+now_date + "\',\'"+dep + "\',\'" + name + "\',\'" + is_base + "\',\'" + city + "\',\'" + leave_date + "\',\'" + leave_date_v + "\',\'" + back_date + "\',\'" + address + "\',\'" + out + "\',\'" + isOK + "\',\'" + temperature + "\',\'" + flag1 + "\',\'" + flag2 + "\',\'" + flag3 + "\',\'" + flag4 + "\',\'" + flag4_v + "\',\'" + v_1 + "\',\'" + v_2 + "\',\'" + can_duty + "\',\'" + can_duty_v + "\',\'" + v_3 + "\')"
# print(sql)
# cursor.execute(sql)
# 用參數(shù)也可以
line_paras = [now_date, dep, name, is_base, city, leave_date, leave_date_v, back_date, address, out, isOK,
temperature, flag1, flag2, flag3, flag4, flag4_v, v_1, v_2, 'can_duty', 'can_duty_v', 'v_3']
# sql = "insert into day_temperature (input_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,can_duty,can_duty_v,v_3) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
# cursor.execute(sql,[now_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,can_duty,can_duty_v,v_3])
# 在該for循環(huán)外使用批量添加丸逸，但要先得到sql的參數(shù)值列表sql_paras
sql_paras.append(line_paras)
sql = "insert into day_temperature (input_date,dep, name, is_base,city,leave_date,leave_date_v,back_date,address,out,isOK,temperature,flag1,flag2,flag3,flag4,flag4_v,v_1,v_2,can_duty,can_duty_v,v_3) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cursor.executemany(sql, sql_paras)
sql = 'select * from day_temperature'
cursor.execute(sql)
# 關(guān)閉游標(biāo)：
cursor.close()
# 提交事物
conn.commit()
# 關(guān)閉連接
conn.close()
補(bǔ)充一下：為了獲取“某個(gè)城市有多少員工”蹋艺，單獨(dú)寫了一個(gè)測(cè)試文件，僅供參考（其實(shí)可以作為一個(gè)工具類的方法）
import sqlite3, json
conn = sqlite3.connect("system.db.sqlite")
def get_citys():
data = []
# 創(chuàng)建一個(gè)游標(biāo) curson
cursor = conn.cursor()
sql = 'select city,count(*),group_concat(name) from day_temperature group by city'
cityusers = cursor.execute(sql).fetchall()
citys = []
numbers = []
usernames = []
for cityuser in cityusers:
citys.append(cityuser[0])
numbers.append(cityuser[1])
usernames.append(cityuser[2])
data.append(citys)
data.append(numbers)
data.append(usernames)
print(data)
# 關(guān)閉游標(biāo)：
cursor.close()
return data
# 提交事物
conn.commit()
get_citys()
# 關(guān)閉連接
conn.close()
