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