（三）利用sqlite將json數(shù)據(jù)集按照表結(jié)構(gòu)持久化操作

繼上篇（二）利用json將excel內(nèi)容生成json文件)后，我們已經(jīng)實(shí)現(xiàn)了從excel中讀取內(nèi)容生成json文件，如果讀出的data數(shù)組有中文，需要特別注意encoding的問(wèn)題，并且通過(guò)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，無(wú)需做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ù)表，然后通過(guò)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í)后開(kāi)始填寫，如：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['有無(wú)接觸確診（疑似）病例史（填寫有或無(wú)/接觸人員情況/接觸時(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)行說(shuō)明）']
   # 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ì)有吞吐和效率的問(wèn)題，一般可以使用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í)后開(kāi)始填寫，如：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['有無(wú)接觸確診（疑似）病例史（填寫有或無(wú)/接觸人員情況/接觸時(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)行說(shuō)明）']
    # 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()
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容