/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 vote376 views1 answer
Check python version colab
Python
6 votes321 views2 answers
How to add mouse button in pygame
Python
7 votes305 views1 answer
Program to find even numbers in python
Python
1 vote312 views1 answer
Array and list in python
Python
-2 votes335 views1 answer
Jwt authentication python flask
Python
9 votes345 views1 answer
Flask tutorials
Python
0 vote314 views4 answers
Dataframe to dict without index
Python
5 votes323 views2 answers
Convert response to json python
Python
0 vote352 views1 answer
Only get top 10 python dataframe
Python
2 votes367 views3 answers
Python get pixel color
Python