[PostgreSQL] Python 에서 PostgreSQL 사용하기

psycopg2 라이브러리

기본 사용법

pip install psycopg2-binary

import psycopg2

# 1. 데이터베이스 연결 정보 설정
# 실제 환경에 맞게 'host', 'database', 'user', 'password', 'port'를 변경한다.
DB_HOST = "localhost"
DB_NAME = "your_database_name"
DB_USER = "your_user"
DB_PASS = "your_password"
DB_PORT = 5432

conn = None # 연결 객체 초기화

try:
    # 1. 연결 설정
    conn = psycopg2.connect(
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASS,
        port=DB_PORT
    )

    # 2. 커서 생성
    cur = conn.cursor()

    # 테이블 생성 예시 (데이터 변경 작업)
    cur.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE
        );
    """)

    # 데이터 삽입 예시 (파라미터화된 쿼리 사용 권장 - SQL 인젝션 방지)
    user_data = ("Alice", "alice@example.com")
    cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", user_data)

    # 4. 변경 사항 확정 (Commit)
    conn.commit()
    print("데이터 삽입 성공!")

    # 데이터 조회 예시 (SELECT 쿼리)
    cur.execute("SELECT id, name, email FROM users WHERE name = %s", ("Alice",))

    # 쿼리 결과 가져오기
    # fetchone(): 하나의 결과만 가져옴
    # fetchall(): 모든 결과를 리스트의 튜플 형태로 가져옴
    # fetchmany(size): 지정된 개수만큼 가져옴
    rows = cur.fetchall()

    print("\n--- 조회 결과 ---")
    for row in rows:
        print(f"ID: {row[0]}, 이름: {row[1]}, 이메일: {row[2]}")

    # 5. 커서 닫기
    cur.close()

except (Exception, psycopg2.Error) as error:
    print(f"PostgreSQL 연결 또는 쿼리 실행 중 오류 발생: {error}")

finally:
    # 5. 연결 닫기 (오류 발생 여부와 관계없이 실행)
    if conn is not None:
        conn.close()
        print("\nPostgreSQL 연결이 닫혔습니다.")

pandas to_sql()

• pandas 의 to_sql() 메서드를 이용해 데이터프레임 단위로 적재를 할 수 있따.
• to_sql() 함수를 사용하려면 데이터베이스 연결이 필요하다.

pip install pandas sqlalchemy psycopg2-binary

import pandas as pd
from sqlalchemy import create_engine
import psycopg2 # 연결 드라이버로 사용

# 1. 데이터프레임 준비 (테이블과 동일한 구조라고 가정)
data = {
    'id': [1, 2, 3],
    'name': ['김철수', '이영희', '박민수'],
    'email': ['cs@example.com', 'yh@example.com', 'ms@example.com'],
    'age': [30, 25, 35]
}
df = pd.DataFrame(data)

# 2. PostgreSQL 연결 엔진 생성
# 형식: 'postgresql+psycopg2://<user>:<password>@<host>:<port>/<database>'
DB_USER = "your_user"
DB_PASS = "your_password"
DB_HOST = "localhost"
DB_PORT = 5432
DB_NAME = "your_database_name"
TABLE_NAME = "users" # 데이터프레임을 저장할 테이블 이름

engine_url = f"postgresql+psycopg2://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

try:
    # SQLAlchemy 엔진 생성
    engine = create_engine(engine_url)
    
    # 3. 데이터프레임을 SQL 테이블로 적재
    # name: 저장할 테이블 이름
    # con: 연결 엔진
    # if_exists: 테이블이 이미 존재할 경우 처리 방식 ('fail', 'replace', 'append')
    # index: DataFrame의 인덱스를 테이블의 컬럼으로 저장할지 여부
    df.to_sql(
        name=TABLE_NAME, 
        con=engine, 
        if_exists='append', # 'append'는 기존 테이블에 데이터 추가
        index=False,        # DataFrame의 인덱스를 저장하지 않음
        method='multi'      # 여러 행을 한 번에 삽입하여 성능 향상
    )
    
    print(f"DataFrame이 '{TABLE_NAME}' 테이블에 적재 성공.")

except Exception as e:
    print(f"데이터 적재 중 오류 발생: {e}")