/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 vote304 views1 answer
How to run the server in django
Python Django
3 votes252 views3 answers
Run python from c#
Python
2 votes305 views1 answer
Auto datetime in django models
Python Django
1 vote239 views1 answer
Display max number of columns pandas
Python
0 vote244 views1 answer
Pandas dataframe get number of occurrence in column
Python
1 vote260 views1 answer
Django boilerplate command
Python Django
51 votes225 views1 answer
Add new keys to a dictionary python
Python
22 votes239 views4 answers
Empty dataframe
Python
1 vote233 views2 answers
Iterable object pytohn
Python
0 vote243 views1 answer
Remote python running line by line visual code
Python