Query MySQL database with python using sshtunnel, with password vs ssh keys/ppk file-CodePudding

I have been able to sshtunnel into a remote database via the following code utilizing passwords

import sshtunnel
import mysql.connector
import numpy as np
import pandas as pd

host =""
username=""
password=""
tunnel_username=""
tunnel_password=""


with sshtunnel.SSHTunnelForwarder(
    (host, 22),
    ssh_username=username,
    ssh_password=password,
    remote_bind_address=('localhost', 3306),
    local_bind_address=('0.0.0.0', 3306)
) as tunnel:
    connection = mysql.connector.connect(
    user= tunnel_username,
    password= tunnel_password,
    host='localhost',
    database= database,
    port=3306)
    data = pd.read_sql_query(query, connection)
    connection.close()
    print(data)

However, circumstances have changed, and I have been forced to only connect via ssh keys (generated with putty gen). With that being said, I have the private key (ppk file), but it is unclear what I need to do (or if possible) to get the following code to work again.

I have not seen an option to reference the ppk file path instead of the sshtunnel password.

CodePudding user response：

Use ssh_pkey parameter of SSHTunnelForwarder constructor to provide the path to your private key file.

And you will need to convert your private key file to the OpenSSH format, as Paramiko (used by sshtunnel) does not support PuTTY key format.
See How to ssh connect through python Paramiko with ppk public key