/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

15 votes296 views6 answers
Python list max value
Python
0 vote294 views1 answer
Modules django
Python
0 vote280 views1 answer
Pandas astype str still object
Python
0 vote299 views1 answer
Jupyter notebook for pdf generation
Python
0 vote299 views1 answer
How to import axes3D
Python
-2 votes299 views1 answer
How to select number by twos in a list python next to each
Python
0 vote352 views1 answer
Add favicon in django admin
Python Django
1 vote324 views1 answer
Python count variable and put the count in a column of data frame
Python
28 votes287 views6 answers
Pandas create empty dataframe
Python
2 votes299 views1 answer
Death stranding
Python