def person_info_transform(): # 删临时表 execute(DropPersonTemporarySQL) # 建临时表 execute(CreatePersonTemporarySQL) params = [] cursor = get_cursor( "select bank_user_id, bank_platform_id, depository_id, cred_no " "from pyner_user_person " "where cred_type = 'IDC'") print('fetch 个人信息结束 {}'.format(datetime.now())) while True: params.clear() results = cursor.fetchmany(10000) print('已经获取个人数据 {}条 时间:{}'.format(len(results), datetime.now())) if len(results) == 0: break for result in results: sex = trans_sex(result[3]) birthday = trans_birthday(result[3]) params.append( [result[0], result[1], result[2], result[3], sex, birthday]) # 在临时表中插入转换后的数据 insert_batch(InsertPersonTemporarySQL, params=params) # 更新存管表用户信息 update(UpdatePersonInfoSQL) print('person_info_transform Finish', datetime.now())
def generate_customer(): """生成客户id""" # ALTER TABLE pyner_customer AUTO_INCREMENT = 1000000001; select_insert(generateSQL) """生成平台客户ID""" update(updatePlatformCredInfoUSCCSQL) update(updatePlatformCredInfoBLCSQL) select_insert(generatePlatformSQL) print('generate_customer Finish', datetime.now())
def user_area_transform(): # 删临时表 execute(DropUserTemporarySQL) # 建临时表 execute(CreateUserTemporarySQL) params = [] area_dict = get_area_dict() cursor = get_cursor("select bank_user_id, bank_platform_id, depository_id, cred_no " "from pyner_user " "where user_type = 'Person' and cred_type = 'IDC'") print('fetch 个人信息结束 {}'.format(datetime.now())) while True: params.clear() results = cursor.fetchmany(10000) print('已经获取个人数据 {}条 时间:{}'.format(len(results), datetime.now())) if len(results) == 0: break for result in results: area = trans_idc_area(result[3], area_dict) params.append([result[0], result[1], result[2], result[3], area.province_code, area.province_name, area.city_code, area.city_name, area.area_code, area.area_name, area.longitude, area.latitude]) # 在临时表中插入转换后的数据 insert_batch(InsertUserTemporarySQL, params=params) cursor = get_cursor("select bank_user_id, bank_platform_id, depository_id, cred_no " "from pyner_user " "where user_type != 'Person' and cred_type = 'USCC'") print('fetch 企业信息结束 {}', datetime.now()) while True: params.clear() results = cursor.fetchmany(1000) print('已经获取企业数据 {}条 时间:{}'.format(len(results), datetime.now())) if len(results) == 0: break for result in results: area = trans_uscc_area(result[3], area_dict) params.append([result[0], result[1], result[2], result[3], area.province_code, area.province_name, area.city_code, area.city_name, area.area_code, area.area_name, area.longitude, area.latitude]) # 在临时表中插入转换后的数据 insert_batch(InsertUserTemporarySQL, params=params) # 更新存管表用户信息 update(UpdateUserInfoSQL) print('user_area_transform Finish!', datetime.now())
def build_history_user(): faker = Faker("zh_CN") params = [] results = get_history_user() for result in results: param = [ result[0], result[1], result[2], result[3], faker.name(), faker.ssn() ] params.append(param) # 插入用户表 insert_batch(insertUserSQL, params=params) print('insertUser Finish!') # 插入个人表 insert_batch(insertPersonSQL, params=params) print('insertPerson Finish!') # 更新平台名 update(updatePlatformNameSQL)
def calc_request_time(): """请求时间为空""" update(calcRequestTimeSQL) print('calc_request_time Finish!', datetime.now())
def calc_flowed_bid_time(): """设置流标标的时间""" update(calcFlowBidTimeSQL) print('calc_flowed_bid_time Finish!', datetime.now())
def calc_overdue_time_and_amount(): """设置逾期时间、逾期金额""" update(calcOverdueTimeAndAmount) print('calc_overdue_time_and_amount Finish!')
def calc_granted_amount(): """放款金额""" update(calcGrantedAmountSQL) print('calc_granted_amount Finish!', datetime.now())
def update_enterprise_customer_id(): update(updateEnterpriseCustomerIdSQL) print('update_enterprise_customer_id Finish', datetime.now())
def update_user_customer_id(): """更新用户表客户Id""" update(updateUserCustomerIdSQL) print('update_user_customer_id Finish', datetime.now())
def calc_raise_duration(): """设置募集期""" update(calcRaiseDurationSQL) update(calcRaiseDurationSQL1) print('calc_raise_duration Finish!', datetime.now())
def update_obligatory_customer_id(): update(updateObligatoryCustomerIdSQL) print('update_obligatory_customer_id Finish', datetime.now())
def update_trade_pay_customer_id(): update(updateTradePayCustomerIdSQL) print('update_trade_pay_customer_id Finish', datetime.now())
def update_bid_customer_id(): update(updateBidCustomerIdSQL) print('update_bid_customer_id Finish', datetime.now())
def update_account_customer_id(): # 更新用户账户表 update(updateAccountCustomerIdSQL1) # 更新平台账户表 update(updateAccountCustomerIdSQL2) print('update_account_customer_id Finish', datetime.now())
def update_platform_customer_id(): update(updatePlatformCustomerIdSQL) print('update_platform_customer_id Finish', datetime.now())
def calc_granted_time(): """放款时间为空且已放款""" update(calcGrantedTimeSQL) print('calc_granted_time Finish!', datetime.now())
def update_trade_receive_customer_id(): update(updateTradeReceiveCustomerIdSQL) print('update_trade_receive_customer_id Finish', datetime.now())
def calc_lended_bid_time(): """设置放款标的时间""" update(calcLendedBidTimeSQL) print('calc_lended_bid_time Finish!', datetime.now())
def update_bid_platform_name(): """设置标的平台名称""" update(updateBidPlatformNameSQL) print('update_bid_platform_name Finish!', datetime.now())
def calc_should_repay_time(): """设置应还时间""" update(calcShowRepayTimeSQL) print('calc_should_repay_time Finish!', datetime.now())
def calc_close_bid_status(): """设置结清标的时间、状态、综合费率""" update(calcCloseBidStatusSQL) print('calc_close_bid_status Finish!', datetime.now())
def calc_repayed_amount(): """还款金额""" update(calcRepayedAmountSQL) print('calc_repayed_amount Finish!', datetime.now())
def update_person_customer_id(): update(updatePersonCustomerIdSQL) print('update_person_customer_id Finish', datetime.now())
def calc_balance_amount(): """设置借款余额""" update(calcBalanceAmountSQL) update(calcBalanceAmountSQL1) print('calc_balance_amount Finish!', datetime.now())
def update_bid_person_info(): update(updateBidPersonInfoSQL) print('update_bid_person_info Finish', datetime.now())
def sync_obligatory_borrower_info(): """同步 债权表借款人用户ID,客户ID""" update(syncObligatoryBorrowerInfoSQL) print('sync_obligatory_borrower_info Finish!', datetime.now())
def update_obligatory_person_info(): update(updateObligatoryPersonInfoSQL) print('update_obligatory_person_info Finish', datetime.now())
def update_report_person_info(): update(updateReportPersonInfoSQL) print('update_report_person_info Finish', datetime.now())
def calc_compensation_repayed_amount_interest(): """设置还代偿还款额、利息""" update(calcCompensationRepayedAmountInterestSQL) print('calc_compensation_repayed_amount_interest Finish!')