以下是一个简单的数据库货存控制系统的Python代码实现，使用SQLite作为关系型数据库：
```python
import sqlite3
# 连接数据库
conn = sqlite3.connect('inventory.db')
# 创建产品表格
conn.execute('''CREATE TABLE IF NOT EXISTS Product
(id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
category TEXT NOT NULL)''')
# 创建供应商表格
conn.execute('''CREATE TABLE IF NOT EXISTS Supplier
(id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
contact TEXT,
phone TEXT,
email TEXT)''')
# 创建购买订单表格
conn.execute('''CREATE TABLE IF NOT EXISTS PurchaseOrder
(id INTEGER PRIMARY KEY,
supplier_id INTEGER NOT NULL,
order_date DATE NOT NULL,
status TEXT NOT NULL,
FOREIGN KEY (supplier_id) REFERENCES Supplier(id))''')
# 创建入库记录表格
conn.execute('''CREATE TABLE IF NOT EXISTS StockIn
(id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
in_date DATE NOT NULL,
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
FOREIGN KEY (product_id) REFERENCES Product(id))''')
# 创建销售记录表格
conn.execute('''CREATE TABLE IF NOT EXISTS Sales
(id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
sale_date DATE NOT NULL,
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
FOREIGN KEY (product_id) REFERENCES Product(id))''')
# 创建其他费用记录表格
conn.execute('''CREATE TABLE IF NOT EXISTS OtherCost
(id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
cost_date DATE NOT NULL,
type TEXT NOT NULL,
amount REAL NOT NULL,
FOREIGN KEY (order_id) REFERENCES PurchaseOrder(id))''')
# 插入数据
conn.execute("INSERT INTO Product (id, name, description, category) VALUES (1, '衬衫', '白色长袖衬衫', '服装')")
conn.execute("INSERT INTO Product (id, name, description, category) VALUES (2, '牛奶', '纯牛奶', '食品')")
conn.execute("INSERT INTO Product (id, name, description, category) VALUES (3, '笔记本', 'A5硬面笔记本', '文具')")
conn.execute("INSERT INTO Supplier (id, name, contact, phone, email) VALUES (1, '供应商A', '张三', '123456789', 'supplierA@example.com')")
conn.execute("INSERT INTO Supplier (id, name, contact, phone, email) VALUES (2, '供应商B', '李四', '987654321', 'supplierB@example.com')")
conn.execute("INSERT INTO PurchaseOrder (id, supplier_id, order_date, status) VALUES (1, 1, '2021-01-01', '已发货')")
conn.execute("INSERT INTO PurchaseOrder (id, supplier_id, order_date, status) VALUES (2, 2, '2021-02-01', '未发货')")
conn.execute("INSERT INTO StockIn (id, product_id, in_date, quantity, unit_price) VALUES (1, 1, '2021-01-05', 100, 50.0)")
conn.execute("INSERT INTO StockIn (id, product_id, in_date, quantity, unit_price) VALUES (2, 2, '2021-02-05', 200, 3.0)")
conn.execute("INSERT INTO Sales (id, product_id, sale_date, quantity, unit_price) VALUES (1, 1, '2021-03-01', 50, 80.0)")
conn.execute("INSERT INTO Sales (id, product_id, sale_date, quantity, unit_price) VALUES (2, 3, '2021-04-01', 100, 10.0)")
conn.execute("INSERT INTO OtherCost (id, order_id, cost_date, type, amount) VALUES (1, 1, '2021-01-10', '运费', 100.0)")
conn.execute("INSERT INTO OtherCost (id, order_id, cost_date, type, amount) VALUES (2, 2, '2021-02-15', '杂费', 50.0)")
# 提交事务
conn.commit()
# 查询某一类产品的库存数量
cursor = conn.execute("SELECT SUM(quantity) FROM StockIn WHERE product_id IN (SELECT id FROM Product WHERE category = '服装')")
print("服装库存数量:", cursor.fetchone()[0])
# 查询某一产品的库存数量
cursor = conn.execute("SELECT SUM(quantity) FROM StockIn WHERE product_id = 1")
print("衬衫库存数量:", cursor.fetchone()[0])
# 查询某一时间段内的销售总额
cursor = conn.execute("SELECT SUM(quantity * unit_price) FROM Sales WHERE sale_date BETWEEN '2021-01-01' AND '2021-12-31'")
print("2021年销售总额:", cursor.fetchone()[0])
# 查询某一时间段内的其他费用总额
cursor = conn.execute("SELECT SUM(amount) FROM OtherCost WHERE cost_date BETWEEN '2021-01-01' AND '2021-12-31'")
print("2021年其他费用总额:", cursor.fetchone()[0])
# 关闭数据库连接
conn.close()
```
该代码中首先使用`sqlite3`库连接到名为`inventory.db`的SQLite数据库，然后使用`execute`函数创建了产品、供应商、购买订单、入库记录、销售记录和其他费用记录六个表格。接着，使用`execute`函数向表格中插入了一些示例数据。最后，使用`execute`函数执行了四个查询，分别查询了某一类产品的库存数量、某一产品的库存数量、某一时间段内的销售总额和某一时间段内的其他费用总额，并使用`fetchone`函数获取查询结果。最后使用`close`函数关闭了数据库连接。
1924
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
