/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 vote193 views1 answer
TypeError: Object of type DictProxy is not JSON serializable
Python
2 votes181 views1 answer
Lcm math python library
Python
0 vote269 views1 answer
Arma-garch model python
Python
0 vote232 views1 answer
Django serializer get image list
Python Django
2 votes183 views1 answer
Python argparser flags
Python
4 votes193 views2 answers
Intersection in list
Python
2 votes206 views2 answers
Python number and name of weekday
Python
26 votes238 views4 answers
Python send email with attachment
Python
5 votes150 views2 answers
Bored definition
Python
18 votes177 views6 answers
Addition in python
Python