/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

2 votes223 views2 answers
The command
Python
0 vote251 views1 answer
How to convert cost to float in python
Python
0 vote253 views1 answer
Change marker border color plotly
Python
1 vote207 views1 answer
Return json with flask
Python
24 votes219 views2 answers
How to find the data type in python
Python
51 votes203 views6 answers
Drop a column in pandas
Python
0 vote227 views1 answer
Roganrola
Python
0 vote211 views1 answer
Url encoded path using python
Python
0 vote245 views1 answer
Theta hat symbol python code
Python
0 vote271 views1 answer
Adding new column to existing DataFrame in Pandas using concat method
Python