Python學習筆記 | 使用SQLite3子產品靈活高效實作小型資料庫應用

常見的資料庫系統都比較龐大且操作複雜，需要在目标電腦上安裝資料庫軟體才能夠正常使用，通常應用于大型資料庫項目開發。而一些小型資料資訊管理項目，資料量小，不需要過高的安全強度，如果使用大型資料庫來開發就顯得大材小用了，無形中還增添了開發難度。

正是基于這種情況，Python語言内置了 一套輕量級資料庫系統，具有小巧、高效、可靠的優點，能夠滿足很大一部分人的開發需求！

一、SQLite3子產品簡介

SQLite3是Python的一個内置子產品，是一套開源嵌入式關系資料庫系統，它不需要獨立的伺服器程序，資料庫就是一個.db字尾的檔案，可以跨平台直接通路，非常便捷。詳細特點如下：

• 無需安裝、零配置、不需要啟動服務
• 不需要單獨檔案系統，資料庫存儲在單一檔案中
• 支援事務操作
• 伺服器和用戶端在同一程序空間運作，不需要獨立的伺服器程序
• 小巧，靈活，高效，可靠

二、SQLite3子產品的使用方法

1、基本步驟

• 導入SQLite3子產品
• 連接配接資料庫
• 建立遊标對象
• 編寫SQL語句
• 執行SQL語句
• 送出事務
• 關閉遊标
• 關閉資料庫連接配接

示範代碼如下：

# 導入sqlit3子產品
import sqlite3

# 建立連接配接，建立或打開資料庫
conn = sqlite3.connect('demo.db')

# 建立遊标對象
cur = conn.cursor()

# 編制SQL語句，建立一個包含三個字段的表，其中pno是自動遞增的，不需要輸入
sql = '''create table if not exists t_person(
        pno INTEGER primary key autoincrement,
        pname VARCHAR not null,
        age INTEGER)'''

# 執行sql語句，編寫sql語句很容易出錯，是以采取異常處理
try:
    cur.execute(sql)  # 執行sql語句
    print('建立表成功！')
except Exception as err:
    print(err)
    print('建立表失敗！')
finally:
    cur.close()  # 關閉遊标
    conn.close()  # 關閉連接配接           

2、向表中插入資料

import sqlite3

conn = sqlite3.connect('demo.db')
cur = conn.cursor()

# t_person(pname,age)表示向這兩個字段插入資料，values(?,?)裡的問号是先占位
# 在調用時再給出具體值，這樣處理不僅靈活，還可以防止sql注入
sql = 'insert into t_person(pname,age) values (?,?)'

try:
    cur.execute(sql, ('張三', 23))  # 插入一行資料
    cur.executemany(sql,[('李四',24),('王五',22),('趙六',25)])  # 插入多行資料
    conn.commit()  # 送出事務，向表中寫入
    print('插入資料成功！')
except Exception as err:
    print(err)
    print('插入資料失敗！')
    conn.rollback()  # 事務復原，傳回到執行sql語句前的狀态
finally:
    cur.close()
    conn.close()           

3、從表中讀取資料

import sqlite3

conn = sqlite3.connect('demo.db')
cur = conn.cursor()
sql = 'select * from t_person'

try:
    cur.execute(sql)
    # fetchall方法用于取得所有結果，fetchone是取得一條結果
    result = cur.fetchall()
    for i in result:
        print(i)
except Exception as err:
    print(err)
    print('查詢失敗！')
finally:
    cur.close()
    conn.close()           

4、修改表中資料

import sqlite3

conn = sqlite3.connect('demo.db')
cur = conn.cursor()
sql = 'update t_person set age=? where pno=?'
try:
    cur.execute(sql, (26, 1))  # 修改指定序号的年齡内容
    conn.commit()
    print('修改資料成功！')
except Exception as err:
    print(err)
    print('修改資料失敗！')
    conn.rollback()
finally:
    cur.close()
    conn.close()           

5、删除表中資料

import sqlite3

conn = sqlite3.connect('demo.db')
cur = conn.cursor()
sql = 'delete from t_person where pno=?'  # 删除指定序号的一行資料
try:
    cur.execute(sql, (2,))  # 元組隻有一個值時，必須在值後面加逗号
    conn.commit()
    print('删除資料成功！')
except Exception as err:
    print(err)
    print('删除資料失敗！')
    conn.rollback()
finally:
    cur.close()
    conn.close()           

6、其它常用操作

• 查詢資料庫中包含的所有表

import sqlite3

conn = sqlite3.connect('demo.db')
cur = conn.cursor()

sql = 'select name from sqlite_master where type="table"'
cur.execute(sql)
tables = cur.fetchall()
print(tables)           

• 删除資料庫中的表

cur.execute('drop table tablename')           

• 查詢表結構

cur.execute('pragma table_info(t_person)')
print(cur.fetchall())           

• 查詢前10條記錄

cur.execute('select * from t_person limit 0,10')           

• 查詢表中不重複字段

cur.execute('select distinct pname from t_person')           

7、技巧分享

當建立了資料庫之後，在pycharm界面左側邊欄目前項目下方會顯示資料庫名稱，滑鼠左鍵輕按兩下需要操作的資料庫名稱，就會彈出資料庫控制台console視窗，在這個視窗裡面就可以直接輸入SQL語句，點選控制台視窗的左上角的綠色運作按鈕就會執行SQL語句，用來調試資料庫非常友善，免得反複寫完整的python代碼進行調試輸出。

并且，在資料庫控制台裡書寫SQL代碼時，會有代碼輔助提示，免得記不住關鍵字而反複查閱。可以在控制台裡書寫并調試SQL語句，然後再将SQL語句複制粘貼到程式代碼中，友善快捷，確定代碼執行無誤！

資料庫控制台界面如下：

SQL控制台界面

三、綜合執行個體

現在對上一篇“辦公自動化之使用python-docx子產品操作Word文檔”中的綜合執行個體進行改良，這裡要求從員工檔案裡提取相關資訊，寫入到資料庫中進行管理。案例如下：

某公司有幾十或幾百員工，每名員工都有一份word文檔的檔案資訊表，文檔格式均相同，見下圖：

個人檔案資訊表

現在想建立一個資料庫，用于管理人員基本資訊，要求内容有姓名、性别、出生年月等8項内容，這些内容在員工檔案資訊表裡都有，如果采取向資料庫裡錄入的形式，工作量太大，還容易出錯，這裡用程式來實作！

代碼如下：

import sqlite3
from docx import Document
from pathlib import Path


# 自定義函數，傳回word表格指定行列的單元格内容
def result(row, col):
    cell = table.rows[row].cells
    return cell[col].text


# 建立資料庫，庫名dossier.db，表名t_person
# 字段：pno序号，pname姓名，gender性别，birth出生年月，nation民族，parth入黨時間，job參加工作事件，pro職稱，school畢業院校
conn = sqlite3.connect('dossier.db')
cur = conn.cursor()
sql = '''create table if not exists t_person(pno INTEGER primary key autoincrement,
        pname VARCHAR not null,gender VARCHAR,birth VARCHAR,nation VARCHAR,parth VARCHAR,
        job VARCHAR,pro VARCHAR,school VARCHAR)'''
try:
    cur.execute(sql)
except Exception as err:
    print(err)
    print('建立表失敗！')

# 擷取所有檔案資訊檔案名
path = Path('檔案資訊')  # 建立路徑對象
files = list(path.glob('*.docx'))  # 周遊指定路徑下所有docx檔案，并轉換成清單

# 從個人檔案檔案中讀取相關資訊并寫入到資料庫中
for file in files:  # 周遊有得到的所檔案
    doc = Document(file)  # 打開文檔
    table = doc.tables[0]  # 指定讀取的表格
    # 利用自定義函數result讀取文檔中指定位置的内容，放置到元組中
    person =(result(0, 1), result(0, 3), result(0, 5), result(1, 1), result(2, 1), result(2, 3), result(3, 1),
           result(4, 5))

    # 将個人資訊寫入到資料庫中
    sql = 'insert into t_person(pname,gender,birth,nation,parth,job,pro,school) values (?,?,?,?,?,?,?,?)'
    try:
        cur.execute(sql, person)  # 插入一行資料
        conn.commit()  # 送出事務，向表中寫入
    except Exception as err:
        print(err)
        print('插入資料失敗！')
        conn.rollback()  # 事務復原，傳回到執行sql語句前的狀态

# 關閉資料庫相關對象
cur.close()
conn.close()
print('資料寫入完畢！！！')           

程式執行後，生成資料庫，檢視表内容如下：

建立的人員資訊表