from sshtunnel import SSHTunnelForwarder
server = SSHTunnelForwarder(
        (host_jump, int(port_jump)),  # 跳板機(jī)的配置
        ssh_pkey=ssh_pk_jump, 
        #密鑰的訪問(wèn)方式，如果是密碼 ssh_password=？？？
        ssh_username=user_name_jump,
        remote_bind_address=(host_mysql, int(port_mysql)))  # 數(shù)據(jù)庫(kù)服務(wù)器的配置
        local_bind_address=(local_ip, local_port)  # 開(kāi)啟本地轉(zhuǎn)發(fā)端口
        ）
	server.start()
	server.close()

from sshtunnel import SSHTunnelForwarder
with SSHTunnelForwarder(
        (host_jump, int(port_jump)),  # 跳板機(jī)的配置
        ssh_pkey=ssh_pk_jump, 
        #密鑰的訪問(wèn)方式，如果是密碼 ssh_password=？？？
        ssh_username=user_name_jump,
        remote_bind_address=(host_mysql, int(port_mysql)))  # 數(shù)據(jù)庫(kù)服務(wù)器的配置
        local_bind_address=(local_ip, local_port)  # 開(kāi)啟本地轉(zhuǎn)發(fā)端口
        ）as server
	server.start()

with SSHTunnelForwarder(
        (host_jump, int(port_jump)),  # 跳板機(jī)的配置
        ssh_pkey=ssh_pk_jump, 
        #密鑰的訪問(wèn)方式，如果是密碼 ssh_password=？？？
        ssh_username=user_name_jump,
        remote_bind_address=(host_mysql, int(port_mysql)))  # 數(shù)據(jù)庫(kù)服務(wù)器的配置
        local_bind_address=(local_ip, local_port)  # 開(kāi)啟本地轉(zhuǎn)發(fā)端口
        ）as server
	server.start()
    conn = mysql.connector.connect(**{'host': '127.0.0.1',
                              'port': server.local_bind_port,
                              'user': dbcfg["user"],
                              'password': dbcfg["password"],
                              'db': dbcfg["db"],
                              'charset': dbcfg["charset"],
                              'collation': dbcfg["collation"],
                              'use_unicode': dbcfg["use_unicode"],
                              'use_pure': True})
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, params)
        rows = cursor.fetchall()
        # conn.commit()
        cursor.close()
        conn.close()

def get_slave_conn_byProxy(dbcfg):
    server = SSHTunnelForwarder(
        eval(dbcfg["addr"]),
        ssh_username=dbcfg["ssh_username"],
        ssh_pkey=os.getenv("HOME") + "/.ssh/id_rsa",
        remote_bind_address=eval(dbcfg["remote_bind_address"]),
        local_bind_address=eval(dbcfg["local_bind_address"])  # 開(kāi)啟本地轉(zhuǎn)發(fā)端口
    )
    server.start()
    conn = get_db_connection({'host': '127.0.0.1',
                              'port': server.local_bind_port,
                              'user': dbcfg["user"],
                              'password': dbcfg["password"],
                              'db': dbcfg["db"],
                              'charset': dbcfg["charset"],
                              'collation': dbcfg["collation"],
                              'use_unicode': dbcfg["use_unicode"],
                              'use_pure': True})
    return server, conn

def get_db_connection(dbcfg):
    # logger.info(f"============ debug db config: {dbcfg}")
    conn = mysql.connector.connect(**dbcfg)
    return conn

# 通過(guò)輸入的連接參數(shù)判斷是否需要通過(guò)跳板機(jī)的方式
def get_slave_conn(dbcfg):
    if "addr" in dbcfg:
        return get_slave_conn_byProxy(dbcfg)
    else:
        conn = get_db_connection(dbcfg)
        return None, conn
       
def read_slave_db_data(sql, conn, params=None, use_pdf=True):
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, params)
        rows = cursor.fetchall()
        # conn.commit()
        cursor.close()
        if use_pdf:
            return pd.DataFrame(rows)
        else:
            return rows