推荐订阅源

import sqlite3

#with sqlite3.connect('test.db') as conn:

with sqlite3.connect(':memory:') as conn:

    cursor = conn.cursor()

    # 3. 执行SQL：创建表

    create_sql = '''

    CREATE TABLE IF NOT EXISTS student (

        id INTEGER PRIMARY KEY AUTOINCREMENT,

        name TEXT NOT NULL,

        age INTEGER,

        score REAL

    )

    '''

    cursor.execute(create_sql)

    # 4. 执行SQL：新增数据（单条）

    cursor.execute("INSERT INTO student (name, age, score) VALUES (?, ?, ?)", ('张三', 18, 95.5))

    # 批量新增（executemany + 列表元组）

    data_list = [('李四', 19, 92.0), ('王五', 17, 88.5), ('赵六', 18, 90.0)]

    cursor.executemany("INSERT INTO student (name, age, score) VALUES (?, ?, ?)", data_list)

    # 5. 提交事务（增/删/改必须提交，查询无需）

    conn.commit()

    print("数据操作成功！")

    # 6. 执行SQL：查询数据

    cursor.execute("SELECT * FROM student WHERE age >= 18")

    # 获取查询结果：fetchall()（所有）/ fetchone()（单条）/ fetchmany(n)（n条）

    results = cursor.fetchall()

    print("查询结果：")

    for row in results:

        print(f"ID：{row[0]}, 姓名：{row[1]}, 年龄：{row[2]}, 成绩：{row[3]}")