#!/usr/bin/env python # coding:utf-8 import csv from pymongo import MongoClient myclient = MongoClient("mongodb://127.0.0.1:27017/") mycoup = myclient['shuiwu06'] mycohz = myclient['shuiwu_hz']['base_date'] mycopp = myclient['shuiwu_210709']['base_data'] def w1(dbn): result = mycoup[dbn].find() list1 = [] for i in result: print(i) # ***********纳税人名称**********************************************# if dbn in ['08_fj']: n1 = i['S1'] elif dbn in ['11_gx']: n1 = i['NSR_NAME'] elif dbn in ['12_hb','19_hb']: n1 = i['NSRMC'] elif dbn in ['20_hn']: n1 = i['taxpayerName'] elif dbn in ['24_sx','26_gz','30_xj','33_sh']: n1 = i['nsrmc'] else: n1 = i['纳税人名称'] #************所属地区*****************************************# if dbn in ['01_ah','07_zj','22_yn']: n2 = i['所属地区'] elif dbn in ['08_fj']: n2 = i['S20'] elif dbn in ['11_gx']: n2 = i['ADDR'] elif dbn in ['19_hb']: n2 = i['SHENGF'] + '/' + i['DIS'] elif dbn in ['20_hn']: n2 = i['siteName'] elif dbn in ['24_sx']: n2 = i['dqmc'] elif dbn in ['26_gz']: n2 = i['area'] else: n2 = '' #*************纳税人识别号或社会信用代码******************************# if dbn in ['01_ah','04_sz','06_js','10_gd','16_gs','17_qh','21_jx','22_yn','25_sx','29_xz','31_tj']: n3 = str(i['纳税人识别号']) elif dbn in ['02_nb','03_bj','05_sc','07_zj','09_sd','13_ln','14_hlj','15_jl','18_hn','28_nx']: n3 = str(i['纳税人识别号或社会信用代码']) elif dbn in ['27_nmg']: n3 = str(i['纳税人识别号或社会信用代码']).split('\xa0')[0] elif dbn in ['08_fj']: n3 = str(i['S2']) elif dbn in ['11_gx']: n3 = str(i['NSR_ID']) elif dbn in ['12_hb','19_hb']: n3 = str(i['NSRSBH']) elif dbn in ['20_hn']: n3 = str(i['taxpayerNumber']) elif dbn in ['23_hn']: n3 = str(i['统一社会信用代码（纳税人识别号）']) elif dbn in ['26_gz','30_xj']: n3 = str(i['nsrsbh']) elif dbn in ['33_sh']: n3 = str(i['nsrsbm']) elif dbn in ['24_sx']: try: n3 = str(i['nsrsbh']) except: n3 = '' #****************组织机构代码************************************# if dbn in ['08_fj']: n4 = str(i['S3']) elif dbn in ['11_gx']: n4 = '' elif dbn in ['12_hb']: n4 = str(i['ZZJGDM']) elif dbn in ['19_hb']: n4 = str(i['ZZJG']) elif dbn in ['20_hn']: n4 = str(i['organizationalCode']) elif dbn in ['26_gz','30_xj','33_sh']: try: n4 = str(i['zzjgdm']) except: n4 = '' elif dbn in ['24_sx']: try: n4 = str(i['zzjgdm']) except: n4 = '' else: n4 = str(i['组织机构代码']) #***************注册地址************************************# if dbn in ['08_fj']: n5 = i['S4'] elif dbn in ['11_gx']: n5 = i['REG_ADDR'] elif dbn in ['12_hb','19_hb']: n5 = i['ZCDZ'] elif dbn in ['20_hn']: n5 = i['place'] elif dbn in ['24_sx','26_gz','30_xj','33_sh']: n5 = i['zcdz'] else: n5 = i['注册地址'] #***********法定代表人或负责人姓名、性别及身份证号码（或其他证件号码）******# if dbn in ['01_ah','07_zj','22_yn']: # 法定代表人或者负责人姓名、性别及身份证号码（或其他证件号码） n6 = i['法定代表人或负责人姓名、性别及身份证号码（或其他证件号码）'] elif dbn in ['02_nb','03_bj','05_sc','13_ln','15_jl','18_hn','28_nx']: n6 = i['法定代表人或者负责人姓名、性别及身份证号码（或其他证件号码）'] elif dbn in ['25_sx',]: n6 = i['法定代表人或者负责人姓名、性别、证件名称及号码'] elif dbn in ['27_nmg',]: n6 = i['法定代表人或者负责人姓名、性别、及身份证号码（或者其他证件号码）'] elif dbn in ['29_xz',]: n6 = i['法人信息'] elif dbn in ['14_hlj',]: n6 = i['法定代表人或者负责人姓名、性别及身份证号码（或者其他证件号码）'] elif dbn in ['04_sz']: n6 = i['法定代表人或负责人姓名、性别、证件名称及号码'] elif dbn in ['10_gd','16_gs','17_qh','21_jx']: n6 = i['法定代表人或者负责人姓名、性别、证件名称及号码'] elif dbn in ['06_js']: n6 = i['法定代表人姓名、性别及身份证号码'] elif dbn in ['09_sd']: n6 = i['法定代表人或者负责人姓名'] + '。' + i['性别'] elif dbn in ['08_fj']: n6 = i['S5'] + '。' + i['S6'] + '。' + i['S7'] elif dbn in ['12_hb']: n6 = i['FDDBR'] + '。' + i['FDDBSEX'] + '。' + i['FDDBZJ']+ '。' + i['FDDBZJH'] elif dbn in ['11_gx']: n6 = i['FDDBR_NAME'] + '。' + i['FDDBR_SEX'] + '。' + i['FDDBR_SFZHM'] elif dbn in ['19_hb']: n6 = i['FDDBR'] elif dbn in ['23_hn',]: n6 = i['法定代表人、负责人或者经法院判决确定的实际责任人的姓名、性别、证件名称及号码'] elif dbn in ['30_xj']: n6 = i['fddbrxm'] + '。' + i['fddbrxb'] + '。' + i['fddbrzjlx']+ '。' + i['fddbrzjhm'] elif dbn in ['31_tj']: n6 = i['姓名'] + '。' + i['性别'] + '。' + i['证件名称'] + '。' + i['证件号码'] elif dbn in ['26_gz']: n6 = i['fddbrhzfzrxm'] + '。' + i['fddbrhzfzrxb'] + '。' + i['fddbrhzfzrzjmc']+ '。' + i['fddbrhzfzrzjhm'] elif dbn in ['33_sh']: n6 = i['frdbmc'] + '。' + i['rowno'] + '。' + i['frdbxb']+ '。' + i['frdbsfz'] elif dbn in ['24_sx']: try: n6 = i['fddbrxm'] + '。' + i['fddbrxb'] + '。' + i['fddbrsfzhm'] except: n6 = '' elif dbn in ['20_hn']: n6 = '' if i['legalName']: n6 += i['legalName'] if i['legalSex']: if i['legalSex'] == 1: fgt = '男' else: fgt = '女' n6 += fgt if i['legalIdCard']: n6 += i['legalIdCard'] #************违法期间法人代表或者负责人姓名、性别、证件名称及号码*************# if dbn in ['01_ah','02_nb','03_bj','05_sc','07_zj','22_yn','28_nx']: # 违法期间法人代表或者负责人姓名、性别及身份证号码（或其他证件号码） n7 = i['违法期间法人代表或者负责人姓名、性别及身份证号码（或其他证件号码）'] elif dbn in ['18_hn']: n7 = i['违法期间法定代表人或者负责人姓名、性别及身份证号码（或其他证件号码）'] elif dbn in ['14_hlj']: n7 = i['违法期间法人代表或者负责人姓名、性别及身份证号码（或者其他证件号码）'] elif dbn in ['10_gd']: n7 = i['违法期间法人代表或者负责人姓名、性别、证件名称及号码'] elif dbn in ['13_ln','15_jl']: n7 = i['违法期间法人代表姓名及身份证号码'] elif dbn in ['25_sx']: n7 = i['违法期间法人代表或者负责人姓名性别及身份证号码（或其他证件号码）'] elif dbn in ['27_nmg']: n7 = i['违法期间法定代表人或责任人姓名、性别、身份证号码（或者其他证件号码）'] elif dbn in ['08_fj']: n7 = i['S8'] + '。' + i['S9'] + '。' + i['S10'] elif dbn in ['30_xj']: try: n7 = i['cwfzrxm'] + '。' + i['cwfzrxb'] + '。' + i['cwfzrzjlx']+ '。' + i['cwfzrzjhm'] except: n7 = '' elif dbn in ['20_hn']: n7 = '' if i['legalNameDuring']: n7 += i['legalNameDuring'] if i['legalSexDuring']: if i['legalSexDuring'] == 1: fgt = '男' else: fgt = '女' n7 += fgt if i['legalIdCardDuring']: n7 += i['legalIdCardDuring'] else: n7 = '' #*************负有直接责任的财务负责人姓名、性别、证件名称及号码*********# if dbn in ['01_ah', '02_nb', '03_bj','05_sc','07_zj','13_ln','15_jl','18_hn','22_yn','27_nmg']: #负有直接责任的财务负责人姓名、性别及身份证号码（或其他证件号码） n8 = i['负有直接责任的财务人员姓名、性别及身份证号码（或其他证件号码）'] elif dbn in ['16_gs','25_sx']: n8 = i['负有直接责任的财务负责人姓名性别及身份证号码（或其他证件号码）'] elif dbn in ['14_hlj']: n8 = i['负有直接责任的财务人员姓名、性别及身份证号码（或者其他证件号码）'] elif dbn in ['28_nx']: n8 = i['负有直接责任的财务负责人姓名、性别及身份证号码（或其他证件号码）'] elif dbn in ['04_sz','06_js','10_gd','17_qh','21_jx']: n8 = i['负有直接责任的财务负责人姓名、性别、证件名称及号码'] elif dbn in ['08_fj']: n8 = i['S11'] + '。' + i['S12'] + '。' + i['S13'] elif dbn in ['23_hn']: n8 = i['经法院裁判确定的负有直接责任的财务人员、团伙成员的姓名、性别、证件名称及号码'] elif dbn in ['20_hn']: n8 = '' if i['financeName']: n8 += i['financeName'] if i['financeSex']: if i['financeSex'] == 1: fgt = '男' else: fgt = '女' n8 += fgt if i['financeIdCard']: n8 += i['financeIdCard'] else: n8 = '' #***********实际负责人姓名、性别及身份证号码（或其他证件号码）************# if dbn in ['01_ah', '02_nb', '03_bj','05_sc','07_zj','13_ln','15_jl','18_hn','22_yn','27_nmg','28_nx']: #实际负责人姓名、性别及身份证号码（或其他证件号码） n9 = i['实际负责人姓名、性别及身份证号码（或其他证件号码）'] elif dbn in ['16_gs','25_sx']: n9 = i['实际负责人姓名性别及身份证号码（或其他证件号码）'] elif dbn in ['06_js']: n9 = i['经法院裁判确定的实际责任人姓名、性别、证件名称及号码'] elif dbn in ['14_hlj']: n9 = i['实际负责人姓名、性别及身份证号码（或者其他证件号码）'] elif dbn in ['20_hn']: n9 = '' if i['principalName']: n9 += i['principalName'] if i['principalSex']: if i['principalSex'] == 1: fgt = '男' else: fgt = '女' n9 += fgt if i['principalIdCard']: n9 += i['principalIdCard'] # n9 = i['principalName'] + '。' + i['principalSex'] + '。' + i['principalIdCard'] else: n9 = '' #*************负有直接责任的中介机构信息****************************# if dbn in ['01_ah', '02_nb', '03_bj','05_sc','07_zj','13_ln','14_hlj','15_jl','18_hn','22_yn','27_nmg','28_nx']: n10 = i['负有直接责任的中介机构信息'] elif dbn in ['04_sz','06_js','10_gd','16_gs','17_qh','21_jx','23_hn','25_sx']: n10 = i['负有直接责任的中介机构信息及其从业人员信息'] elif dbn in ['20_hn']: n10 = i['agency'] else: n10 = '' #*************案件性质*********************************# if dbn in ['08_fj']: n11 = i['S18'] elif dbn in ['09_sd']: n11 = '' elif dbn in ['11_gx','12_hb','19_hb']: n11 = i['AJXZ'] elif dbn in ['20_hn']: n11 = i['hardCaseType']['typeName'] elif dbn in ['26_gz','30_xj']: n11 = i['ajxz'] elif dbn in ['29_xz']: n11 = i['违法案件性质'] elif dbn in ['33_sh']: n11 = i['ajMc'] elif dbn in ['24_sx']: try: n11 = i['ajxzmc'] except: n11 = '' else: n11 = i['案件性质'] #***********主要违法事实相关法律依据及税务处理处罚情况**********************# if dbn in ['01_ah','02_nb','05_sc','07_zj','13_ln','14_hlj','15_jl','16_gs','18_hn','22_yn','25_sx','27_nmg']: n12 = i['主要违法事实相关法律依据及税务处理处罚情况'] elif dbn in ['03_bj','29_xz']: n12 = i['主要违法事实'] elif dbn in ['06_js']: n12 = i['主要违法事实、相关法律依据及税务处理处罚情况'] elif dbn in ['04_sz','10_gd','17_qh','28_nx','31_tj']: n12 = i['主要违法事实'] + '。' +i['相关法律依据及税务处理处罚情况'] elif dbn in ['12_hb']: n12 = i['ZYWFSS'] + '。' +i['FLYJ'] elif dbn in ['30_xj']: n12 = i['wfss'] + '。' +i['swclcfqk'] elif dbn in ['09_sd']: n12 = i['主要违法事实'] + '。' + i['相关法律依据及税务处理处罚情况 '] elif dbn in ['08_fj']: n12 = i['S19'] elif dbn in ['11_gx']: n12 = i['ZYWFSS'] elif dbn in ['19_hb']: n12 = i['WFSS'] elif dbn in ['33_sh']: n12 = i['wfss'] elif dbn in ['20_hn']: n12 = i['content'] elif dbn in ['21_jx']: n12 = i['主要违法事实直接法律依据及税务处理处罚情况'] elif dbn in ['24_sx']: n12 = i['zywfss'] + '。' + i['flyj_cljg'] elif dbn in ['26_gz']: n12 = i['zywfss'] + '。' + i['xgflyjjswclcfqk'] elif dbn in ['23_hn']: n12 = i['主要违法事实'] + '。' + i['相关法律依据及税务处理、税务行政处罚等情况'] #**************************************************************# if dbn in ['08_fj']: n13 = i['docreltime'].replace('-','/') elif dbn in ['11_gx']: n13 = i['DOCPUBTIME'].split(' ')[0].replace('.','/') elif dbn in ['12_hb']: n13 = i['GBRQ'].split(' ')[0] elif dbn in ['19_hb']: n13 = i['GBRQ'].split(' ')[0].replace('年','/').replace('月','') elif dbn in ['24_sx']: n13 = i['xsrq'].split(' ')[0].replace('-','/') elif dbn in ['26_gz']: n13 = i['CrTime'].split(' ')[0].replace('-','/') elif dbn in ['30_xj']: n13 = '' else: n13 = i['date'] n14 = dbn list1.append([n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, n13, n14]) return list1 # 01_ah 02_nb # dbn = '33_sh' def drcav(): l1 = ['01_ah', '02_nb', '03_bj', '04_sz', '05_sc', '06_js', '07_zj', '08_fj', '09_sd', '10_gd', '11_gx', '12_hb', '13_ln', '14_hlj', '15_jl', '16_gs', '17_qh', '18_hn', '19_hb', '20_hn', '21_jx', '22_yn', '23_hn', '24_sx' , '25_sx', '26_gz', '27_nmg', '28_nx', '29_xz', '30_xj', '31_tj', '33_sh'] f = open('minglu.csv','w',encoding='utf-8',newline='') csv_writer = csv.writer(f) for dbn in l1: list1 = w1(dbn) for i1 in list1: csv_writer.writerow(i1) f.close() def r1(): with open('minglu.csv', 'r', encoding='utf-8') as f: csv_writer = csv.reader(f) num = 0 for i in csv_writer: num +=1 print(num) nsrmc = i[0] ssdq =i[1] nsrsbh= i[2] zzjgdm=i[3] zcdz= i[4] fddbr=i[5] wffddbr= i[6] zjfzcwr= i[7] sjfzr= i[8] zjzrzj= i[9] ajxz=i[10] zywf= i[11] date=i[12] soure=i[13] dict1 = { "nsrmc":nsrmc, "ssdq":ssdq, "nsrsbh":nsrsbh, "zzjgdm":zzjgdm, "zcdz":zcdz, "fddbr":fddbr, "wffddbr":wffddbr, "zjfzcwr":zjfzcwr, "sjfzr":sjfzr, "zjzrzj":zjzrzj, "ajxz":ajxz, "zywf":zywf, "date": date, "source": soure, } # print(dict1) # break mycohz.insert_one(dict1) # r1() # def ser_m(name): a = mycohz.find({"nsrmc":name}) for i in a: print(i) i.pop('_id') mycopp.insert_one(i) def w_test(): f = open('test.csv', 'w', encoding='utf-8', newline='') csv_writer = csv.writer(f) i1= ['1','2'] csv_writer.writerow(i1) f.close() # w_test() import xlrd #打开excel def r2(): wb = xlrd.open_workbook('税务违法企业匹配名单.xlsx') #按工作簿定位工作表 sh = wb.sheet_by_name('Sheet1') # print(sh.nrows)#有效数据行数 # print(sh.ncols)#有效数据列数 # print(sh.cell(0,0).value)#输出第一行第一列的值 # print(sh.row_values(0))#输出第一行的所有值 # #将数据和标题组合成字典 # print(dict(zip(sh.row_values(0),sh.row_values(1)))) #遍历excel，打印所有数据 num = 0 for i in range(sh.nrows): num +=1 print(num) name = sh.row_values(i)[0] ser_m(name) # print(name) # r2() # drcav() #将库内历史数据导入csv，用来备份上传至另一个mongo --写入minglu.csv # r1() #将上一步csv导入mongo --写入shuiwu_hz mongoDB # 查询前添加mongo索引 db.***.createIndex({}) # r2() #将xlsx文件查询，数据写入 mongodb # 下命令导出csv # mongoexport.exe -h 127.0.0.1 --port 27017 -d shuiwu_210709 -c base_data --csv -f nsrmc,ssdq,nsrsbh,zzjgdm,zcdz,fddbr,wffddbr,zjfzcwr,sjfzr,zjzrzj,ajxz,zywf,date,source -o shuiwu.csv