/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

2 votes186 views2 answers
Python save image to pdf
Python
1 vote165 views1 answer
Line continuation in python
Python
0 vote188 views1 answer
Python import
Python
2 votes166 views1 answer
Kivy changing screen in python
Python
0 vote158 views1 answer
How to restore windows security
Python
2 votes171 views1 answer
Python pi value
Python
1 vote174 views1 answer
Python find the average of a list
Python
38 votes166 views6 answers
Pip is not recognized as an internal or external command
Python
0 vote227 views1 answer
List variables in session tensorflow 1
Python
0 vote168 views1 answer
How to add percentage in countplot
Python