/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 vote215 views1 answer
How to know if the space button has been clicked in python pygame
Python
2 votes171 views2 answers
Del all variables python
Python
0 vote185 views1 answer
Export a Pandas dataframe as a table image
Python
0 vote218 views1 answer
Flask - store object directly in a session [duplicate]
Python Flask
0 vote162 views1 answer
Stack widgets in tkinter
Python
3 votes161 views1 answer
Ipython autoreload
Python
12 votes213 views2 answers
Python tkinter window fullscreen
Python
4 votes295 views6 answers
Eval function in python
Python
0 vote200 views1 answer
Django check if get parameter exists
Python Django
0 vote174 views1 answer
Find where df series is null and print
Python