/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 vote291 views1 answer
Seeparate string without split function python
Python
1 vote235 views1 answer
Code that works if variable has a value python
Python
0 vote284 views1 answer
Jaccard distance python
Python
0 vote270 views1 answer
Tf.data.Dataset select files with labels filter
Python
10 votes260 views2 answers
Create dict from json file python
Python
0 vote266 views1 answer
Get_scholarly_instance()
Python
0 vote234 views1 answer
Reportlab drawimage issues with png transparency background
Python
0 vote256 views1 answer
From numpy to list python
Python
15 votes317 views6 answers
Django install
Python Django
36 votes249 views6 answers
Get list file in folder python
Python