/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 votes151 views1 answer
Save file python tkinter
Python
16 votes161 views2 answers
Python reload import
Python
6 votes140 views4 answers
Python commenting
Python
2 votes155 views3 answers
Request session python
Python
120 votes139 views6 answers
Getting Input in Python
Python
0 vote136 views2 answers
Change markersize in legend matplotlib
Python
0 vote152 views1 answer
Selenium get h1 text python
Python
24 votes154 views4 answers
How to code in python
Python
15 votes154 views3 answers
Ffmpeg python
Python
0 vote173 views1 answer
Dynamic footer in django
Python Django