파이썬으로 외부 DB 접근하기

.env 파일 작성 & 로드

외부 DB 접속 정보는 민감한 정보이므로 먼저 .env 파일을 작성하여 그 안에 접속 정보를 담자.

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=password
DB_NAME=tbl_name
DB_PORT=portnumber

그리고 아래와 같이 .env에서 접속 정보를 로드해준다.

import os
from dotenv import load_dotenv

load_dotenv()   # 작업 중인 디렉토리에 .env가 있는 경우
#load_dotenv(dotenv_path='/path/to/my/env/.env').  # 별도의 .env 디렉토리를 명시할 때

DB 연결 라이브러리

파이썬에서는 다양한 DB 라이브러리를 제공한다. 여기에서는 MySQL이나 MariaDB를 지원하는 pymysql 라이브러리를 활용하여 DB에 접근해보자.
이외에도 아래와 같은 라이브러리들이 있다.

• SQLAlchemy → 여러 DBMS 지원
• psycopg2 → PostgreSQL
• cx_Oracle → oracle

혹시 pymysql이 설치되어 있지 않다면 pip install PyMySQL로 설치할 수 있다.
그리고 아래의 코드를 실행하면 외부의 DB에 연결하여 쿼리를 수행할 수 있다.

import pymysql

connection = pymysql.connect(
    host=os.getenv("DB_HOST"),       # ex) '127.0.0.1' or your db server IP
    user=os.getenv("DB_USER"),       # ex) 'root'
    password=os.getenv("DB_PASSWORD"),   # ex) 'password'
    database=os.getenv("DB_NAME"),   
    port=int(os.getenv("DB_PORT"))    # 정수형으로 바꾸어야 함
)

try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM tbl1;"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    connection.close()

쿼리 결과 DataFrame으로 변환하기

cursor.fetchall()로 가져온 데이터는 보통 리스트의 튜플 형태로 반환된다. 따라서 아래 코드를 이용하면 보다 깔끔하게 DataFrame 형식으로 변환해서 볼 수 있다.

import pandas as pd

# description에서 컬럼 이름 추출
columns = [desc[0] for desc in cursor.description]

df = pd.DataFrame(result, columns=columns)

다른 라이브러리 코드 예시

PostgreSQL

import psycopg2

connection = psycopg2.connect(
    host='DB_HOST',       # ex) '127.0.0.1'
    user='DB_USER',       # ex) 'postgres'
    password='DB_PASS',   # ex) 'password'
    dbname='DB_NAME'      # ex) 'testdb'
)

try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM subDB2.tbl3;"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    connection.close()

SQLAlchemy

from sqlalchemy import create_engine

# MySQL 예시
db_url = "mysql+pymysql://DB_USER:DB_PASS@DB_HOST/DB_NAME"

# PostgreSQL 예시
# db_url = "postgresql+psycopg2://DB_USER:DB_PASS@DB_HOST/DB_NAME"

engine = create_engine(db_url)
with engine.connect() as connection:
    result = connection.execute("SELECT * FROM tbl3;")
    for row in result:
        print(row)