/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 votes134 views4 answers
Csv to python dictionary
Python
0 vote129 views1 answer
How to get a int from string python
Python
0 vote119 views1 answer
How to pick everything after a character in python
Python
2 votes157 views1 answer
Matplotlib plot adjust margins
Python
4 votes152 views2 answers
How to count range in django template
Python Django
2 votes110 views1 answer
Father's day 2020
Python
0 vote169 views1 answer
How do i get auth user model dynamically in django?
Python Django
1 vote129 views1 answer
Discord.py commands.group
Python
2 votes113 views1 answer
How to backspace in python
Python
27 votes124 views3 answers
Python check if string or list
Python