import paramiko
import pymysql
from sshtunnel import SSHTunnelForwarder

# SSH settings
ssh_host = '129.159.146.88'
ssh_user = 'ubuntu'
ssh_key_path = 'C:/Users/kerts/OneDrive/Documents/Keys/Ubuntu_Oracle/ssh-key-2023-02-12.key'

# MySQL settings
mysql_host = 'localhost'  # because we will connect through the SSH tunnel
mysql_port = 3306  # the default MySQL port
mysql_user = 'root'
mysql_password = 'naP2tion'
mysql_db = 'warbot'

# create an SSH client and load the private key
ssh_client = paramiko.SSHClient()
ssh_client.load_system_host_keys()
ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
private_key = paramiko.RSAKey.from_private_key_file(ssh_key_path)

# start the SSH tunnel using sshtunnel
with SSHTunnelForwarder(
        (ssh_host, 22),
        ssh_username=ssh_user,
        ssh_pkey=private_key,
        remote_bind_address=(mysql_host, mysql_port)) as tunnel:
    
    # connect to the MySQL server through the SSH tunnel
    mysql_conn = pymysql.connect(
        host='localhost',
        port=tunnel.local_bind_port,
        user=mysql_user,
        password=mysql_password,
        db=mysql_db
    )

    # send a query to the MySQL database and print the response table
    with mysql_conn.cursor() as cursor:
        query = 'SELECT * FROM conversations WHERE username = "user1";'
        cursor.execute(query)
        rows = cursor.fetchall()
        for row in rows:
            print(row)

    # close the MySQL connection
    mysql_conn.close()

# close the SSH client
ssh_client.close()

# query = 'SELECT * FROM conversations WHERE username = "user1";'