/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 vote187 views1 answer
Django redirect to external url
Python
0 vote192 views8 answers
Empaquetado y manejo dependencias en python
Python
0 vote252 views1 answer
If list is null python apply any function site:stackoverflow.com
Python
4 votes204 views6 answers
How to change python version
Python
0 vote208 views2 answers
Generate secret key python
Python
2 votes230 views1 answer
Python gui drag and drop
Python
1 vote225 views1 answer
Number of column in dataset pandas
Python
0 vote226 views1 answer
How to let something see on screen with python
Python
0 vote219 views1 answer
Python import colors
Python
8 votes205 views6 answers
Convert integer to binary python
Python