记录——影刀RPA高级操作题（二）——使用影刀RPA内置包和”Xpath“获取数据，获取数据写入目标MySQL

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

import xbot
from xbot import print, sleep, web
from .import package
from .package import variables as glv
import re
import math
import mysql.connector

def main(args):
    
    pass


def data():
    '''获取数据'''
    web_page = xbot.web.create(url="http://www.boxofficecn.com/the-red-box-office", mode="chrome")
    sum_data = re.search(r'共 (\d+) 项',web_page.find_by_xpath('//div[@id="tablepress-4_info"]').get_text()).group(1)
    sum_page = math.ceil(int(sum_data) / 50)
    sum_data = []
    for idx in range(sum_page):
        mv_ele = web_page.find_all_by_xpath('//tbody/tr')
        for obj in mv_ele:
            mv_name = obj.children()[1].get_text().split("（")[0]
            mv_year = obj.children()[0].get_text()
            mv_diqu = country(obj.children()[0].children()[0].get_attribute("src"))
            mv_denshu = obj.children()[1].get_text().split("（")[1].replace("）",'')
            mv_daoyan = obj.children()[2].get_text()
            mv_piaofan = re.findall(r'\d+',obj.children()[3].get_text())[0]
            sum_data.append(tuple([mv_name,mv_year,mv_diqu,mv_denshu,mv_daoyan,mv_piaofan,"林丑丑"]))
        next_page_ele = web_page.find_by_xpath("//div[@id='tablepress-4_paginate']/a[2]")
        next_page_ele.click()
    print(f"需要写入数据库数据：{sum_data}")
    web_page.close()
    #数据写入数据库
    write_sql(sum_data)
    return None


def country(country_url):
    data = {
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1e8-1f1f3.svg":"中国",
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1fa-1f1f8.svg":"美国",
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1ee-1f1f3.svg":"印度",
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1ef-1f1f5.svg":"日本",
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1ec-1f1e7.svg":"英国",
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1eb-1f1f7.svg":"法国",
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1eb-1f1ee.svg":"芬兰",
        "https://s.w.org/images/core/emoji/12.0.0-1/svg/1f1e6-1f1fa.svg":"美国"
    }
    if country_url in data:
        return data[country_url]
    else:
        return None


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


    
def write_sql(data):
    '''获取到的数据写入数据库'''
    mydb = Connection_locat()
    mycursor = mydb.cursor()
    try:
        sql = "INSERT INTO movies (电影名称, 上映年份, 制片地区, 评分, 导演, 票房, 提交人) 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()