/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

1 vote306 views1 answer
Python get object attribute by string
Python
6 votes326 views4 answers
Flask get with parameters
Python
0 vote318 views1 answer
Tuple count in python
Python
0 vote285 views1 answer
Pandas make new dataframe
Python
0 vote290 views1 answer
Calculating difference of elemetns in array python
Python
0 vote322 views1 answer
How to check if two buttons were pressed python
Python
0 vote343 views1 answer
Untrack local changes
Python
9 votes316 views1 answer
Delete model object django
Python Django
0 vote329 views1 answer
How to send Email verification codes to user in Firebase using Python
Python
1 vote390 views1 answer
How to add captcha in django forms
Python Django