/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 vote185 views1 answer
Db_index django
Python
1 vote238 views1 answer
How to pass header in requests
Python
0 vote166 views1 answer
Get one from dataloader
Python
-1 vote168 views1 answer
Python date from yy/mm/dd to yy-mm-dd
Python
0 vote181 views1 answer
Plotly vertical bar chart
Python
22 votes168 views6 answers
How to install pygame in python
Python
5 votes163 views4 answers
Dropping unnamed columns in pandas
Python
0 vote242 views1 answer
How to compress image field in django?
Python
0 vote170 views1 answer
Python string and variable in 1 print
Python
12 votes170 views1 answer
Python range for float
Python