应用场景

1.在某种情况下我们需要和多个系统进行对接，实现数据的预览，这就涉及到用户信息权限的同步。

实现脚本（python）

#!/usr/bin/python3
# -*- encoding: utf-8 -*-
import pymysql
//定义数据库信息字典
db_devops = {"host": "10.41.1.19", "user": "devops", "password": "123456", "dbname": "devops", "port": 3306}
db_loonflow = {"host": "127.0.0.1", "user": "root", "password": "123456", "dbname": "loonflow_r2", "port": 3306}


database2 = "devops" #生产库同步
loonselect_sql = "SELECT username FROM account_loonuser;"
#shutselect_sql = "SELECT username,password FROM user;"
shutselect_sql = "SELECT username,secret FROM permission_userprofile;"
looninsert_sql = "INSERT INTO account_loonuser(\
password,last_login,username,alias,email,phone,is_active,creator,gmt_created,\
gmt_modified,is_deleted,type_id) VALUES ( \
  %s, '2020-04-05 14:10:33', %s, '', %s, \
  '','1', '', '2020-04-05 13:51:38', '2020-04-05 14:10:32', '0', '0');"

#dept sync
loondeptselect_sql = "select name from account_loondept;"
devopsselect_sql = "select id,deptName,parentId_id from permission_dept;"
loondept_sql = "INSERT INTO account_loondept(id,name,parent_dept_id,leader,approver,label,creator,gmt_created,gmt_modified,is_deleted) values(%s,%s,%s,'','','','','2023-02-20 13:51:38','2022-02-21 14:10:32',0);"

def shutuser_select(selectsql,db_info):
    host = db_info["host"]
    user = db_info["user"]
    password = db_info["password"]
    dbname = db_info["dbname"]
    port = db_info["port"]
    conn = pymysql.connect(host=host, user=user, password=password, port=port, database=dbname)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute(selectsql)
    shutuser_dic_li = cursor.fetchall()
    cursor.close()
    conn.close()
    print("result===%s"%shutuser_dic_li)
    return shutuser_dic_li


def user_dic(loonuser_list):
    new_user_dic_li = []
    shutuser_dic_li = shutuser_select(shutselect_sql, db_devops)  # type:list
    print("======--------------------------", new_user_dic_li, shutuser_dic_li)
    for shutuser in shutuser_dic_li:
        if shutuser["username"] not in loonuser_list:
            new_user_dic_li.append(shutuser)
    return new_user_dic_li

def dept_dic(loondept_list):
    new_dept_dic_li = []
    devops_dic_li = shutuser_select(devopsselect_sql,db_devops)  # type:list
    print("devops_dic_li======", devops_dic_li,loondept_list)
    for shutuser in devops_dic_li:
        if shutuser["deptName"] not in loondept_list:
            new_dept_dic_li.append(shutuser)
    return new_dept_dic_li

def loonuser_insert(typename="user"):
    db_info = db_loonflow
    host = db_info["host"]
    user = db_info["user"]
    password = db_info["password"]
    dbname = db_info["dbname"]
    port = db_info["port"]
    conn = pymysql.connect(host=host, user=user, password=password, port=port, database=dbname)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    if typename == "user":
        cursor.execute(loonselect_sql)
        loonuser_dic_li = cursor.fetchall()
        loonuser_list = [i["username"] for i in loonuser_dic_li]
        new_user_dic_li = user_dic(loonuser_list)
        print("loonuser_list======",loonuser_list)
        if new_user_dic_li:
            data = []
            for i in new_user_dic_li:
                email = "%s@qq.com" % i["username"]
                usertup = (i["secret"], i["username"], email)
                data.append(usertup)
            cursor.executemany(looninsert_sql, data)
            conn.commit()
            cursor.close()
            conn.close()
            print("用户更新完成")
        else:
            print("没有用户同步")
    elif typename == "dept":
        cursor.execute(loondeptselect_sql)
        loondept_dic_li = cursor.fetchall()
        print("=======",loondept_dic_li)
        loondept_list = [i["name"] for i in loondept_dic_li]
        new_dept_dic_li = dept_dic(loondept_list)
        print("new_dept_dic_li",new_dept_dic_li)
        if new_dept_dic_li:
            data = []
            for i in new_dept_dic_li:
                usertup = (i["id"], i["deptName"], i["parentId_id"])
                data.append(usertup)
            print("***********************",data)
            cursor.executemany(loondept_sql, data)
            conn.commit()
            cursor.close()
            conn.close()
            print("dept更新完成")
        else:
            print("没有dept同步")
if __name__ == '__main__':
    loonuser_insert(typename="user")
    loonuser_insert(typename="dept")