[Python, MySQL] Python에서 DB 접속해 Data 다운받기 2

SQL connection method : Standard TCP/IP over SSH 일 때 python에서 DB 접속하는 방법.

아래 내용은 vscode의 jupyter 파일로 작업하였다

 

 

 

MySQL에서 standard TCP/IP over SSH로 connection 생성

MySQL에서 connection method를 Standard TCP/IP over SSH로 설정하고 파라미터 설정 후 연결

 

SSH 연결은 key 파일이 필요하므로 SSH key file을 꼭 첨부하자

 

 

 

pymysql, sshtunnel 라이브러리 설치

! pip install pymysql
! pip install sshtunnel

python에서 필요한 라이브러리 설치하기

라이브러리 호출

import pymysql
import paramiko
import pandas as pd
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder
from os.path import expanduser

DB 연결 및 데이터 불러오기

 

Vscode의 Virtual Machine 사용하는 경우

1. SQL 접속에 필요한 key 파일(key.pem)을 VM 내 원하는 폴더에 업로드

       ex) .ssh폴더

2. 업로드한 key.pem 파일 경로 복사

• key.pem 파일 경로 아래 코드에 첨부

mypkey = paramiko.RSAKey.from_private_key_file('key.pem파일 경로')

•  sql 호스트 설정값, ssh 설정값 입력

sql_hostname = 'mysql hostname'
sql_username = 'Username'
sql_password = '연결 비밀번호'
sql_main_database = '연결하고자 하는 스키마명' 
sql_port = 'MYSQL server Port'
ssh_host = 'SSH Hostname에서 : 앞쪽'
ssh_user = 'SSH Username'
ssh_port = 'SSH Hostname에서 : 뒤쪽'

주의사항: int 타입일 경우 '' 빼고 넣기

 

• 데이터 불러오기

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 = 'sql문 작성' 
    df = pd.read_sql_query(query, conn)
    conn.close()

 

 

참고

https://www.reddit.com/r/learnpython/comments/53wph1/connecting_to_a_mysql_database_in_a_python_script/