/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 vote466 views1 answer
Check even or odd in single line
Python
1 vote414 views1 answer
How to remove plotly toolbar
Python
3 votes413 views1 answer
Python bounding box on image
Python
30 votes348 views1 answer
Python generate folder if it not exist
Python
0 vote417 views1 answer
Django.core.exceptions.ImproperlyConfigured: Field name is not valid for model
Python Django
0 vote336 views2 answers
Dataframe multiindex query
Python
0 vote344 views1 answer
RuntimeError: Tensor for 'out' is on CPU, Tensor for argument #1 'self' is on CPU, but expected them to be on GPU
Python
0 vote326 views1 answer
Half String
Python
2 votes358 views2 answers
Exclude index column pandas
Python
1 vote340 views1 answer
How to remove every other letter from a string python
Python