读取微信账单和支付宝账单文件csv，并导入mysql

import os
import csv
import glob
import chardet
import pymysql as mysql
#TODO 一、创建数据库连接
conn = mysql.connect(host='192.168.3.116', port=3306, user='root',password='123456', db='db_wx_zfb', charset='utf8mb4', connect_timeout=1000)
mycursor = conn.cursor()
print('mysql连接成功:192.168.3.116:3306~~~~~~~~')

#TODO 二、读取所有csv文件
csv_list = glob.glob(r'C:\Users\Yinzh\Desktop\记账\*.csv') 
print('解析文件路径%s' %csv_list)

#执行创表语句(创建表“loan_file”，设置其表内的字段名和字段对应列的容量)
sql_tab1 ="CREATE TABLE IF NOT EXISTS `loan_file` (\n" \
                "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" \
                "  `path` varchar(255) DEFAULT NULL COMMENT '文件路径',\n" \
                "  `file_name` varchar(64) DEFAULT NULL COMMENT '文件名称',\n" \
                "  `crt_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" \
                "  PRIMARY KEY (`id`)\n" \
                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='文件导入记录'"

sql_tab2="CREATE TABLE IF NOT EXISTS `loan_repay` (\n" \
                "  `uid` int(11) NOT NULL AUTO_INCREMENT,\n" \
                "  `pay_type` varchar(10) DEFAULT NULL COMMENT '交易渠道',\n" \
                "  `month` varchar(10) DEFAULT NULL COMMENT '月份',\n" \
                "  `trans_time` datetime DEFAULT NULL COMMENT '交易时间',\n" \
                "  `trans_type` varchar(64) DEFAULT NULL COMMENT '交易分类',\n" \
                "  `counterparty` varchar(64) DEFAULT NULL COMMENT '交易对方',\n" \
                "  `other_partyaccount` varchar(64) DEFAULT NULL COMMENT '对方账号',\n" \
                "  `product_remarks` varchar(1024) DEFAULT NULL COMMENT '商品说明',\n" \
                "  `collection_dis` varchar(64) DEFAULT NULL COMMENT '收/支',\n" \
                "  `money` decimal(16,4) DEFAULT NULL COMMENT '金额',\n" \
                "  `payment_method` varchar(64) DEFAULT NULL COMMENT '收/付款方式',\n" \
                "  `status` varchar(64) DEFAULT NULL COMMENT '交易状态',\n" \
                "  `order_number` varchar(64) NOT NULL COMMENT '交易订单号',\n" \
                "  `merchant_order` varchar(64) DEFAULT NULL COMMENT '商家订单号',\n" \
                "  `remarks` varchar(1024) DEFAULT NULL COMMENT '备注',\n" \
                "  `crt_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" \
                "  PRIMARY KEY (`uid`),\n" \
                "  UNIQUE KEY `uq_order` (`order_number`)\n" \
                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='账单明细'"
mycursor.execute(sql_tab1)
mycursor.execute(sql_tab2)
print('数据表创建成功【loan_file】：%s' % sql_tab1)
print('数据表创建成功【loan_repay】：%s' % sql_tab2)

#TODO 三、读取每一个csv文件数据 [alipay、微信]
bill_list = []  #账单流水明细
path_list = []  #账单文件记录

for i in csv_list:  
    # 匹配文件
    filePath = i
    dirname, file_name = os.path.split(filePath)
    print('解析文件名称%s~' % file_name)

    file_coding = chardet.detect(open(filePath,'rb').read())['encoding']
    print('解析文件encoding：%s' % file_coding)

    # 对比数据库文件记录，存在过
    mycursor.execute('SELECT * FROM loan_file where file_name = "' +file_name+ '"')
    results = mycursor.fetchall()
    if len(results) > 0:
        print('文件【%s】系统内已存在' % file_name)
        continue

    path_list.append([i,file_name])
    # 文件编码初始化
    if file_coding.startswith('GB') : file_coding = 'gbk'
    
    # 解析csv文件
    writeLine = False
    with open(filePath,'r',encoding= file_coding) as f:
        reader = csv.reader(f)
        for row in reader:
            # 设置开始读取行
            if len(row) > 0  and row[0].startswith('交易时间') : 
                writeLine = True
                continue
            # 入库集合        
            if writeLine == True :
                # 账单列信息
                # '交易渠道','月份','交易时间','交易分类','交易对方','对方账号','商品说明','收/支','金额','收/付款方式','交易状态','交易订单号','商家订单号','备注'
                # pay_type ,month ,trans_time datetime,trans_type ,counterparty ,other_partyaccount ,product_remarks ,collection_dis ,money ,payment_method ,status ,order_number ,merchant_order ,remarks
                month = row[0][0:7]
                #解析微信文件
                if file_name.startswith('微信') :
                    # 交易时间,交易类型,交易对方,商品,收/支,金额(元),支付方式,当前状态,交易单号,商户单号,备注
                    bill_list.append(['微信',month,row[0],row[1],row[2],'/',row[3],row[4],row[5][1:],row[6],row[7],row[8],row[9],row[10]])
                #解析支付宝文件
                else :
                    # 交易时间,交易分类,交易对方,对方账号,商品说明,收/支,金额,收/付款方式,交易状态,交易订单号,商家订单号,cl备注
                    bill_list.append(['支付宝',month,row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11]])
    f.close()

#四、TODO 写入mysql数据库 
print('开始导入~~~')
sql1 = 'INSERT INTO loan_file (`path`,file_name) VALUES (%s,%s)'
# 关键字 ON DUPLICATE KEY UPDATE 根据唯一约束更新数据，保证流水数据唯一【order_number】
sql2 = 'INSERT INTO loan_repay (pay_type, month, trans_time, trans_type, counterparty, other_partyaccount, product_remarks, collection_dis, money, payment_method, status, order_number, merchant_order, remarks) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE pay_type = values(pay_type), month = values(month), trans_time = values(trans_time), trans_type = values(trans_type), counterparty = values(counterparty), other_partyaccount = values(other_partyaccount), product_remarks = values(product_remarks), collection_dis = values(collection_dis), money = values(money), payment_method = values(payment_method), status = values(status), order_number = values(order_number), merchant_order = values(merchant_order), remarks = values(remarks)'
mycursor.executemany(sql1,path_list)
mycursor.executemany(sql2,bill_list)
print('结束导入~~~')
conn.commit()
mycursor.close()