/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 vote174 views1 answer
Bad request 400 heroku app
Python
7 votes241 views1 answer
Jupyter install user environment
Python
0 vote187 views1 answer
KMeans
Python
0 vote167 views1 answer
Cbv uk django
Python
2 votes131 views3 answers
Typeerror: 'list' object is not callable
Python
1 vote194 views3 answers
How to get today weekday in python
Python
0 vote219 views1 answer
How to delete lists after using them in python
Python
81 votes185 views6 answers
String to date python
Python
0 vote210 views1 answer
Seconds to time python
Python
0 vote190 views1 answer
Pandas join tables based on column of different length
Python