/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

1 vote352 views2 answers
Can you look for specific characters in python
Python
0 vote351 views1 answer
Check if string contains alphabets python
Python
1 vote336 views1 answer
Flask socketio send
Python
2 votes331 views1 answer
Python typing effect
Python
30 votes348 views3 answers
Python read scv
Python
0 vote315 views1 answer
Plt title color
Python
0 vote324 views1 answer
Basic script
Python
0 vote355 views1 answer
Label with list comprehension python
Python
-2 votes332 views1 answer
Sort function in pandas dataframe to sort specific properties
Python
1 vote332 views1 answer
Example XlsxWriter in Python
Python