Python資料庫sqlite3圖文範例詳解

2022-09-18 22:00:36

前言

提到資料庫，大家第一時間想到的可能是 sql 資料庫，這種資料庫非常好用，但是對於新手就不是很容易上手，需要熟悉一段時間才可以大概掌握。這種資料庫在大型的專案開發過程中用到的地方不會很多，但是一些普通的專案還是很實用的，大大減少了程式碼量。

sqlite3資料庫

sqlite3 資料庫是 Python 自帶的資料庫，甚至不需要額外安裝模組，而且操作簡單。

Python + Mysql = SQLite

但是這種資料庫在網上的教學很少，因為我也是最近才知道，所以找了很長時間的資料，

今天終於找齊了，來總結一下。~~真的找了好長時間啊~~

1.需要的模組（只有一個）

import sqlite3

2.模組的使用

首先開啟我們的編譯器（推薦使用vscode，因為資料庫檔案字尾是 .db，vscode裡顯示比較清楚）

2.1建立與資料庫的連線

先放上程式碼

conn = sqlite3.connect('test.db')

使用 sqlite3 的 connect 函數可以建立資料庫或者連線資料庫，

如果這個資料庫存在，就連線這個資料庫，

如果這個庫不存在，就建立資料庫。

右邊小括號裡的是資料庫名。

我們把這個儲存在變數 conn 裡，這個變數名可以自己起

公式：

變數名 = sqlite3.connect( '你要的資料庫名.db' )

2.2建立遊標

還是先放程式碼

cur = conn.cursor()

在 2.1 中，我們建立了與資料庫的連線，我們現在還需要一個遊標來執行 sql 命令，

所以我們要用 conn 的 cursor 函數建立一個遊標。

conn 就是剛才 2.1 中建立的儲存資料庫的變數，你要用你定義的變數名，

再定義一個變數表示這個遊標。

公式：

變數 = 資料庫變數.cursor()

2.3建立 table

先放程式碼

import sqlite3
def check(db_name,table_name):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
    cursor.execute(sql)
    values = cursor.fetchall()
    tables = []
    for v in values:
        tables.append(v[0])
    if table_name not in tables:
        return False # 可以建表
    else:
        return True # 不能建表
conn = sqlite3.connect('資料庫名.db')
#建立一個遊標 cursor
cur = conn.cursor()
if (check("資料庫名.db","Table名") == False):
    sql_text_1 = '''CREATE TABLE 你的table名
            (xx XX,
                xx XX);'''
    # 執行sql語句
    cur.execute(sql_text_1)

程式碼比較長，因為我把 2.1 和 2.2 的兩行程式碼也加了上去。

我們需要 table（表）來存資料，建立 table 的程式碼如下：

變數名 = '''CREATE TABLE 你的table名
            (xx XX,
                xx XX);'''
# 執行sql語句
cur.execute(上面的變數名)

上面是用一個變數儲存了建立 table 的 sql 語句，

下面使用 cur（就是剛才那個遊標）.execute() 函數執行建立 table 的語句。

我們也可以使用 cur.executemany 函數來同時執行多條 sql 語句。

sql 語句的內容，比如建立表的格式是上面這樣的

格式：' ' 'CREATE TABLE 你的 table 名 (xx XX, xx XX);' ' '

這行程式碼裡小寫的 xx 就是你要的屬性名，比如你的資料庫是這樣

姓名	班級
張三	1

其中，屬性名就是 “姓名” 和 “班級”，

小寫的 xx 就應該分別寫姓名和班級（注意，不帶引號）

後面的大寫的XX就是這個屬性所接受的資料的型別，

就相當於Python中的 int 型別和 str 型別。

只不過，我們在 sql 語句中，把 int 型別改成了 NUMBER，把 str 型別改成了 TEXT。

當我們執行這個程式碼，我們的資料夾目錄裡會多出來一個你的資料庫名.db 檔案

當我們再次執行，會發現程式報錯了。

報錯資訊的大概意思是：table 已經存在了。

這是因為我們第一次執行時已經建立了 table ，我們再次執行時，

程式會再次建立同名 table，就會報錯。

所以，我們建立 table 之前要判斷一下這個table存不存在，如果存在就不建立，如果不存在就建立

這個判斷我把它寫成了一個函數，就是我上面程式碼那個 check 函數。

這一步也是我想了好長時間，還找資料找了好長時間才知道的

2.4插入資料

先放程式碼

cur.executemany('INSERT INTO 你的table名 VALUES (?,?)', data)
conn.commit()

其中第一行程式碼中 executemany() 函數的意思就是同時執行多個 sql 語句。

這個函數的括號裡寫的逗號前面就是插入資料的 sql 語句，後面 data 可以是一個列表或者元組。注意，如果是列表的話，必須是列表裡面有若干個元組的形式。

插入資料的 sql 語句的使用：

INSERT INTO 你的table名 VALUES (若干個逗號，用逗號分割)

這裡我們要插入 data 這些資料，所以在括號裡我們使用問號 “?” 來代替這個元素。

大家可以回去看一下 2.3 建立table 的講解，在2.3中，我們建立了兩個屬性，分別是 “姓名” 和 “班級”。因為我們有兩個屬性，所以要有兩個問號。

2.5查詢資料

先放程式碼

def find_tb():
    cur.execute("select * from 你的table名")
    # 提取查詢到的資料
    return cur.fetchall()

這個就很簡單了，我寫這個函數使用時可以把你那個table裡的所有資料都取出來。

第一行是查詢table的 sql 語句，格式是：

select * from 你的table名

下一行再用 fetchall() 函數把資料提取出來，直接 return 即可。

快樂的coding時間！

好了，前面的東西大家應該也都看完了，來點 demo ？

順便說一下，我這個 demo 的靈感來源是最近在網上刷到很多高考查分的視訊，恰好最近在做這個資料庫，所以說我這個 demo 受考試的啟發，就做了一個學生分數系統，其實這個特別爽，可以把自己的分數改成全部滿分！雖然實際沒啥用，但是還是很爽的

程式碼裡沒有我沒講過的部分，大家可以對照上面的講解看程式碼，VScode無報錯執行。

對了，如果有看不懂的可以私信我，不出意外的話一天之內就能回覆。

程式碼：

import sqlite3
import os
def check(db_name,table_name):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
    cursor.execute(sql)
    values = cursor.fetchall()
    tables = []
    for v in values:
        tables.append(v[0])
    if table_name not in tables:
        return False # 可以建表
    else:
        return True # 不能建表
def find_tb():
    cur.execute("select * from scores")
    # 提取查詢到的資料
    return cur.fetchall()
def zcd():
    os.system('cls')
    print("學生分數管理系統")
    print("1.增加學生分數資訊")
    print("2.檢視全部學生分數")
    print("3.查詢分數段內學生分數")
    print("4.退出")
 
if __name__ == '__main__':
    # 建立與資料庫的連線
    conn = sqlite3.connect('stuents_scores.db')
    #建立一個遊標 cursor
    cur = conn.cursor()
    # 如果沒有表則執行建表的sql語句
    if (check("stuents_scores.db","scores") == False):
        sql_text_1 = '''CREATE TABLE scores
                (姓名 TEXT,
                    班級 TEXT,
                    性別 TEXT,
                    語文 NUMBER,
                    數學 NUMBER,
                    英語 NUMBER,
                    總分 NUMBER);'''
        # 執行sql語句
        cur.execute(sql_text_1)
    zcd()
    while True:
        op = int(input("請輸入:"))
        if op == 1:
            S_name = input("請輸入要新增的學生的姓名(如:張三):")
            S_class = input("請輸入要新增的學生的班級(如:一班):")
            S_xb = input("請輸入該學生性別:")
            S_Chinese = int(input("請輸入該學生語文成績(只輸入一個數位,如:82):"))
            S_Maths = int(input("請輸入該學生數學成績(只輸入一個數位,如:95):"))
            S_English = int(input("請輸入該學生英語成績(只輸入一個數位,如:98):"))
            S_gj = S_Maths+S_Chinese+S_English # 總分
            data = [(S_name, S_class, S_xb, S_Chinese, S_Maths, S_English,S_gj)]
            cur.executemany('INSERT INTO scores VALUES (?,?,?,?,?,?,?)', data)
            conn.commit()
            # cur.close()
            # conn.close()
            print("成功!")
            os.system('pause')
            os.system('cls')
            zcd()
        elif op == 2:
            info_list = find_tb()
            print("全部學生資訊(排名不分前後):")
            for i in range(len(info_list)):
                print("第"+str(i+1)+"個:")
                print("學生姓名:"+str(info_list[i][0]))
                print("學生班級:"+str(info_list[i][1]))
                print("學生性別:"+str(info_list[i][2]))
                print("學生語文成績:"+str(info_list[i][3]))
                print("學生數學成績:"+str(info_list[i][4]))
                print("學生英語成績:"+str(info_list[i][5]))
                print("學生總成績:"+str(info_list[i][6]))
                os.system('pause')
                os.system('cls')
                zcd()
        elif op == 3:
            info_list = find_tb()
            fen = int(input("你要要查詢總成績高於n分的學生, 請輸入n:"))
            for i in range(len(info_list)):
                if info_list[i][6] >= fen:
                    print("查詢結果:")
                    print("第"+str(i+1)+"個:")
                    print("學生總成績:"+str(info_list[i][6]))
            os.system('pause')
            os.system('cls')
            zcd()
        elif op == 4:
            os.system('cls')
            break