基于python与MySQL的记账可视化小程序

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

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

数据库操作类

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 resultexcept: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()

数据库操作函数

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] + moneyID = ms.Look("select * from infor")[-1][0] + 1dt =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 = "+idms.Update(sqlj)elif(change==2):sqlj = "update infor set money = " + item  + " where ID = " + idms.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+1sq="update infor set summoney = " + str(presum)  + " where ID = " + str(Id)ms.Update(sq)elif(change==3):sqlj = "update infor set summoney = " + item + " where ID = " + idms.Update(sqlj)elif(change==4):sqlj = "update infor set name = " + '"' + item + '"' + " where ID = " + idms.Update(sqlj)elif (judge == 4):sql = "select * from infor"result = ms.Look(sql)for i in result:print(i)print(start)judge = int(input("请输入执行功能:"))

可视化

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 += 1else:oneyear+=1xtick = (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 += 1else:onemonth += 1xtick = (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 += 1else:oneday += 1xtick = (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("请输入执行功能:"))

主函数

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

实例

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！