用python+MySQL代码写一个寄存酒系统,要有操作人员登录界面,存酒界面,取酒界面、存取酒明细查询
时间: 2023-06-23 12:09:27 浏览: 53
由于题目比较广泛,我会提供一份简单的代码供参考,但需要根据实际需求进行修改和完善。
首先,我们需要安装 `mysql-connector-python` 模块,用于连接 MySQL 数据库。可以使用以下命令进行安装:
```
pip install mysql-connector-python
```
然后,我们创建一个名为 `wine_storage.py` 的 Python 文件,用于实现寄存酒系统。以下是代码实现:
```python
import mysql.connector
from getpass import getpass
# 连接数据库
db = mysql.connector.connect(
host='localhost',
user='root',
password=getpass('Enter your MySQL password: '),
database='wine_storage'
)
# 定义操作人员类
class Operator:
def __init__(self, name, password):
self.name = name
self.password = password
# 登录
def login(self):
cursor = db.cursor()
cursor.execute('SELECT * FROM operators WHERE name=%s AND password=%s', (self.name, self.password))
operator = cursor.fetchone()
if operator:
print('Login successful.')
return True
else:
print('Login failed.')
return False
# 定义存酒类
class Wine:
def __init__(self, name, age, price):
self.name = name
self.age = age
self.price = price
# 存酒
def store(self, operator):
cursor = db.cursor()
cursor.execute('INSERT INTO wines (name, age, price, operator) VALUES (%s, %s, %s, %s)', (self.name, self.age, self.price, operator))
db.commit()
print('Store wine successful.')
# 取酒
def take(self):
cursor = db.cursor()
cursor.execute('SELECT * FROM wines WHERE name=%s AND age=%s AND price=%s ORDER BY id DESC LIMIT 1', (self.name, self.age, self.price))
wine = cursor.fetchone()
if wine:
cursor.execute('DELETE FROM wines WHERE id=%s', (wine[0],))
db.commit()
print('Take wine successful.')
else:
print('Wine not found.')
# 定义存取酒明细查询类
class Record:
def __init__(self, operator=None, wine=None):
self.operator = operator
self.wine = wine
# 查询存取酒明细
def query(self):
cursor = db.cursor()
if self.operator and self.wine:
cursor.execute('SELECT * FROM wines WHERE operator=%s AND name=%s AND age=%s AND price=%s', (self.operator, self.wine.name, self.wine.age, self.wine.price))
elif self.operator:
cursor.execute('SELECT * FROM wines WHERE operator=%s', (self.operator,))
elif self.wine:
cursor.execute('SELECT * FROM wines WHERE name=%s AND age=%s AND price=%s', (self.wine.name, self.wine.age, self.wine.price))
else:
cursor.execute('SELECT * FROM wines')
records = cursor.fetchall()
for record in records:
print(record)
# 主程序
if __name__ == '__main__':
# 创建操作人员表
cursor = db.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS operators (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), password VARCHAR(255))')
# 创建存酒表
cursor.execute('CREATE TABLE IF NOT EXISTS wines (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT, price FLOAT, operator VARCHAR(255))')
# 登录
name = input('Enter your name: ')
password = getpass('Enter your password: ')
operator = Operator(name, password)
if not operator.login():
exit()
# 存酒
name = input('Enter wine name: ')
age = int(input('Enter wine age: '))
price = float(input('Enter wine price: '))
wine = Wine(name, age, price)
wine.store(operator.name)
# 取酒
name = input('Enter wine name: ')
age = int(input('Enter wine age: '))
price = float(input('Enter wine price: '))
wine = Wine(name, age, price)
wine.take()
# 存取酒明细查询
record = Record(operator=operator.name, wine=wine)
record.query()
```
以上代码实现了一个简单的寄存酒系统,包括操作人员登录界面、存酒界面、取酒界面和存取酒明细查询界面。在第一次运行程序时,会自动创建两个表:`operators` 表用于存储操作人员信息,`wines` 表用于存储酒的信息。
在生产环境中,需要对代码进行完善和优化,例如增加异常处理、防止 SQL 注入等。