python----/数据库/本地/杂项.ipynb

In [1]:

import pymysql
import snowflake
from faker import Faker
import random
import datetime
fake = Faker('zh_CN')
import random
# 创建数据库连接
connection = pymysql.connect(
host='192.168.0.244',
port=3306,
user='root',
password='Abcd@123456',
database='hongxx',
charset='utf8'
)
# 创建游标对象
cursor = connection.cursor()

In [2]:

def create_table(cursor,result):
    
    data = []
    for i in result:
        list_list = list(i)
        des=cursor.description  # 获取表详情，字段名，长度，属性等
        t = ",".join([item[0] for item in des])
        table_head = t.split(',')  # # 查询表列名 用,分割
    
        dict_result = dict(zip(table_head, list_list))  # 打包为元组的列表 再转换为字典
        data.append(dict_result)  # 将字典添加到list_result中
    return data

In [26]:

sql = 'select * from sys_user where dept_id=100'

In [27]:

cursor.execute(sql)
rows = cursor.fetchall()
result = create_table(cursor,rows)

In [28]:

[i["user_id"] for i in result]

Out [28]:

[2,
 3,
 4,
 6,
 24,
 31,
 308,
 909,
 917,
 918,
 1145,
 3690,
 3698,
 3828,
 3842,
 3843,
 3844,
 3895,
 3899,
 3912,
 3913,
 3935,
 3936,
 3988,
 5440,
 5492,
 7696,
 7713,
 7714,
 7723,
 7728,
 2059454179031199746]

In [25]:

for item in result:
    userid = item["user_id"]
  
    sql2 = f"update sys_user set dept_id=100, team_user_type ='BACKEND' where user_id={userid}"
    cursor.execute(sql2)
connection.commit()

In [24]:

for item in result:
    userid = item["user_id"]
    sql2 = f"update sys_user_dept set dept_id=100, person_type='hx_sys' where user_id={userid}"
    cursor.execute(sql2)
connection.commit()

In [ ]:

for item in result:
    userid = item["user_id"]
    sql2 = f"update sys_user_role set dept_id=100 where user_id={userid}"
    cursor.execute(sql2)
connection.commit()