数据库实践

def SQLite_Test():
    # =========== 连接数据库 ============
    # 1. 连接本地数据库
    connectA = sqlite3.connect("example.db")
    # 2. 连接内存数据库，在内存中创建临时数据库
    connectB = sqlite3.connect(":memory:")

    # =========== 创建游标对象 ============
    cursorA = connectA.cursor()
    cursorB = connectB.cursor()

    # =========== 创建表 ============
    cursorA.execute("CREATE TABLE class(id real, name text, age real, sex text)")
    cursorB.execute("CREATE TABLE family(relation text, job text, age real)")

    # =========== 插入数据 ============
    cursorA.execute("INSERT INTO class VALUES(1,‘Jock‘,8,‘M‘)")
    cursorA.execute("INSERT INTO class VALUES(2,‘Mike‘,10,‘M‘)")
    # 使用 ? 占位符
    cursorA.execute("INSERT INTO class VALUES(?,?,?,?)", (3,‘Sarah‘,9,‘F‘))

    families = [
        [‘Dad‘, ‘CEO‘, 35],
        [‘Mom‘, ‘singer‘, 33],
        [‘Brother‘, ‘student‘, 8]
    ]
    cursorB.executemany("INSERT INTO family VALUES(?,?,?)",families)

    # =========== 查找数据 ============
    # 使用 命名变量 占位符
    cursorA.execute("SELECT * FROM class WHERE sex=:SEX", {"SEX":‘M‘})
    print("TABLE class: >>>select Male\n", cursorA.fetchone())
    cursorA.close()
    
    cursorB.execute("SELECT * FROM family ORDER BY relation")
    print("TABLE family:\n", cursorB.fetchall())
    cursorB.close()

    # =========== 断开连接 ============
    connectA.close()
    connectB.close()

SQLite_Test()