库存可用数量

import sqlite3

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

# 创建库存表
cursor.execute('''
CREATE TABLE IF NOT EXISTS inventory (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    quantity INTEGER
)
''')

# 插入初始库存数据
cursor.execute("INSERT INTO inventory (product_name, quantity) VALUES ('Product A', 100)")
cursor.execute("INSERT INTO inventory (product_name, quantity) VALUES ('Product B', 50)")

# 提交更改
conn.commit()

# 查询库存可用数量
def get_available_quantity(product_name):
    cursor.execute("SELECT quantity FROM inventory WHERE product_name = ?", (product_name,))
    result = cursor.fetchone()
    if result:
        return result[0]
    else:
        return 0

# 更新库存数量
def update_inventory(product_name, quantity_change):
    cursor.execute("SELECT quantity FROM inventory WHERE product_name = ?", (product_name,))
    current_quantity = cursor.fetchone()[0]
    new_quantity = current_quantity + quantity_change
    cursor.execute("UPDATE inventory SET quantity = ? WHERE product_name = ?", (new_quantity, product_name))
    conn.commit()

# 示例：查询Product A的库存可用数量
print(f"Available quantity of Product A: {get_available_quantity('Product A')}")

# 示例：更新Product A的库存数量
update_inventory('Product A', -10)
print(f"Updated available quantity of Product A: {get_available_quantity('Product A')}")

# 关闭数据库连接
conn.close()