alg/functions/mysql.py

"""
Mysql Functions
"""
import pymysql


class MysqlClient(object):
    """
    MySQL工具, env默认prod版本
    """

    def __init__(self):
        mysql_config = {
            # "host": "rm-bp1159bu17li9hi94.mysql.rds.aliyuncs.com",
            "host": "rr-bp1l12ea7e9wgu947.mysql.rds.aliyuncs.com",
            "port": 3306,  # 端口号
            "user": "wx2016_longvideo",  # mysql用户名
            "passwd": "wx2016_longvideoP@assword1234",  # mysql用户登录密码
            "db": "longvideo",  # 数据库名
            "charset": "utf8mb4"  # 如果数据库里面的文本是utf8编码的，charset指定是utf8
        }
        self.connection = pymysql.connect(
            host=mysql_config['host'],  # 数据库IP地址，内网地址
            port=mysql_config['port'],  # 端口号
            user=mysql_config['user'],  # mysql用户名
            passwd=mysql_config['passwd'],  # mysql用户登录密码
            db=mysql_config['db'],  # 数据库名
            charset=mysql_config['charset']  # 如果数据库里面的文本是utf8编码的，charset指定是utf8
        )

    def select(self, sql):
        """
        查询
        :param sql:
        :return:
        """
        cursor = self.connection.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        return data

    def update(self, sql):
        """
        插入
        :param sql:
        :return:
        """
        cursor = self.connection.cursor()
        try:
            res = cursor.execute(sql)
            self.connection.commit()
            return res
        except Exception as e:
            self.connection.rollback()

    def close(self):
        """
        关闭连接
        """
        self.connection.close()


class MySQLClientSpider(object):
    """
    爬虫的 mysql 配置
    """

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

    def select(self, sql):
        """
        查询
        :param sql:
        :return:
        """
        cursor = self.connection.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        return data

    def update(self, sql):
        """
        插入
        :param sql:
        :return:
        """
        cursor = self.connection.cursor()
        try:
            res = cursor.execute(sql)
            self.connection.commit()
            return res
        except Exception as e:
            print("error", sql)
            print(e)
            self.connection.rollback()

    def close(self):
        """
        关闭连接
        """
        self.connection.close()