Python

Search

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()

Comment

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()

Comment

PREVIOUS	NEXT

Code Example
Python :: how to store something in python
Python :: scale values in 0 100 python
Python :: def create(self validated_data)
Python :: convert string ranges list python
Python :: merge two dict python
Python :: change a coolumn datatype in pandas
Python :: 2 functions at a time py
Python :: python emoji convert
Python :: exception logging
Python :: get all subarrays of an array python
Python :: pandas flip x and y axis
Python :: how to define the range of values in seaborn heatmap
Python :: python switch item
Python :: string in netcdf file python
Python :: check if binary tree is balanced python
Python :: python add new key to dictionary
Python :: pandas convert hex string to int
Python :: selenium options python path
Python :: iterrows pd
Python :: theme_use() tkinter theme usage
Python :: python maximum product subarray
Python :: decode a qrcode inpython
Python :: python csv find specific string
Python :: print in pytest python
Python :: que es una funcion en python
Python :: python not equal to
Python :: qdate to date
Python :: plotly facet_grid python
Python :: tkinter label border color
Python :: python default value

Search

PYTHON

python db access though ssh user

python db access though ssh user

ADD CONTENT