教材征订信息管理系统与职业发展：技术实现与库存管理实践

import sqlite3

# 连接数据库
conn = sqlite3.connect('textbook.db')
cursor = conn.cursor()

# 创建教材表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        isbn TEXT NOT NULL,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        publisher TEXT NOT NULL,
        price REAL NOT NULL,
        stock INTEGER NOT NULL
    )
''')

# 创建用户表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        role TEXT NOT NULL
    )
''')

# 创建订单表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        book_id INTEGER NOT NULL,
        quantity INTEGER NOT NULL,
        order_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (book_id) REFERENCES books(id)
    )
''')

conn.commit()
conn.close()
      

def place_order(user_id, book_id, quantity):
    conn = sqlite3.connect('textbook.db')
    cursor = conn.cursor()

    # 查询教材库存
    cursor.execute('SELECT stock FROM books WHERE id = ?', (book_id,))
    stock = cursor.fetchone()[0]

    if stock >= quantity:
        # 更新库存
        new_stock = stock - quantity
        cursor.execute('UPDATE books SET stock = ? WHERE id = ?', (new_stock, book_id))

        # 插入订单
        cursor.execute('INSERT INTO orders (user_id, book_id, quantity) VALUES (?, ?, ?)',
                       (user_id, book_id, quantity))
        conn.commit()
        print("订单提交成功，库存已更新。")
    else:
        print("库存不足，无法完成订单。")

    conn.close()
      

def place_order_with_transaction(user_id, book_id, quantity):
    conn = sqlite3.connect('textbook.db')
    cursor = conn.cursor()

    try:
        # 开始事务
        conn.execute('BEGIN')

        # 查询库存
        cursor.execute('SELECT stock FROM books WHERE id = ?', (book_id,))
        stock = cursor.fetchone()[0]

        if stock >= quantity:
            # 更新库存
            new_stock = stock - quantity
            cursor.execute('UPDATE books SET stock = ? WHERE id = ?', (new_stock, book_id))

            # 插入订单
            cursor.execute('INSERT INTO orders (user_id, book_id, quantity) VALUES (?, ?, ?)',
                           (user_id, book_id, quantity))

            # 提交事务
            conn.commit()
            print("订单提交成功，库存已更新。")
        else:
            print("库存不足，无法完成订单。")
            conn.rollback()  # 回滚事务
    except Exception as e:
        print(f"发生错误：{e}")
        conn.rollback()
    finally:
        conn.close()