/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

2 votes149 views1 answer
Python optionmenu tkinter
Python
0 vote125 views1 answer
Python how to add a string to a list in the middle
Python
0 vote188 views1 answer
How to subtract 48 hours from datetime filed in python without using pandas
Python
5 votes121 views2 answers
Install pandas in python mac
Python
0 vote131 views1 answer
Python3 yyyymmddhhmmss
Python
137 votes135 views6 answers
Python file reading
Python
1 vote166 views1 answer
Python autoclick website
Python
1 vote145 views3 answers
Pascal triangle python
Python
9 votes194 views5 answers
Proxy request python
Python
0 vote161 views1 answer
Windows 10 reset django migrations
Python Django