/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 vote119 views1 answer
Return an RDD containing all pairs of elements with matching keys in self and other.
Python
108 votes113 views6 answers
Pandas sort dataframe by column
Python
0 vote121 views1 answer
All letters an numbers py array
Python
2 votes130 views1 answer
How to align text in tkinter
Python
1 vote123 views1 answer
Log loss python
Python
18 votes113 views6 answers
Get last element of array python
Python
39 votes125 views10 answers
Python for loop
Python
0 vote125 views1 answer
Pandas eliminar filas de un dataframe con una condicion
Python
2 votes101 views2 answers
How split string in python by size
Python
22 votes112 views4 answers
Join two numpy arrays
Python