templates_mysql.py

import time
import traceback

from sqlalchemy import create_engine


class TemplatesMysql(object):

    def __init__(self, site_name='us'):
        self.DB_CONN_DICT = {
            "mysql_port": "3306",
            "mysql_db": "selection",
            "mysql_user": "adv_yswg",
            # "mysql_pwd": "S4FeR09bFF441lTz",
            "mysql_pwd": "HCL1zcUgQesaaXNLbL37O5KhpSAy0c",
            "mysql_host": "rm-wz9yg9bsb2zf01ea4yo.mysql.rds.aliyuncs.com",
        }
        self.DB_CONN_DICT_PG = {
            "mysql_port": "5433",
            "mysql_db": "selection",
            "mysql_user": "postgres",
            # "mysql_pwd": "S4FeR09bFF441lTz",
            "mysql_pwd": "fazAqRRVV9vDmwDNRNb593ht5TxYVrfTyHJSJ3BS",
            "mysql_host": "192.168.10.217",
        }
        self.site_name = site_name
        self.engine = self.mysql_connect()
        self.engine_pg = self.pg_connect()

    def mysql_connect(self):
        """
        Connection of mysql.
        """
        nums = 0
        while True:
            nums += 1
            try:
                if self.site_name == 'us':
                    db = 'selection'
                else:
                    db = f'selection_{self.site_name}'
                return create_engine(
                    f'mysql+pymysql://{self.DB_CONN_DICT["mysql_user"]}:' + f'{self.DB_CONN_DICT["mysql_pwd"]}@{self.DB_CONN_DICT["mysql_host"]}:{self.DB_CONN_DICT["mysql_port"]}/{db}?charset=utf8mb4')  # , pool_recycle=3600
            except Exception as e:
                print("error_mysql_connect:", e, f"\n{traceback.format_exc()}")
                time.sleep(nums * 20)
                continue

    def pg_connect(self):
        """
        Connection of mysql.
        """
        nums = 0
        while True:
            nums += 1
            try:
                if self.site_name == 'us':
                    db = 'selection'
                else:
                    db = f'selection_{self.site_name}'
                return create_engine(
                    f'postgresql+psycopg2://{self.DB_CONN_DICT_PG["mysql_user"]}:' + f'{self.DB_CONN_DICT_PG["mysql_pwd"]}@{self.DB_CONN_DICT_PG["mysql_host"]}:{self.DB_CONN_DICT_PG["mysql_port"]}/{db}')  # , pool_recycle=3600
            except Exception as e:
                print("error_mysql_connect:", e, f"\n{traceback.format_exc()}")
                time.sleep(nums * 20)
                continue

    def mysql_reconnect(self, table_name=None, e=None):
        """
        Repeated connection of mysql.
        """
        print(f"{table_name}表报错，等待5s继续：", e, f"\n{traceback.format_exc()}")
        self.engine = self.mysql_connect()
        time.sleep(5)