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!')
