/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 vote189 views1 answer
Get data from model with field name in django
Python Django
6 votes165 views2 answers
Pad zeros to a string python
Python
0 vote160 views1 answer
Balancing paranthesis python
Python
48 votes169 views2 answers
Python regex tester
Python
0 vote199 views1 answer
Django command to fetch all columns of a table
Python Django
2 votes188 views1 answer
Python strip non numeric in string
Python
0 vote176 views1 answer
Tkinter start maximized
Python
1 vote178 views1 answer
Write a Python program to read last n lines of a file
Python
9 votes205 views2 answers
Django model form import
Python Django
46 votes217 views4 answers
How to stop container in docker
Python