/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

4 votes234 views5 answers
Module 'datetime' has no attribute 'now' django
Python Django
39 votes173 views6 answers
Python float precision
Python
106 votes191 views5 answers
Sorting by column in pandas
Python
1 vote207 views1 answer
Mysql Unread result found
Python
0 vote289 views2 answers
How to close a flask web server with python
Python Flask
0 vote199 views1 answer
Using Lists as Queues
Python
0 vote205 views1 answer
Networkx draw edge description
Python
3 votes237 views2 answers
Pygame bg color
Python
0 vote196 views1 answer
H2o dataframe columns drop
Python
0 vote178 views1 answer
Cls in python
Python