発音暗記アプリの実装（３）・・SQL

では、起動時を想定してDBからデータ読み込んでみます。
SQLの書き方の確認です。DBには例によってこのデータが入っている。

まず、単語データが全部で何個あるか？（項目総数）

import sqlite3
mydb = "mydb.sqlite3"
con =sqlite3.connect(mydb)
cur = con.cursor()
cur.execute("SELECT COUNT(*) FROM t_shengci") #項目総数
row = cur.fetchone()
print(row[0])
con.close()

levelが0のデータはいくつあるか

import sqlite3
mydb = "mydb.sqlite3"
con =sqlite3.connect(mydb)
cur = con.cursor()
cur.execute("SELECT COUNT(*) FROM t_shengci WHERE level = 0") #項目総数
row = cur.fetchone()
print(row[0])
con.close()

データの取得（一気に複数行取り出す場合 fetchall）

import sqlite3
mydb = "mydb.sqlite3"
con =sqlite3.connect(mydb)
cur = con.cursor()
cur.execute("SELECT id, hanzi, pinyin, level, timestamp FROM t_shengci") 
rows = cur.fetchall()
for row in rows:
    tempid, temphanzi, temppinyin, templevel, temptimestamp = row
    print(tempid, temphanzi, temppinyin, templevel, temptimestamp)
con.close()

これでデータ取得できています。（並べ替えの効果を見るために、DB Browserで日時とレベルをいじっています。）

45 韭菜盒子 jiǔ cài hé zi 2 2024-04-16 12:37:48
46 高麗菜盒子 gāo lí cài hé zi 1 2024-04-16 16:37:48
47 酸菜盒子 suān cài hé zi 0 2024-04-16 12:37:48
48 蘿蔔絲捲 luó bo sī juǎn 1 2024-04-16 13:37:48
49 豆腐捲 dòu fu juǎn 2 2024-04-16 14:37:48
50 豬肉餡餅 zhū ròu xiàn bǐng 1 2024-04-16 12:37:48
51 牛肉捲餅 niú ròu juǎn bǐng 2 2024-04-16 12:37:48
52 豬肉捲餅 zhū ròu juǎn bǐng 2 2024-04-16 15:37:48

暗記アプリなら、覚えるべき優先順位は、Levelが最も低く、かつ、同じレベルの中で一番古い単語だろうということでSQL のORDER BYで並べ替え。

import sqlite3
mydb = "mydb.sqlite3"
con =sqlite3.connect(mydb)
cur = con.cursor()
cur.execute("SELECT id, hanzi, pinyin, level, timestamp FROM t_shengci \
            ORDER BY level ASC, timestamp ASC") 
rows = cur.fetchall()
for row in rows:
    tempid, temphanzi, temppinyin, templevel, temptimestamp = row
    print(tempid, temphanzi, temppinyin, templevel, temptimestamp)
con.close()

これで実行すると

47 酸菜盒子 suān cài hé zi 0 2024-04-16 12:37:48
50 豬肉餡餅 zhū ròu xiàn bǐng 1 2024-04-16 12:37:48
48 蘿蔔絲捲 luó bo sī juǎn 1 2024-04-16 13:37:48
46 高麗菜盒子 gāo lí cài hé zi 1 2024-04-16 16:37:48
45 韭菜盒子 jiǔ cài hé zi 2 2024-04-16 12:37:48
51 牛肉捲餅 niú ròu juǎn bǐng 2 2024-04-16 12:37:48
49 豆腐捲 dòu fu juǎn 2 2024-04-16 14:37:48
52 豬肉捲餅 zhū ròu juǎn bǐng 2 2024-04-16 15:37:48

レベルの低い順で、かつ同じレベルの中では古い順に並びかえられいるのが分かります。1行だけ取ってくるなら

import sqlite3
mydb = "mydb.sqlite3"
con =sqlite3.connect(mydb)
cur = con.cursor()
cur.execute("SELECT id, hanzi, pinyin, level, timestamp FROM t_shengci \
            ORDER BY level ASC, timestamp ASC") 
row = cur.fetchone()
tempid, temphanzi, temppinyin, templevel, temptimestamp = row
print(tempid, temphanzi, temppinyin, templevel, temptimestamp)
con.close()

これでよさそうですね。
では、この画面

の表示内容を実際のデータで置き換えることを想定して、とりあえず必要なデータを拾ってくる関数を用意します。

import sqlite3
mydb = "mydb.sqlite3"

def flashcard():   
    con =sqlite3.connect(mydb)
    cur = con.cursor()
    cur.execute("SELECT COUNT(*) FROM t_shengci")
    row = cur.fetchone()
    totalcount = row[0]

    cur.execute("SELECT COUNT(*) FROM t_shengci WHERE level = 0") 
    row = cur.fetchone()
    level0count =row[0]

    cur.execute("SELECT COUNT(*) FROM t_shengci WHERE level = 1") 
    row = cur.fetchone()
    level1count =row[0]
    
    cur.execute("SELECT COUNT(*) FROM t_shengci WHERE level = 2") 
    row = cur.fetchone()
    level2count =row[0]
    
    cur.execute("SELECT COUNT(*) FROM t_shengci WHERE level = 3") 
    row = cur.fetchone()
    level3count =row[0]
    
    cur.execute("SELECT id, hanzi, pinyin, level, timestamp FROM t_shengci \
                ORDER BY level ASC, timestamp ASC") 
    row = cur.fetchone()
    tempid, temphanzi, temppinyin, templevel, temptimestamp = row
    res = tempid, temphanzi, temppinyin, templevel,totalcount, \
        level0count,level1count,level2count,level3count
    con.close()
    return(res)

関数内関数使って整理しました。

import sqlite3
mydb = "mydb.sqlite3"

def flashcard():
    con =sqlite3.connect(mydb)
    cur = con.cursor()

    def getrow(SQLstr:str):
        cur.execute(SQLstr)
        return cur.fetchone()
    
    totalcount = getrow("SELECT COUNT(*) FROM t_shengci")[0]
    level0count = getrow("SELECT COUNT(*) FROM t_shengci WHERE level = 0")[0]
    level1count = getrow("SELECT COUNT(*) FROM t_shengci WHERE level = 1")[0]
    level2count = getrow("SELECT COUNT(*) FROM t_shengci WHERE level = 2")[0]
    level3count = getrow("SELECT COUNT(*) FROM t_shengci WHERE level = 3")[0]
    tempid, temphanzi, temppinyin, templevel, temptimestamp \
        = getrow("SELECT id, hanzi, pinyin, level, timestamp FROM t_shengci \
                 ORDER BY level ASC, timestamp ASC")
    res = tempid, temphanzi, temppinyin, templevel,totalcount, \
        level0count,level1count,level2count,level3count
    con.close()
    return(res)

使ってみた例（抜粋）

tempid, temphanzi, temppinyin, templevel,totalcount, \
        level0count,level1count,level2count,level3count = flashcard()
print(tempid) 
print(temphanzi)
print(temppinyin)
print(totalcount)
print(level2count)

これを実行すると、

47
酸菜盒子
suān cài hé zi
8
4

とデータとれています。表示に必要なデータを取ってくる道具立てが揃いました。