/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 votes167 views1 answer
Django empty queryset
Python
3 votes148 views1 answer
Parquet to csv
Python
0 vote160 views3 answers
Python convert dat file to csv
Python
0 vote153 views1 answer
Finns = False
Python
-1 vote226 views1 answer
Install grepper for edge
Python
4 votes168 views1 answer
How to loop the length of an array pytoh
Python
0 vote147 views1 answer
Loginrequiredmixin django
Python
0 vote152 views1 answer
How to make a distance function in python
Python
30 votes172 views1 answer
If dir not exist mkdir python
Python
0 vote164 views1 answer
Validating credit card numbers
Python