tj-factory_Person_v1_to_v2.py

说明：该脚本把21的mysql数据库factory_cloud.personnel表里的数据迁移到185的mysql数据库tj_factory_prod.bd_person表里，这2个表字段不一致，只要2个表相关联的字段。

 

import pymysql
import sys
import datetime
from time import strftime
##################在21找出人员数据######################
try:
    mysql_connetion_21 = pymysql.connect(host='192.168.1.21',user='project_ifactorys_manager',passwd='88a78967-a282-4166-a0e8-ee409f893935',port=3306)
except:
        print("mysql连接失败！") 
        sys.exit(1)
select_sql = "select name,gender,birthday,phone,credentials_type,credentials_number,starttime,endtime,sync_status,is_black,enable_state,black_time,worker_name,depart_code from factory_cloud.personnel ;"
cursor_21 = mysql_connetion_21.cursor()
cursor_21.execute(select_sql)
results_21 = list(cursor_21.fetchall())


now_datetime = strftime("%Y-%m-%d %H:%M:%S")
print(now_datetime)
print(results_21)
#-------------------在21找出人员数据-------------------------------

####################在185的bd_person表插入数据##########################################
mysql_connetion_185 = pymysql.connect(host='172.16.13.185',user='root',passwd='sykj_2022',port=3306)
cursor_185 = mysql_connetion_185.cursor()
for line in results_21:
    line = list(line)
    print(line)
    # 证件类型
    line[4] = line[4] + 1
    # 是否可用
    if int(line[10]) == 0:
        line[10] = 2
    elif int(line[10]) == 1:
        line[10] = 0
    # 黑名单创建时间
    
    # insert_sql = 'insert into tj_factory_prod.bd_person(name,type,sex,birthday,phone,credentials_type,credentials_number,effective_begin,effective_end,sync_status,sync_message,is_black,step_val,del_flag,create_by,update_by,create_time,user_id,dept_id,black_time) values("{}",{},{},"{}","{}",{},"{}","{}","{}",{},"{}",{},{},{},"{}","{}","{}",{},{},"{}")'.format(line[0],1,line[1],line[2],line[3],line[4],line[5],line[6],line[7],0,'未开始',line[9],1,line[10],'admin','admin',now_datetime,1,100,line[11])
    insert_sql = 'insert into tj_factory_prod.bd_person(name,type,sex,birthday,phone,credentials_type,credentials_number,effective_begin,effective_end,sync_status,sync_message,is_black,step_val,del_flag,create_by,update_by,create_time,user_id,dept_id,work_type) values("{}",{},{},"{}","{}",{},"{}","{}","{}",{},"{}",{},{},{},"{}","{}","{}",{},{},"{}")'.format(line[0],1,line[1],line[2],line[3],line[4],line[5],line[6],line[7],1,'同步成功',line[9],1,line[10],'admin','admin',now_datetime,1,100,'钢筋工')
    # print(insert_sql)
    try:
        cursor_185.execute(insert_sql)
        mysql_connetion_185.commit()
    except Exception as e:
        print(e)
        print("插入bd_person表失败")
        sys.exit(1)
 #-----------------------#在185的bd_person表插入数据----------------------------------------------------   
 
 ####################在185的bd_person_dept_post表插入数据##########################################
    #根据身份证号码找person 人的id
    cursor_185.execute('select id from tj_factory_prod.bd_person where credentials_number="{}";'.format(line[5]))
    person_id = cursor_185.fetchone()[0]
    # print('select id from tj_factory_prod.bd_person where credentials_number="{}";'.format(line[5]))
    
    #根据岗位名称找post_id  岗位id
    post_select = 'select post_id from tj_factory_prod.sys_post where post_name = "{}";'.format(str(line[12]))
    cursor_185.execute(post_select)
    post = cursor_185.fetchone()
    if not post:
        post_id = 4
    else:
        post_id = post[0]
        
    #在21根据depart_code在base_depart表找name 部门名称
    depart_select = 'select name from factory_pms.base_depart where code="{}";'.format(str(line[13]))
    cursor_21.execute(depart_select)
    depart = cursor_21.fetchone()
    if depart:
        depart_name = depart[0]
    else:
        depart_name = '自有工人'
    #根据部门名称找dept_id 部门id
    dept_select = 'select dept_id from tj_factory_prod.sys_dept where dept_name = "{}" and del_flag = 0;'.format(str(depart_name))
    cursor_185.execute(dept_select)
    dept = cursor_185.fetchone()
    if not dept:
        dept_id = 223
    else:
        dept_id = dept[0]
        
    insert_sql = "insert into tj_factory_prod.bd_person_dept_post(person_id,dept_id,post_id,is_main) values({},{},{},1);".format(person_id,dept_id,post_id)
    try:
        cursor_185.execute(insert_sql)
        mysql_connetion_185.commit()
    except Exception as e:
        print(e)
        print("插入bd_person_dept_post表失败")
        sys.exit(1)
 #------------------------在185的bd_person_dept_post表插入数据----------------------------------------
        
cursor_185.close()
    
mysql_connetion_185.close()
    
cursor_21.close()
mysql_connetion_21.close()