使用sqlite3建置一個本機資料庫，當然要用mssql或自己掛Docker DB也可以

初始化資料庫

暫時先行，可能後續再依據實際開發狀況修改，以下為資料庫結構創建SQL

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Users" (
	"UID"	INTEGER NOT NULL UNIQUE,
	"TYPE"	INTEGER NOT NULL,
	"NAME"	TEXT NOT NULL,
	"PWDHASH"	TEXT NOT NULL,
	PRIMARY KEY("UID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Cards" (
	"CID"	INTEGER NOT NULL UNIQUE,
	"Bind_User"	INTEGER,
	"Balance"	INTEGER NOT NULL DEFAULT 0,
	"Frozen"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("CID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "transmit_logs" (
	"ID"	INTEGER NOT NULL UNIQUE,
	"TYPE"	INTEGER NOT NULL DEFAULT 0,
	"STATUS"	INTEGER NOT NULL DEFAULT 0,
	"Remark"	TEXT,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Order_logs" (
	"ID"	INTEGER NOT NULL UNIQUE,
	"TID"	INTEGER,
	"Valid"	INTEGER NOT NULL DEFAULT 0,
	"Shipment_Status"	INTEGER NOT NULL DEFAULT 0,
	"Order_INFO"	TEXT,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
COMMIT;

使用Python調用sqlite3

載入資料庫

如果目標路徑沒有檔案，會建立一個新的.DB檔案

import sqlite3 as db
conn = db.connect(env['SQL']['sqlite_URL'])
print(f"load database from {env['SQL']['sqlite_URL']} successfully")

初始化資料庫(執行SQL檔案)

讀取SQL檔案並執行

def exec_sqlfile(conn, fp):
    try:
        with open(fp, 'r') as sql_file:
            sql_script = sql_file.read()
        cursor = conn.cursor()
        cursor.executescript(sql_script)
        conn.commit()
        conn.close()
        print("Execte Script successfully")
        return True
    except Exception as err:
        print(err)
        return False

exec_sqlfile(conn=conn, fp="./data/sql/init.sql")

新增一個測試帳號(插入資料至指定Table)

sqlite3的佔位符是**?**

格式為cursor.execute(str of sqlscript, (變數))，可以參考

def INS_user(conn, user):
    try:
        sql = f"INSERT INTO Users (TYPE, NAME, PWDHASH) VALUES (?, ?, ?)"
        cursor = conn.cursor()
        cursor.execute(sql, (user.type, user.name, user.pwdhash),)
        uid = cursor.lastrowid
        conn.commit()
        conn.close()
        print(f"Execte INSERTR user successfully:{uid}")
        return uid
    except Exception as err:
        print(err)
        return -1

查詢User資料(SELECT from Table)

藉由UID查詢User Table中的紀錄

def quy_user(conn, uid):
  sql = f"SELECT * FROM Users WHERE UID = {uid}"
  print(sql)
  for row in conn.execute(sql):
      print(row)

這邊寫的比較急，剛烤肉回來月半中，後續可能會再慢慢追加forign key與其他資料庫設計，先這樣能用就好