/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 vote91 views1 answer
Pie chart add outline python
Python
1 vote107 views1 answer
Python print string name in pattern
Python
21 votes114 views1 answer
How to plot a graph using matplotlib
Python
2 votes111 views1 answer
Pip vs anaconda venv
Python
0 vote120 views1 answer
Intialize 2d aray in python
Python
0 vote88 views1 answer
Python code for diamond with gap between odd rows
Python
0 vote119 views1 answer
Python read xls
Python
0 vote114 views1 answer
Weight constraints keras cnn
Python
5 votes168 views1 answer
How to lowercase list in python
Python
1 vote114 views1 answer
Convert a tuple into string python
Python