简易python实现股票管理系统
1.创建gupiao.mysql文件
代码如下:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for gupiao
-- ----------------------------
DROP TABLE IF EXISTS `gupiao`;
CREATE TABLE `gupiao` (
`code` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`chengjiaoliang` varchar(255) DEFAULT NULL,
`zhenfu` varchar(255) DEFAULT NULL,
`zuigao` varchar(255) DEFAULT NULL,
`chengjiaoe` varchar(255) DEFAULT NULL,
`zuidi` varchar(255) DEFAULT NULL,
`liutongshizhi` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of gupiao
-- ----------------------------
INSERT INTO `gupiao` VALUES ('000001', '平安银行', '87380661.00', '3.679', '19.39', '1655173500.00', '18.68', '366962780000.000');
INSERT INTO `gupiao` VALUES ('000002', '万 科A', '63008652.00', '2.733', '30.18', '1864723100.00', '29.36', '285698000000.000');
INSERT INTO `gupiao` VALUES ('000004', '国华网安', '3684202.00', '3.123', '26.10', '94312208.00', '25.28', '2123142700.000');
INSERT INTO `gupiao` VALUES ('000005', '世纪星源', '9381600.00', '2.299', '2.62', '24199218.00', '2.56', '2729501300.000');
INSERT INTO `gupiao` VALUES ('000006', '深振业A', '7947972.00', '2.257', '5.78', '45321045.00', '5.65', '7644906200.000');
INSERT INTO `gupiao` VALUES ('000007', '全新好', '3980776.00', '2.334', '8.25', '32343689.00', '8.06', '2514837100.000');
INSERT INTO `gupiao` VALUES ('000008', '神州高铁', '13182328.00', '1.449', '2.77', '36171292.00', '2.73', '7275947600.000');
INSERT INTO `gupiao` VALUES ('000009', '中国宝安', '22072709.00', '1.662', '7.30', '159613330.00', '7.18', '18373666000.000');
INSERT INTO `gupiao` VALUES ('000010', '美丽生态', '3987000.00', '2.892', '4.17', '16328901.00', '4.05', '2120016300.000');
INSERT INTO `gupiao` VALUES ('000011', '深物业A', '5905600.00', '2.577', '13.60', '78974279.00', '13.25', '6981065700.000');
2.main.py
代码如下:
from prettytable import PrettyTable
import sql as sql
print("欢迎使用股票管理系统!")
# 用于登陆操作
def login():
login_name = input("请输入用户名:")
login_pwd = input("请输入密码:")
if (login_name=="admin"and login_pwd=="admin"):
start_person_system()
else:
print("\033[1;31;0m密码错误!\033[0m")
# 用于查看股票信息
def find_gupiao_info():
x=PrettyTable(["股票代码","名称","成交量","振幅","最高","成交额","最低","流通市值"])
gupiao_data=sql.select_db()
for gupiao in gupiao_data:
tencent=[gupiao[0], gupiao[1], gupiao[2], gupiao[3], gupiao[4], gupiao[5], gupiao[6], gupiao[7]]
x.add_row(tencent)
print(x)
#修改股票信息
def update_gupiao_info():
code = input("请输入需要修改的股票的代码:")
gupiao_data=sql.select_value(code)
if len(gupiao_data)==0:
print("\033[1;31;0m当前的股票代码不存在,请检查输入!\033[0m")
else:
choose = input("是否修改当前 {0} 的数据信息?(y/n)".format(gupiao_data[0][1]))
if choose.lower() == "y":
update_code = input("请输入修改后的股票代码:")
update_name = input("请输入修改后的股票名称:")
update_chengjiaoliang = input("请输入修改后的股票的成交量:")
update_zhenfu = input("请输入修改后的股票的振幅:")
update_zuigao = input("请输入修改后的股票的最高:")
update_chengjiaoe = input("请输入修改后的股票的成交额:")
update_zuidi = input("请输入修改后的股票的最低:")
update_liutongshizhi = input("请输入修改后的股票的流通市值:")
check=sql.update_db(update_code,update_name,update_chengjiaoliang,update_zhenfu,update_zuigao,update_chengjiaoe,update_zuidi,update_liutongshizhi,code)
if int(check)==1:
print("\033[1;31;0m修改股票信息成功!\033[0m")
else:
print("\033[1;31;0m修改股票信息失败,请检查是否编号重复!\033[0m")
else:
pass
# 添加股票信息
def add_gupiao_info():
add_code = input("请输入需要添加的股票代码:")
add_name = input("请输入需要添加的股票名称:")
add_chengjiaoliang = input("请输入需要添加的股票的成交量:")
add_zhenfu = input("请输入需要添加的股票的振幅:")
add_zuigao = input("请输入需要添加的股票的最高:")
add_chengjiaoe = input("请输入需要添加的股票的成交额:")
add_zuidi = input("请输入需要添加的股票的最低:")
add_liutongshizhi = input("请输入需要添加的股票的流通市值:")
check=sql.insert_student(add_code,add_name,add_chengjiaoliang,add_zhenfu,add_zuigao,add_chengjiaoe,add_zuidi,add_liutongshizhi)
if int(check)==1:
print("\033[1;31;0m添加股票信息成功!\033[0m")
else:
print("\033[1;31;0m添加股票信息失败,请检查是否编号重复!\033[0m")
# 通过当前股票的代码删除当前股票的信息
def delete_gupiao_info_by_code():
del_code = input("请输入需要删除股票的代码:")
gupiao_data = sql.select_value(del_code)
if len(gupiao_data) == 0:
print("\033[1;31;0m当前的股票代码不存在,请检查输入!\033[0m")
else:
choose = input("是否刪除当前 {0} 的数据信息?(y/n)".format(gupiao_data[0][1]))
if choose.lower() == "y":
check = sql.delete_db(del_code)
if int(check)==1:
print("\033[1;31;0m股票"+str(gupiao_data[0][1])+"信息删除成功\033[0m")
else:
print("\033[1;31;0m股票"+str(gupiao_data[0][1])+"信息删除失败\033[0m")
else:
pass
# 用于根据code查看股票信息
def find_gupiao_bycode():
code = input("请输入需要查询的股票的代码:")
gupiao_data = sql.select_value(code)
if len(gupiao_data) == 0:
print("\033[1;31;0m当前的股票代码不存在,请检查输入!\033[0m")
else:
x = PrettyTable(["股票代码", "名称", "成交量", "振幅", "最高", "成交额", "最低", "流通市值"])
tencent = [gupiao_data[0][0], gupiao_data[0][1], gupiao_data[0][2], gupiao_data[0][3], gupiao_data[0][4], gupiao_data[0][5], gupiao_data[0][6], gupiao_data[0][7]]
x.add_row(tencent)
print(x)
# 登录后的操作界面
def start_person_system():
while True:
user_input = input("请输入当前需要操作的编号!(输入q或者Q退出)\n1.查看所有股票信息\t2.修改股票信息\t3.添加股票信息\t4.删除股票的信息\t5.根据唯一编号查询股票的信息\n")
if user_input == "1":
find_gupiao_info()
elif user_input == "2":
update_gupiao_info()
elif user_input == "3":
add_gupiao_info()
elif user_input == "4":
delete_gupiao_info_by_code()
elif user_input == "5":
find_gupiao_bycode()
else:
if user_input.lower() == "q":
print("感谢使用本系统!您已退出!")
break
login()
sql.py
代码如下:
import pymysql
db = pymysql.connect(host='localhost',
port=3306,
user='root',
passwd='123456',
db='test')
def select_value(code):
'''查询数据库'''
# 打开数据库连接
sql = "select * from gupiao where code=%s" % code
# 使用 cursor() 方法创建一个游标对象cur
cur = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cur.execute(sql)
# 使用 fetchall() 方法获取查询结果
data = cur.fetchall()
# print(data) # 取出对应的psw值
# 关闭数据库连接
#db.close()
return data
def select_db():
'''查询数据库'''
# 打开数据库连接
sql = "select * from gupiao "
# 使用 cursor() 方法创建一个游标对象cur
cur = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cur.execute(sql)
# 使用 fetchall() 方法获取查询结果
data = cur.fetchall()
# print(data) # 取出对应的psw值
# 关闭数据库连接
#db.close()
return data
def delete_db(code):
'''删除操作'''
# 使用cursor()方法获取操作游标
cur = db.cursor()
sql_delete = "delete from gupiao where code=%s"%code
try:
cur.execute(sql_delete) # 执行
# 提交
db.commit()
return 1
except Exception as e:
#print("操作异常:%s" % str(e))
# 错误回滚
db.rollback()
return 0
#finally:
#db.close()
def update_db(code,name,chengjiaoliang,zhenfu,zuigao,chengjiaoe,zuidi,liutongshizhi,code1):
'''3.更新操作'''
# 使用cursor()方法获取操作游标
cur = db.cursor()
sql_update = "update gupiao set code=%s,name='%s',chengjiaoliang='%s',zhenfu='%s',zuigao='%s',chengjiaoe='%s',zuidi='%s',liutongshizhi='%s' where code=%s"%(code,name,chengjiaoliang,zhenfu,zuigao,chengjiaoe,zuidi,liutongshizhi,code1)
try:
b=cur.execute(sql_update) # 执行sql
# 提交
a=db.commit()
return 1
except Exception as e:
# 错误回滚
#print("错误信息:%s" % str(e))
db.rollback()
return 0
#finally:
#db.close()
def insert_student(code,name,chengjiaoliang,zhenfu,zuigao,chengjiaoe,zuidi,liutongshizhi):
'''插入操作'''
# 使用cursor()方法获取操作游标
cur = db.cursor()
sql_insert= "insert into gupiao (code,name,chengjiaoliang,zhenfu,zuigao,chengjiaoe,zuidi,liutongshizhi) values(%s,'%s','%s','%s','%s','%s','%s','%s')"%(code,name,chengjiaoliang,zhenfu,zuigao,chengjiaoe,zuidi,liutongshizhi)
try:
cur.execute(sql_insert)
# 提交
db.commit()
return 1
except Exception as e:
#print("错误信息:%s" % str(e))
# 错误回滚
db.rollback()
return 0
#finally:
#db.close()
if __name__ == "__main__":
dat=insert_student(1,1,1,1,2,1)
print(dat)