we-analysis/applications/mysql.py

import pymysql


class Mysql(object):
    """
    封装后的mysql方法
    """
    def __init__(self):
        self.connection = pymysql.connect(
            host="rm-bp1159bu17li9hi94.mysql.rds.aliyuncs.com",  # 数据库IP地址，内网地址
            port=3306,  # 端口号
            user="crawler",  # mysql用户名
            passwd="crawler123456@",  # mysql用户登录密码
            db="piaoquan-crawler",  # 数据库名
            charset="utf8mb4"  # 如果数据库里面的文本是utf8编码的，charset指定是utf8
        )

    def insert_line(self, data):
        """
        :param data: 插入Mysql的代码
        :return:
        """
        if self.index(data):
            try:
                with self.connection.cursor() as cursor:
                    sql = "INSERT INTO we_analysis_results (mini_program, task_name, subject, date, control_group, test_group1, test_group2, test_group3, test_group4, test_group5, test_group6, test_group7, test_group8, test_group9, test_group10, test_group11, test_group12, test_group13, test_group14, test_group15, test_group16, test_group17, test_group18, test_group19, test_group20, test_group21, test_group22, test_group23, test_group24) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
                    cursor.execute(sql, data)
                self.connection.commit()
            finally:
                self.connection.close()
        else:
            print("数据已经存在")

    def index(self, data):
        """
        :param data: 判断数据是否去重
        :return:
        """
        mini_program = data[0]
        task_name = data[1]
        subject = data[2]
        date = data[3]
        sql = (
            f'SELECT 1 FROM we_analysis_results where mini_program = "{mini_program}" and task_name = "{task_name}" and subject = "{subject}" and date = "{date}"')
        print(sql)
        cursor = self.connection.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        if data:
            return False
        else:
            return True