/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 vote313 views1 answer
Python loc id in list
Python
0 vote334 views1 answer
Python urlparse get domain
Python
2 votes326 views1 answer
Read database pandas
Python
0 vote329 views1 answer
Updateview
Python
0 vote297 views1 answer
Pandas create average per group
Python
0 vote340 views1 answer
Django form field add attrs
Python Django
0 vote439 views2 answers
Make entry bigger in tkinter python
Python
1 vote316 views3 answers
Pandas add column from list
Python
0 vote370 views1 answer
Django test imagefield
Python Django
0 vote338 views1 answer
Standardizing column names pandas
Python