最近学习了一下MySQL的操作，顺便写了一个记账并可视化的小程序。
                                 数据库设计

ID name money summoney datetime

注：该表格在“demo1”数据库下，表格名为：infor

数据库操作类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
class MySql:
    ###构造函数
    def __init__(self,user,password,database,maineig="localhost"):
        self.db=MySQLdb.connect(maineig,user,password,database,charset='utf8')
    ###查询函数
    def Look(self,sql):
        try:
            cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
            cursor.execute(sql)  # 执行sql语言
            result = cursor.fetchall()  # 获取表中数据,其中result是一个元组
            return result
        except:
            print("SEARCH ERROR!")
    ###插入函数
    def Insert(self,sql):
        try:
            cursor=self.db.cursor()
            cursor.execute(sql)
            self.db.commit()  # 提交到数据库执行
        except:
            self.db.rollback()
            print("INSERT ERROR:已经回滚!")
    ###数据库删除信息
    def Delete(self,sql):
        try:
            cursor=self.db.cursor()
            cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
            print("DELETE ERROR:已经回滚!")
    ###数据库更新信息
    def Update(self,sql):
        try:
            cursor=self.db.cursor()
            cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
            print("UPDATE ERROR:已经回滚!")
    ###析构函数
    def __del__(self):
        self.db.close()

数据库操作函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
def DatabaseConduct(ms):
    start = "+------选择功能-------+\n"
    start += "|     1.添加信息      |\n"
    start += "|     2.删除信息      |\n"
    start += "|     3.修改信息      |\n"
    start += "|     4.查询信息      |\n"
    start += "|     5.退出程序      |\n"
    start += "+---------end---------+\n"
    print(start)
    judge = int(input("请输入执行功能:"))
    while (judge != 5):
        if (judge == 1):
            name = input("请输入姓名：")
            money = float(input("请输入金额：(￥)"))
            summoney = ms.Look("select * from infor")[-1][3] + money
            ID = ms.Look("select * from infor")[-1][0] + 1
            dt =datetime.datetime.now()
            sql1 = """insert into infor (ID,name,money,summoney,datetime) VALUES (""" + str(ID) + " , "+'"' + name + '"'+" , " + str(money) + " , " + str(summoney) + " , " +'''"'''+str(dt)+'''"'''+")"
            ms.Insert(sql1)
        elif (judge == 2):
            id = int(input("请输入要删除的行号："))
            sql2="delete from infor where ID = "+str(id)
            ms.Delete(sql2)
            res=ms.Look("select * from infor")
            max=len(res)
            for i in range(id,max+2):
                sqli="update infor set ID = "+str(i-1)+" where ID = "+str(i)
                ms.Update(sqli)
        elif (judge == 3):
            id=input("请输入要更改的行号：")
            change = int(input("请选择要更改的字段：1.name; 2.money; 3.summoney; 4.datetime"))
            item=input("请输入更改后的值：")
            sqlj=""
            if(change==1):
               sqlj="update infor set name = "+ '"'+item+'"'+" where ID = "+id
               ms.Update(sqlj)
            elif(change==2):
                sqlj = "update infor set money = " + item  + " where ID = " + id
                ms.Update(sqlj)
                re=ms.Look("select * from infor")
                print(re)
                max=len(re)
                presum = re[int(id) -2][3]
                presum=presum+re[int(id)-1][2]
                for i in range(int(id)-1,max):
                    presum+=re[i][2]
                    Id=i+1
                    sq="update infor set summoney = " + str(presum)  + " where ID = " + str(Id)
                    ms.Update(sq)
            elif(change==3):
                sqlj = "update infor set summoney = " + item + " where ID = " + id
                ms.Update(sqlj)
            elif(change==4):
                sqlj = "update infor set name = " + '"' + item + '"' + " where ID = " + id
                ms.Update(sqlj)
        elif (judge == 4):
            sql = "select * from infor"
            result = ms.Look(sql)
            for i in result:
                print(i)
        print(start)
        judge = int(input("请输入执行功能:"))

可视化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
def Visual(ms):
    all=ms.Look("select * from infor")
    start = "+------选择功能-------+\n"
    start += "|     1.按年显示      |\n"
    start += "|     2.按月显示      |\n"
    start += "|     3.按日显示      |\n"
    start += "|     4.退出程序      |\n"
    start += "+---------end---------+\n"
    print(start)
    judge = int(input("请输入执行功能:"))
    while (judge !=4):
        if (judge==1):
            strbeginyear=str(all[0][4])[0:4]
            strendyear=str(all[-1][4])[0:4]
            oneyear=int(strbeginyear)
            everymoneys=[]
            summoneys=[]
            thislist = []
            while oneyear <=int(strendyear):
                onest=ms.Look("select * from infor where datetime regexp '"+str(oneyear)+"'")
                if(onest !=()):
                    onemoney = onest[-1][3] - onest[0][3] + onest[0][2]
                    onesum = onest[-1][3]
                    everymoneys.append(onemoney)
                    summoneys.append(onesum)
                    thislist.append(oneyear)
                    oneyear += 1
                else:
                    oneyear+=1
            xtick = (np.arange(len(thislist)))
            plt.subplot()
            matplotlib.rcParams["font.family"] = "KaiTi"
            plt.bar(xtick,everymoneys,0.3,label="各年收支")
            plt.plot(xtick, summoneys, "--r",label="累计收支")
            plt.xlabel("年份",fontproperties="KaiTi",size=14)
            plt.ylabel("金额(￥)",fontproperties="KaiTi",size=14)
            plt.title("各年份收支信息",fontproperties="KaiTi",size=22)
            plt.legend()
            plt.xticks(xtick)
            plt.show()
        elif(judge==2):
            year=input("请输入年份：")
            partyear=ms.Look("select * from infor where datetime regexp '" + year + "'")
            strbeginmonth = str(partyear[0][4])[5:7]
            strendmonth=str(partyear[-1][4])[5:7]
            onemonth = int(strbeginmonth)
            everymoneys = []
            summoneys = []
            monthlist=["1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"]
            thislist=[]
            monthcount=int(strbeginmonth)
            while onemonth <= int(strendmonth):
                onest = ms.Look("select * from infor where datetime regexp '" +year+"-"+ "0*"+str(onemonth) + "'")
                if(onest !=()):
                    onemoney = onest[-1][3] - onest[0][3] + onest[0][2]
                    onesum = onest[-1][3]
                    everymoneys.append(onemoney)
                    summoneys.append(onesum)
                    thislist.append(monthlist[onemonth-1])
                    onemonth += 1
                else:
                    onemonth += 1
            xtick = (np.arange(len(thislist)))
            plt.subplot()
            matplotlib.rcParams["font.family"] = "KaiTi"
            plt.bar(xtick, everymoneys, 0.3,label="各月收支")
            plt.plot(xtick, summoneys, "--r",label="累计收支")
            plt.xlabel("月份", fontproperties="KaiTi", size=14)
            plt.ylabel("金额(￥)",fontproperties="KaiTi", size=14)
            plt.title(year+"年各月份收支信息", fontproperties="KaiTi", size=22)
            plt.xticks(xtick,thislist,fontproperties="KaiTi",size=14)
            plt.legend()
            plt.show()
        elif (judge==3):
            year  = input("请输入年份：")
            month = input("请输入月份：")
            partmonth = ms.Look("select * from infor where datetime regexp '" + year +"-"+"0*"+month+ "'")
            strbeginday = str(partmonth[0][4])[8:10]
            strendday = str(partmonth[-1][4])[8:10]
            oneday = int(strbeginday)
            everymoneys = []
            summoneys = []
            thislist = []
            daycount = int(strbeginday)
            while oneday <= int(strendday):
                onest = ms.Look("select * from infor where datetime regexp '" + year + "-" + "0*" + month +"-0*"+str(oneday)+ "'")
                if (onest != ()):
                    onemoney = onest[-1][3] - onest[0][3] + onest[0][2]
                    onesum = onest[-1][3]
                    everymoneys.append(onemoney)
                    summoneys.append(onesum)
                    thislist.append(oneday)
                    oneday += 1
                else:
                    oneday += 1
            xtick = (np.arange(len(thislist)))
            plt.subplot()
            matplotlib.rcParams["font.family"] = "KaiTi"
            plt.bar(xtick, everymoneys, 0.3,label="各天收支")
            plt.plot(xtick, summoneys, "--r",label="累计收支")
            plt.xlabel("天", fontproperties="KaiTi", size=14)
            plt.ylabel("金额(￥)", fontproperties="KaiTi", size=14)
            plt.title(year+"年"+month+"月每日收支信息", fontproperties="KaiTi", size=22)
            plt.xticks(xtick, thislist, fontproperties="KaiTi", size=14)
            plt.legend()
            plt.show()
        print(start)
        judge = int(input("请输入执行功能:"))

主函数

1
2
3
4
if __name__=="__main__":
    ms=MySql("root","此处填写数据库密码","demo1")
    DatabaseConduct(ms)
    Visual(ms)

实例

在这里插入图片描述
在这里插入图片描述