/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

0 vote154 views1 answer
Check if list elememnt in dataframe column
Python
1 vote297 views1 answer
Minimum-number-of-steps-to-reduce-number-to-1
Python
19 votes156 views3 answers
Debugger online for python
Python
0 vote151 views1 answer
Show all rows with nan for a column value pandas
Python
0 vote180 views1 answer
Gunicorn django static files
Python Django
0 vote173 views1 answer
Mp.solutions.findhands not in python 3.8
Python
0 vote150 views1 answer
Pick the element from list whihc matched with sub string
Python
8 votes158 views3 answers
How to install whl file in python
Python
21 votes180 views3 answers
Python if not null or empty
Python
0 vote152 views1 answer
Django post request 403 forbidden
Python