/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 vote171 views1 answer
Python String to array using list comprehension
Python
4 votes150 views2 answers
Display current local time in readable format
Python
0 vote183 views1 answer
Aes in django
Python Django
0 vote142 views1 answer
Print out a name in python
Python
16 votes145 views6 answers
Turtle
Python
3 votes203 views4 answers
Delete row by index pandas
Python
1 vote150 views1 answer
How to use python to print multiplication table
Python
0 vote174 views1 answer
Spacy get number of tokens
Python
13 votes136 views1 answer
Check strings last letter python
Python
12 votes156 views5 answers
Conda python update
Python