积分系统

1.Python代码

import pymysql
import easygui
import mysql_function
class INTEGRAL_FUNCTOIN ():
    
    def __init__ (self,MYSQL):                                                    #自动执行打开封装mysql
        self.conn = pymysql.connect(host="localhost",user="root",password="1451964253",database="优惠记录")
        self.cursor = self.conn.cursor()
        self.print_condition = {"1":self.integral_query,"2":self.integral_join,"3":self.user_join,"4":self.satisfy_modify_if}
        self.MYSQL = MYSQL.mysql
        
    def integral_query(self):                                               #获得姓名进行查找
        while True:
            message = ['姓名']
            name = student = easygui.multenterbox('查询积分：', '积分系统', message)
            if None == name:
                break
            self.cursor.execute(self.MYSQL["QUERY"],name)
            result = self.cursor.fetchone()
            if result == None:
                easygui.msgbox("暂未有几分",'积分查询','Yes')
                continue
            else:
                print_ = ",".join(result[:-1]),",积分：",str(result[-1])
                easygui.msgbox(print_,'积分查询','Yes')
                
    def integral_join(self):                                                #加入积分，输入积分和姓名添加到mysql中
        while True:
            message = ['姓名',"积分"]
            name = student = easygui.multenterbox('添加积分：', '积分系统', message)
            if None == name:
                break
            try:
                r = self.cursor.execute(self.MYSQL["JOIN_INTEGRAL"],[name[1],name[0]])
                self.conn.commit()
            except Exception:
                easygui.msgbox("没有该用户！",'积分查询','Yes')
    def user_join(self):                                                    #用户添加如果有新用户，就可以加入
        while True: 
            message = ['姓名',"电话"]
            name = student = easygui.multenterbox('添加用户：', '积分系统', message)
            if None == name:
                break
            self.cursor.execute(self.MYSQL["USER_QUERY"],[name[0],name[1]])           #调出判断是否存在此用户
            result = self.cursor.fetchone()
            try:
                if name[0] == result[0]:
                    easygui.msgbox("用户以存在！",'添加用户','Yes')
            except Exception:
                r = self.cursor.execute(self.MYSQL["JOIN_USER"],[name[0],name[1]]) #不存在再添加
                self.conn.commit()
                easygui.msgbox("用户以添加",'添加用户','Yes')
                    
    def satisfy_modify_if (self):
        while True:
            message = ['姓名']
            name = student = easygui.multenterbox('输入姓名后即可删除！', '积分系统', message)
            if None == name:
                break                                             #大于就让他清零
            easygui.msgbox("恭喜你你的积分",'满足查询','Yes')
            self.cursor.execute(self.MYSQL["DELETE"] ,[name])   
            self.conn.commit()          
            easygui.msgbox("已清理",'添加用户','Yes')
                
    def mysql_sign_in (self,alias):             #判断进入
        self.print_condition[alias]()
        self.cursor.close()   
        self.conn.close()

while True:
    tuple = ('1','2','3',"4")
    command = easygui.buttonbox("""查看信息请按：1\n添加记录请按：2\n添加用户请按：3\n条件满足的数据：4""",'积分系统',tuple)
    if command == None:
        break
    obj = INTEGRAL_FUNCTOIN(mysql_function)
    obj.mysql_sign_in(command)

2.自定文件 mysql_function

mysql = {"QUERY":"select name,telephone_number,sum(integral) from integral_table left join user_table on integral_table.uid = user_table.user_id where user_table.name =%s group by user_id"
,"DELETE":"update integral_table set integral = 0 where uid = (SELECT user_id FROM user_table WHERE name = %s )"
,"JOIN_INTEGRAL": "insert integral_table (time,integral,uid) values (now(),%s,(select user_id from user_table where name = %s))"
,"USER_QUERY": "select name,telephone_number from user_table where name =%s and telephone_number =%s"
,"JOIN_USER": "insert user_table (name,telephone_number) values (%s,%s)"
,"satisfy_999":"select name ,telephone_number,integral from user_table left join (select uid ,sum(integral) as integral from integral_table group by uid) as number on user_table.user_id = number.uid where number.integral >= 999"}

3.mysql

3.1 用户表

CREATE TABLE `user_table`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `telephone_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

3.2 积分表

CREATE TABLE `integral_table`  (
  `record_id` int(11) NOT NULL AUTO_INCREMENT,
  `time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  `integral` int(255) NULL DEFAULT NULL,
  `uid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`record_id`) USING BTREE,
  INDEX `user_link`(`uid`) USING BTREE,
  CONSTRAINT `user_link` FOREIGN KEY (`uid`) REFERENCES `user_table` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 41 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;