记录——影刀RPA高级操作题（一）

# 使用提醒:
# 1. xbot包提供软件自动化、数据表格、Excel、日志、AI等功能
# 2. package包提供访问当前应用数据的功能，如获取元素、访问全局变量、获取资源文件等功能
# 3. 当此模块作为流程独立运行时执行main函数
# 4. 可视化流程中可以通过"调用模块"的指令使用此模块

import xbot
from xbot import print, sleep
from .import package
from .package import variables as glv
import requests
import mysql.connector
import json

def main(args):

    pass


"获取数据"
def get_data():
    url = 'http://43.143.30.32:9527/movies'
    response = requests.get(url)
    if response.status_code == 200:
        print(response.json())
    else:
        print("请求失败，状态码：", response.status_code)
    sum_data = []
    for obj in response.json()["data"]:
        sum_data.append(tuple(list(obj.values())))
    sum_data = [(tup[0], tup[1], tup[2], "林丑丑", tup[4], tup[5], tup[6]) for tup in sum_data]
    return sum_data



def Connection_locat():
    '''连接本地数据库'''
    try:
        mydb = mysql.connector.connect(
            host="127.0.0.1",
            user="root",
            password="123456",
            database="taobao",
            charset='utf8'
        )
        if mydb.is_connected():
            return mydb
    except Exception as e:
        print(f"数据库连接失败：{e}")


def create_table(table):
    '''本地数据库建表'''
    mydb = Connection_locat()
    mycursor = mydb.cursor()
    try:
        create_table_sql = f"CREATE TABLE {table}(year VARCHAR(100),diqu VARCHAR(100),daoyan VARCHAR(100),tijiaoren VARCHAR(100),mo_name VARCHAR(100),piaofang VARCHAR(100),pinfen VARCHAR(100))"
        query_table_sql = f"SHOW TABLES LIKE '{table}'"
        mycursor.execute(query_table_sql)
        result = mycursor.fetchall()
        if len(result) == 0:
            mycursor.execute(create_table_sql)
            mycursor.execute(f"ALTER TABLE {table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            mydb.commit()
        else:
            #print("《dianyin》，此表已存在")
            pass
    except Exception as e:
         print(f"建表失败：{e}")
    finally:
        mycursor.close()
        mydb.close()


def write_sql(data,table):
    '''获取待到的数据写入本地数据库'''
    mydb = Connection_locat()
    mycursor = mydb.cursor()
    try:
        query_data_sql = f"select * from {table}"
        mycursor.execute(query_data_sql)
        result = mycursor.fetchall()
        if len(result) > 0:
            #print("数据已存在，无需新增数据")
            pass
        else:
            sql = f"INSERT INTO {table} (year, diqu, daoyan, tijiaoren, mo_name, piaofang, pinfen) VALUES (%s, %s, %s, %s, %s, %s, %s)"
            mycursor.executemany(sql, data)
            mydb.commit()
            print("成功写入数据")
    except Exception as e:
         print(f"写入数据失败：{e}")
    finally:
        mycursor.close()
        mydb.close()



def check_data_sql(sql):
    '''查询统计数据'''
    result = []
    mydb = Connection_locat()
    mycursor = mydb.cursor()
    try:
        mycursor.execute(sql)
        result = mycursor.fetchall()
    except Exception as e:
         print(f"查询数据失败：{e}")
    finally:
        mycursor.close()
        mydb.close()
    return result