/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

12 votes207 views3 answers
How to open any application using python
Python
3 votes188 views2 answers
Python json check if key exist
Python
1 vote212 views1 answer
How to replace a row value in pyspark dataframe
Python
1 vote225 views1 answer
How to make a bot send whatever you dm it into a server discord.py
Python
18 votes207 views3 answers
How to python
Python
0 vote212 views1 answer
Wxpython mainloop
Python
0 vote205 views1 answer
List tuple dictionary,
Python
8 votes213 views3 answers
Python split range into n groups
Python
18 votes226 views3 answers
Tkinter make window not resizable
Python
1 vote227 views1 answer
How to get input in python3
Python