/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

4 votes340 views1 answer
Create or update django models
Python Django
0 vote273 views1 answer
Load text file line in listbox python tkinter site:stackoverflow.com
Python
21 votes306 views4 answers
Send dm discord py
Python
1 vote297 views2 answers
How to print a text
Python
0 vote331 views1 answer
Can't import flask login
Python Flask
32 votes302 views2 answers
Drop if nan in column pandas
Python
6 votes294 views2 answers
Scatter plot plotly
Python
0 vote267 views1 answer
Serversocket.accept() python
Python
0 vote331 views1 answer
Access django server from another machine
Python Django
0 vote311 views1 answer
How to print the fibonacci sequence in python using while loop
Python