/Python/Python db access though ssh user

Python db access though ssh user

import pymysql
import paramiko
import pandas as pd
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder
from os.path import expanduser

home = expanduser('~')
mypkey = paramiko.RSAKey.from_private_key_file(home + pkeyfilepath)
# if you want to use ssh password use - ssh_password='your ssh password', bellow

sql_hostname = 'sql_hostname'
sql_username = 'sql_username'
sql_password = 'sql_password'
sql_main_database = 'db_name'
sql_port = 3306
ssh_host = 'ssh_hostname'
ssh_user = 'ssh_username'
ssh_port = 22
sql_ip = '1.1.1.1.1'

with SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_user,
        ssh_pkey=mypkey,
        remote_bind_address=(sql_hostname, sql_port)) as tunnel:
    conn = pymysql.connect(host='127.0.0.1', user=sql_username,
            passwd=sql_password, db=sql_main_database,
            port=tunnel.local_bind_port)
    query = '''SELECT VERSION();'''
    data = pd.read_sql_query(query, conn)
    conn.close()

Source: stackoverflow.com

Similar codes

2 votes205 views3 answers
Str to datetime time
Python
0 vote185 views1 answer
Split coumn of df into multiple dynamic columns
Python
0 vote222 views1 answer
Dataframe plot conditional formatting
Python
25 votes196 views6 answers
Python check if list contains elements of another list
Python
0 vote224 views1 answer
Django create object from dict
Python Django
0 vote190 views1 answer
Loop on dataframe lines python
Python
1 vote216 views1 answer
Installing python packages in visual studio code
Python
0 vote185 views1 answer
Rename pandas columns with list of new names
Python
0 vote193 views1 answer
Uneven chunks of array slices
Python
0 vote300 views1 answer
How to install flask module in vscode
Python Flask