[SOLVED] Query MySQL database with Python using sshtunnel, with SSH ppk key file

Issue

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 PuTTYgen). 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.

Solution

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

Answered By - Martin Prikryl

Answer Checked By - Terry (WPSolving Volunteer)