/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 vote113 views2 answers
How to print out text in python
Python
17 votes105 views4 answers
Pandas return row
Python
0 vote106 views1 answer
Slice in iloc
Python
9 votes114 views2 answers
Argc python
Python
2 votes146 views2 answers
Get query param in django
Python Django
15 votes107 views2 answers
Python check if variable is string
Python
0 vote113 views1 answer
Import one file into another python
Python
1 vote114 views1 answer
Python code to receive gmail
Python
3 votes104 views3 answers
Drop variable pandas
Python
0 vote151 views1 answer
How to subtract 48 hours from datetime filed in python without using pandas
Python