/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 vote141 views1 answer
Ms access python dataframe
Python
1 vote143 views1 answer
Python change name of the imported function
Python
3 votes208 views3 answers
Django.db.utils.OperationalError: no such table:
Python Django
0 vote179 views1 answer
Send message if user is banned discord.py
Python
2 votes139 views3 answers
Linux python installation wheel
Python
0 vote146 views1 answer
Python get next item from generator
Python
0 vote138 views1 answer
Python crosshair overlay
Python
6 votes140 views6 answers
What is the best ide for python
Python
0 vote140 views1 answer
Get path of notebook
Python
5 votes139 views2 answers
Model load pytorch
Python