/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

3 votes160 views3 answers
Pytest
Python
1 vote95 views1 answer
Typeerror: list indices must be integers or slices, not str
Python
0 vote129 views1 answer
Matplotlib rc params
Python
0 vote167 views1 answer
Migrations.rename_field django
Python Django
-1 vote144 views2 answers
Python path absolute
Python
0 vote155 views2 answers
How to use speech recognition in python
Python
0 vote134 views1 answer
Python program to remove newline characters from a file
Python
1 vote132 views1 answer
How to filter queryset with foreign key in django
Python
1 vote138 views1 answer
Django queryset first element
Python
0 vote153 views2 answers
Remove leading and lagging spaces dataframe python
Python