/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 vote166 views1 answer
Crank nicholson scheme python
Python
0 vote155 views1 answer
Create animation from sequence of image python
Python
4 votes152 views3 answers
Display prime numbers between two intervals in python
Python
0 vote114 views1 answer
How to extract keys from dictreader python
Python
44 votes172 views3 answers
Python string cut last character
Python
17 votes164 views6 answers
Euclidean distance python
Python
4 votes225 views4 answers
Change default python version
Python
186 votes256 views6 answers
How to read file in python
Python
1 vote159 views1 answer
Python catch sigterm
Python
2 votes160 views1 answer
How to change button background color while clicked tkinter python
Python