douyin-spider/zmysql.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import traceback

from dbutils.pooled_db import PooledDB
import pymysql
from pymysql.cursors import DictCursor
from contextlib import contextmanager


def singleton(class_):
    instances = {}

    def get_instance(*args, **kwargs) -> BaseDb:
        if class_ not in instances:
            instances[class_] = class_(*args, **kwargs)
        return instances[class_]

    return get_instance


class BaseDb:

    @staticmethod
    def __build_conn(db_key):
        db_config = {
            'shanxi': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'shanxi',
                'charset': 'utf8mb4'
            },
            'chengyi': {
                'host': '172.16.101.19',
                'user': 'root',
                'port': 3306,
                'password': '*Root123',
                'db': 'ods',
                'charset': 'utf8mb4'
            },
            'bigdata': {
                'host': '172.16.101.11',
                'user': 'root',
                'port': 3307,
                'password': 'msstar',
                'db': 'bigdata',
                'charset': 'utf8mb4'
            },
            'bigdata-gw': {
                'host': '113.142.79.114',
                'user': 'root',
                'port': 3307,
                'password': 'msstar',
                'db': 'bigdata',
                'charset': 'utf8mb4'
            },
            'guns': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'guns',
                'charset': 'utf8mb4'
            },
            'dangjian-test': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'dangjian-test',
                'charset': 'utf8mb4'
            },
            'kaohe': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'kaohe',
                'charset': 'utf8mb4'
            },
            'weibo': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'weibo',
                'charset': 'utf8mb4'
            },
            'collect': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'collect',
                'charset': 'utf8mb4'
            },
            'open': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'open',
                'charset': 'utf8mb4'
            },
            'spider': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'spider',
                'charset': 'utf8mb4'
            },
            'operate': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'operate',
                'charset': 'utf8mb4'
            },
            'tvad': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'tv_ad',
                'charset': 'utf8mb4'
            },
            'fanruan': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'fanruan',
                'charset': 'utf8mb4'
            },
            'qingbo': {
                'host': '172.16.101.10',
                'user': 'shanxiguangdian',
                'port': 3306,
                'password': 'swfgVAKWhCfIFVs9',
                'db': 'shanxiguangdian',
                'charset': 'utf8mb4'
            },
            'palo': {
                'host': '172.16.101.2',
                'user': 'edap',
                'port': 9021,
                'password': 'Du8Pwv5T',
                'db': 'ods',
                'charset': 'utf8mb4'
            },
            'palo-gw': {
                'host': '113.142.79.114',
                'user': 'edap',
                'port': 9021,
                'password': 'Du8Pwv5T',
                'db': 'ods',
                'charset': 'utf8mb4'
            },
            'palo_eds': {
                'host': '172.16.101.2',
                'user': 'edap',
                'port': 9021,
                'password': 'Du8Pwv5T',
                'db': 'eds',
                'charset': 'utf8mb4'
            },
            'palo_cxzx': {
                'host': '172.16.101.2',
                'user': 'edap',
                'port': 9021,
                'password': 'Du8Pwv5T',
                'db': 'cxzx',
                'charset': 'utf8mb4'
            },
            'local': {
                'host': '127.0.0.1',
                'user': 'root',
                'port': 3306,
                'password': '123456',
                'db': 'bak',
                'charset': 'utf8mb4'
            },
            'tidb-kuyun': {
                'host': '172.16.101.1',
                'user': 'root',
                'port': 4000,
                'password': 'msstar',
                'db': 'kuyun',
                'charset': 'utf8mb4'
            },
            'tidb-qdxw': {
                'host': '172.16.101.1',
                'user': 'root',
                'port': 4000,
                'password': 'msstar',
                'db': 'qdxw',
                'charset': 'utf8mb4'
            },
            'tidb-youmei': {
                'host': '172.16.101.1',
                'user': 'root',
                'port': 4000,
                'password': 'msstar',
                'db': 'youmei',
                'charset': 'utf8mb4'
            },
            'tidb-bigdata': {
                'host': '172.16.101.1',
                'user': 'root',
                'port': 4000,
                'password': 'msstar',
                'db': 'bigdata',
                'charset': 'utf8mb4'
            },
            'mysql_test': {
                'host': '172.16.101.5',
                'user': 'root',
                'port': 33061,
                'password': 'msstar',
                'db': 'zyx',
                'charset': 'utf8mb4'
            },
            'rexian-yunos-sync': {
                'host': '172.16.101.5',
                'user': 'root',
                'port': 33060,
                'password': 'msstar',
                'db': 'rexian-yunos',
                'charset': 'utf8mb4'
            },
            'report_forms': {
                'host': 'rm-2vc3039h858t9ab7sfo.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'cxzx',
                'port': 3306,
                'password': 'sxtvs53$68HD',
                'db': 'report_forms',
                'charset': 'utf8mb4'
            },
            'bm': {
                'host': '172.16.16.110',
                'user': 'ssp',
                'port': 3306,
                'password': 'ssp@0518',
                'db': 'rexian-yunos',
                'charset': 'utf8mb4'
            },
            'hqy': {
                'host': '10.30.161.60',
                'user': 'hqy-readonly01',
                'port': 3306,
                'password': 'HqyR198kd#sd',
                'db': 'webtv_cms',
                'charset': 'utf8mb4'
            },
            'tide': {
                'host': 'rm-2vcyu6uzd1n5m9f756o.mysql.cn-chengdu.rds.aliyuncs.com',
                'user': 'sxsdst',
                'port': 3306,
                'password': 'xfWzxQ@4dfDv',
                'db': 'tidemedia_cms',
                'charset': 'utf8mb4'
            },
            'xha-idaas': {  
                'host': '10.30.162.15',
                'user': 'root',
                'port': 3306,
                'password': 'AdminStarv2022',
                'db': 'idaas',
                'charset': 'utf8mb4'
            },
            'jiaoda': {
                'host': '10.30.135.194',
                'user': 'root',
                'port': 3306,
                'password': 'root',
                'db': 'scas',
                'charset': 'utf8mb4'
            },
            'ssp-old':{
                'host': '47.108.249.49',
                'user': 'cxzx',
                'port': 7001,
                'password': '123456',
                'db': 'member',
                'charset': 'utf8mb4'
            }
        }
        return PooledDB(pymysql, 1, cursorclass=DictCursor, **db_config[db_key])

    def __init__(self, db_key) -> None:
        self.__mysql_pool = self.__build_conn(db_key)

    def get_conn(self):
        return self.__mysql_pool.connection()

    def query(self, sql, args=None):
        with self.open_tx() as s:
            return s.query(sql, args)

    def query_one(self, sql, args=None):
        with self.open_tx() as s:
            return s.query_one(sql, args)

    def update(self, sql, args=None):
        with self.open_tx() as s:
            s.update(sql, args)

    def update_many(self, sql, args=None):
        with self.open_tx() as s:
            s.update_many(sql, args)

    def insert(self, table, columns, args):
        with self.open_tx() as s:
            s.insert(table, columns, args)

    def insert_default(self, table, args):
        with self.open_tx() as s:
            s.insert_default(table, args)

    @contextmanager
    def open_tx(self):
        """
        开启事务
        :return: 开启事务的会话
        """
        session = Session(self.__mysql_pool.connection())
        try:
            yield session
            session.conn.commit()
        except:
            session.conn.rollback()
            raise Exception("sql error")
        finally:
            session.conn.close()


@singleton
class MysqlTest(BaseDb):
    def __init__(self) -> None:
        super().__init__('mysql_test')


@singleton
class MysqlRexian(BaseDb):
    def __init__(self) -> None:
        super().__init__('rexian-yunos-sync')

@singleton
class QingBo(BaseDb):
    def __init__(self) -> None:
        super().__init__('qingbo')


@singleton
class FanRuan(BaseDb):
    def __init__(self) -> None:
        super().__init__('fanruan')


@singleton
class KaoHe(BaseDb):
    def __init__(self) -> None:
        super().__init__('kaohe')


@singleton
class ReportFormsDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('report_forms')


@singleton
class BigDataDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('bigdata')

@singleton
class BigDataGwDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('bigdata-gw')

@singleton
class ShanxiDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('shanxi')


@singleton
class PaloDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('palo')

@singleton
class PaloGwDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('palo-gw')

@singleton
class PaloEdsDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('palo_eds')


@singleton
class PaloCxzxDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('palo_cxzx')


@singleton
class GunsDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('guns')


@singleton
class ChengyiDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('chengyi')


@singleton
class DangjianTest(BaseDb):
    def __init__(self) -> None:
        super().__init__('dangjian-test')


@singleton
class CollectDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('collect')


@singleton
class OpenDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('open')


@singleton
class OperateDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('operate')


@singleton
class SpiderDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('spider')

@singleton
class TvAdDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('tvad')


@singleton
class TiKuyunDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('tidb-kuyun')

@singleton
class TiYoumeiDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('tidb-youmei')

@singleton
class TiKuyunDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('tidb-kuyun')

@singleton
class TiQdxw(BaseDb):
    def __init__(self) -> None:
        super().__init__('tidb-qdxw')

@singleton
class TiBigDataDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('tidb-bigdata')

@singleton
class FanruanDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('fanruan')


@singleton
class WeiboDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('weibo')


@singleton
class LocalDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('local')


@singleton
class BMDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('bm')

@singleton
class HqyDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('hqy')



@singleton
class TideDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('tide')

@singleton
class XhaIdaasDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('xha-idaas')

@singleton
class JiaodaDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('jiaoda')

@singleton
class SspOldDb(BaseDb):
    def __init__(self) -> None:
        super().__init__('ssp-old')

class Session:
    def __init__(self, conn) -> None:
        self.conn = conn

    def update(self, sql, args=None):
        cursor = self.conn.cursor()
        cursor.execute(sql, args)
        cursor.close()

    def update_many(self, sql, args=None):
        cursor = self.conn.cursor()
        cursor.executemany(sql, args)
        cursor.close()

    def query(self, sql, args=None):
        # print(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql, args)
        data = cursor.fetchall()
        cursor.close()
        return data

    def query_one(self, sql, args=None):
        cursor = self.conn.cursor()
        cursor.execute(sql, args)
        data = cursor.fetchone()
        cursor.close()
        return data

    def insert(self, table, columns, args):
        cursor = self.conn.cursor()
        size = len(columns)
        if size < 1:
            raise Exception('列不能为空')
        column_sql = ','.join([f'`{x}`' for x in columns])
        values_sql = ','.join(['%s' for i in range(size)])
        sql = f'insert into {table} ({column_sql}) values ({values_sql})'

        cursor.executemany(sql, args)
        cursor.close()

    def insert_default(self, table, args):
        cursor = self.conn.cursor()
        if len(args) < 1:
            return
        size = len(args[0])
        values_sql = ','.join(['%s' for i in range(size)])
        sql = f'insert into {table} values ({values_sql})'

        cursor.executemany(sql, args)
        cursor.close()