/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

-1 vote351 views2 answers
Shutil remove
Python
34 votes316 views6 answers
How to update a python package
Python
6 votes344 views3 answers
%s in python
Python
11 votes367 views3 answers
Username Python
Python
1 vote325 views1 answer
Python fdr correction
Python
0 vote333 views1 answer
Defaultdict initialize keys
Python
2 votes302 views2 answers
Django admin actions
Python Django
0 vote324 views1 answer
Powershell get list of groups and members
Python
0 vote410 views1 answer
Copy any files from one folder to another folder in python
Python
0 vote371 views1 answer
Flask echo server
Python Flask