/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 vote124 views1 answer
Python requests json as dict
Python
5 votes178 views1 answer
Pandas version check in python
Python
1 vote140 views1 answer
Start index from 1 in python
Python
0 vote132 views1 answer
Python read in integers separated by spaces
Python
2 votes94 views3 answers
Creating a dictionary
Python
3 votes111 views3 answers
Get title beautifulsoup
Python
1 vote136 views1 answer
Python opencv number of frames
Python
8 votes181 views3 answers
F.readlines python
Python
1 vote127 views1 answer
Index of max in tensor
Python
2 votes129 views1 answer
Download youtube videos using api python
Python