[day9] 建置SQL DB

使用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与其他资料库设计，先这样能用就好