我有一个很多年前自己写的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 代码已经放出来了
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
