/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 vote102 views1 answer
Max sum slice python 5 - autopilot
Python
22 votes99 views5 answers
If else python
Python
1 vote102 views1 answer
Multiprocessing a for loop python
Python
0 vote93 views1 answer
Pandas check match string lowercase
Python
8 votes105 views3 answers
How to playsound in python
Python
0 vote185 views1 answer
Django import settings variables
Python Django
2 votes103 views1 answer
Who is a pythonista
Python
3 votes97 views3 answers
2d array python initialize
Python
133 votes100 views6 answers
How to use input in python
Python
0 vote110 views1 answer
Generate random numbers following Poisson distribution, Geometric Distribution, Uniform Distribution, and Normal Distribution, and plot them
Python