/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 vote257 views1 answer
Ajouter élément liste python
Python
1 vote273 views2 answers
Format on str
Python
11 votes285 views2 answers
Change pandas column value based on condition
Python
8 votes254 views1 answer
Find time of run for python code
Python
8 votes296 views1 answer
Python generate uid
Python
13 votes281 views2 answers
How to copy one dictionary to another in python
Python
0 vote300 views2 answers
Python dictionary rename key
Python
20 votes302 views4 answers
Python open web browser
Python
0 vote278 views1 answer
Python get last modification time of file
Python
0 vote281 views1 answer
Read the entire text file using the read() function
Python