Python 读取 支付宝账单并存储到 Access 中
我有一个很多年前自己写的C#+Access的记账程序，用了很多年，现在花钱的机会多了，并且大部分走的支付宝，于是就想把账单从支付宝网站上下载下来，直接写入到Access，这样就很省心了。
记账程序是长这个样子的：
还有报表汇总模块儿：
Access 主要表结构如下：
支付宝支付流水下载下来如下：
具体代码如下：
table="收支记录表" path="C:\\Users\\user\\Desktop\\tmp\\alipay_record_20190114.xlsx" #test_connect() read_excel(path)
#-*-coding:utf-8 -*- import pypyodbc import xlrd from ClassDef import * import CommonUtil import traceback
def read_excel(path): workbook=xlrd.open_workbook(path) sheet=workbook.sheet_by_index(0) nrows=sheet.nrows ncols=sheet.ncols print(nrows, ncols) j=0 for i in range(1, nrows): # pay_time=sheet.row_values(i,0)#付款时间 # print(pay_time) #print(sheet.cell(i, 0).value) # 指定行和列获取数据 goodsName = sheet.cell(i, 5).value #商品名称 inOrOut = sheet.cell(i, 7).value ## 收、支 status = sheet.cell(i, 8).value ## 交易状态 # if (reason.find("收益发放") > -1): # continue if inOrOut is not None and len(inOrOut.strip()) >0 and goodsName.find("收益发放") == -1\ and goodsName.find("赚钱红包推荐奖励")==-1\ and (status.find("交易成功")>-1 or status.find("等待确认收货")>-1): #print(i,inOrOut) ioRecord = InOutRecord() ioRecord.reason=goodsName################################## reason ioRecord.inOrOut=inOrOut[0:1]########################### 收/支 payTimeStr = sheet.cell(i, 0).value if payTimeStr is not None : # print (i, payTimeStr) payTimeStr = payTimeStr[0:8] dateStr = payTimeStr[0:4] monthStr = payTimeStr[4:6] dayStr = payTimeStr[6:8] # value = datetime.datetime(payTimeStr) XXXX # print(payTimeStr) # print(dateStr) # print(monthStr) # print(dayStr) #自动生成编号：先查当天有几条记录了 filterDate = dateStr[2:4]+monthStr+dayStr numberStr,connStr=genAccessKeyAndTradeDate(filterDate, dateStr, monthStr, dayStr) # print("编号===" + numberStr) if len(numberStr) > 0: ioRecord.numberStr = numberStr ######################### 编号 else: print("eeeeeeeeeeeeeeeeeeeeeeee " + "编号自动生成错误" + "eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee") ioRecord.payDate=connStr################################### 日期 ########################################################################## amount = sheet.cell(i, 6).value#金额 ioRecord.amount=amount # print("金额==="+str(amount)) #交易来源地 tradeSource = sheet.cell(i, 3).value # 交易来源地 #获取交易对方，来确认收支类型 tradeObject = sheet.cell(i, 4).value # 交易对方 #获取收支具体类型，如衣、食、住、用、行等 ioRecord.type=getType(tradeObject) #修正 type if ioRecord.reason is not None: if ioRecord.reason.find('打车')>-1: ioRecord.type='行' #完善 ioRecord.reason=ioRecord.reason.strip()+"【"+tradeObject.strip()+"】"+"【"+tradeSource.strip()+"】" # if len(ioRecord.reason)>15:#商品名称只取前15个字符 # ioRecord.reason=ioRecord.reason[0:15] ioRecord.comment=tradeObject.strip() ret=insertRecord(ioRecord)#存储 #ret=insertTest(); #ret=test_insertRecord(ioRecord) if ret==1: print(str(j + 1) + "--" + str( i) + " " + ioRecord.numberStr + " " + ioRecord.payDate + " " + ioRecord.inOrOut + " " + str(ioRecord.amount) + " " + ioRecord.type + " " + ioRecord.reason + "---存储成功") print("==============================================================================================") j=j+1
#记录当前已经记了几条账目了 def getRecordCountByDate(date): conn,cur=connect_access() #sql="SELECT * FROM " + table+" where 编号 like"+date+"" sql = "select * from 收支记录表 where 编号 like "+"'"+"%"+date+"%"+"'" #sql = "select * from 收支记录表 where 编号 = "+"'"+date+"'" #sql = "select * from 收支记录表 where 编号 like " + date #print(sql) cur.execute(sql) #cur.execute(sql,date) alldata = cur.fetchall() total_rows = len(alldata) # total_cols = len(alldata[0]) # print("总行数 = %d" % ( total_rows)) return total_rows
#根据日期自动生成编号，并生成交易日期 def genAccessKeyAndTradeDate(filterDate, dateStr, monthStr, dayStr): recordCount = getRecordCountByDate(filterDate) print("filterDate==="+filterDate) print("recordCount==="+str(recordCount)) numberStr = "" if recordCount == 0: numberStr = filterDate + "01" elif recordCount > 0 and recordCount < 9: numberStr = filterDate + "0" + str(recordCount + 1) elif recordCount >= 9: numberStr = filterDate + str(recordCount + 1) connStr = dateStr + "/" + monthStr + "/" + dayStr return numberStr, connStr
def getType(tradeObject): type="" if tradeObject is not None: if tradeObject.find('超市') > -1 or tradeObject.find('汇悦') > -1 or tradeObject.find( '大王') > -1 or tradeObject.find('便利') > -1: \ type = '食' if tradeObject.find('商品') > -1 or tradeObject.find('蔬菜') > -1 or tradeObject.find('奶吧') > -1: type = "食" if tradeObject.find('条码支付-总部') or tradeObject.find('食品') > -1 or tradeObject.find( '餐吧') > -1 or tradeObject.find('切面') > -1: type = "食" if tradeObject.find('德青源') > -1 or tradeObject.find('食') > -1: type = "食" if tradeObject.find('童') > -1 or tradeObject.find('母') > -1: type = '宝宝' if tradeObject.find('移动') > -1: type = "通信费用" if tradeObject.find('地铁') > -1: type = "行" if tradeObject.find('市妇幼') > -1 or tradeObject.find('御和堂') > -1 or tradeObject.find('医院') > -1: type = '医疗健康' if tradeObject.find('七匹狼') > -1 or tradeObject.find('南极人') > -1: type = '衣' if tradeObject.find('图书') > -1 or tradeObject.find('当当') > -1: type = '自我完善' if tradeObject.find('供电') > -1: type = '电费' return type
#存储一条数据 def insertRecord(ioRecord): if (ioRecord is not None): #printItemAllInfo(ioRecord) conn, cursor = connect_access() #sql = "insert into 收支记录表 (编号, 日期, 收支原因, 收支, 金额, 类型) values( %s, %s, %s, %s, %s, %s)" # sql = "insert into 收支记录表 (编号, 日期, 收支原因, 收支, 金额, 类型) " \ # "values(%s, %s, %s, %s, %s, %s)" createDate = CommonUtil.getCurrentDateStr() sql = "insert into 收支记录表 (编号, 日期, 收支原因, 收支, 金额, 类型,备注) values("\ + "'"+ str(ioRecord.numberStr) + "'" +","\ + "'" + str(ioRecord.payDate) + "'" + ','\ + "'" + str(ioRecord.reason) + "'" + ','\ + "'" + str(ioRecord.inOrOut) + "'" + ','\ + "'" + str(ioRecord.amount) + "'" + ','\ + "'" + str(ioRecord.type) + "'"+ ',' \ + "'" + str(ioRecord.comment+"_"+createDate) + "'" + ')' #sql = 'insert into 收支记录表 (编号, 日期, 收支原因, 收支, 金额, 类型) values('+str(ioRecord.numberStr)+',' # +ioRecord.payDate+','+ioRecord.reason+','+ioRecord.inOrOut+','+float(ioRecord.amount)+','+ioRecord.type+');' # print("=======================================================================") # print(dataRow[16], dataRow[17], dataRow[18]) #print(sql) #print(ioRecord.numberStr,ioRecord.payDate,ioRecord.reason,ioRecord.inOrOut,ioRecord.amount,ioRecord.type) #print(createDate) try: # cursor.execute(sql, ( # str(ioRecord.numberStr),str(ioRecord.payDate),str(ioRecord.reason), # str(ioRecord.inOrOut),str(ioRecord.amount),str(ioRecord.type))) cursor.execute(sql) conn.commit() cursor.close() conn.close() return 1 except Exception as e: traceback.print_exc() print("insertOnRecord() 出错，具体记录内容如下XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX") printItemAllInfo(ioRecord) cursor.close() conn.close() return -1
#获取数据库连接 def connect_access(): mdb = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};' \ 'DBQ=D:\\个人\\我的软件\\PersonalAsistance_2012_04_13\\PersonalAsistance\\PersonalAsistance\\bin\\Debug\\PADB.mdb' conn = pypyodbc.win_connect_mdb(mdb) cur = conn.cursor() return conn,cur
总结:
这里面有几个关键点：
1、读Excel（这个不算，比较淘常见）
2、生成Access表的主键，这个主键的规则是这样的：取年的最后两位，月的两位，日的两位，然后从01开始往后编码，有几条编到几；
3、连接Access，读、写Access；
python 代码已经放出来了，记账程序有需要的可QQ我：861712499
