import sshtunnel

import mssql_python
import pymssql
import pyodbc


# Fill in with your credentials
ssh_host = {"ssh_address_or_host": "host",
            "ssh_username": "",
            "ssh_password": "",
            "ssh_port": 22,
            "local_bind_address": ('127.0.0.1', 0),
            }

sql_server = {"host": "DB_HOST_IP",
              "port": 1433,
              "database": "my_database",
              "user": "",
              "password": "",
              }

tunnel = sshtunnel.open_tunnel(
    (ssh_host['ssh_address_or_host'], ssh_host["ssh_port"]),
    ssh_username=ssh_host["ssh_username"],
    ssh_password=ssh_host["ssh_password"],
    local_bind_address=ssh_host["local_bind_address"],
    remote_bind_address=(sql_server['host'], sql_server['port']))

try:
    tunnel.start()

    ### Connect with pyodbc
    pyodbc_conn_str = (
        f"Driver={{ODBC Driver 17 for SQL Server}};"
        f"Server={tunnel.local_bind_host},{tunnel.local_bind_port};"
        f"DATABASE={sql_server['database']};"
        f"UID={sql_server['user']};"
        f"PWD={sql_server['password']};"
        "Encrypt=no;TrustServerCertificate=yes")
    conn = pyodbc.connect(pyodbc_conn_str)
    curs = conn.cursor()
    curs.execute("SELECT 'hello from pyodbc'")
    print(curs.fetchall())  ### This works

    ### Connect with mssql_python
    CONNECTION_STRING = (
        f"Server={tunnel.local_bind_host},{tunnel.local_bind_port};"
        f"DATABASE={sql_server['database']};"
        f"UID={sql_server['user']};"
        f"PWD={sql_server['password']};"
        "Encrypt=no;TrustServerCertificate=yes")

    conn = mssql_python.connect(CONNECTION_STRING)  ### This hangs.
    print(conn)  ### We never get to here
    res = conn.execute("SELECT 'hello from mssql_python'")
    print(res.fetchall())

finally:
    tunnel.stop()