Python/Python+Database

파이썬 데이터베이스 프로그래밍 완전 입문: PostgreSQL과 psycopg2 연동하기

코딩 코디네이터 2025. 4. 12. 13:20

파이썬 데이터베이스 프로그래밍 완전 입문
: PostgreSQL과 psycopg2 연동하기

PostgreSQL? psycopg2? 이름도 낯설고 복잡하게만 느껴지셨나요? 파이썬으로 쉽고 강력하게 PostgreSQL을 다루는 실전 예제와 함께 배워봐요!

안녕하세요~ 😊

오늘은 PostgreSQL이라는 강력한 오픈소스 데이터베이스와, 이를 파이썬에서 쉽게 다룰 수 있게 해주는 psycopg2 모듈에 대해 이야기해보려 해요.

사실 대부분의 튜토리얼이 너무 간단하거나 너무 어렵게만 다뤄서, 막상 실무에 적용하려면 막히는 부분이 한두 군데가 아니잖아요?

그래서 이 글에서는 단순히 명령어만 나열하는 게 아니라,

꼭 필요한 개념 + 실전 예제 중심으로 PostgreSQL 연동을 완전히 익힐 수 있도록 정리해봤어요.

특히 AWS EC2, Docker 환경에서도 확장 가능한 구조를 염두에 두고 설명할 테니, 배워두시면 정말 유용할 거예요!

그럼 지금부터 하나씩 천천히, PostgreSQL과의 연결을 시작해볼까요? 🚀

1. PostgreSQL이란? 왜 사용해야 할까? 🧐 2. psycopg2란? 설치와 기본 사용법 ⚙️ 3. 파이썬에서 PostgreSQL 연결하기: 전체 흐름 정리 🔄 4. CRUD 예제로 배우는 실전 SQL 처리 ✍️ 5. 커넥션 풀과 트랜잭션 처리로 성능 향상하기 🚀 6. 실습 환경 구성 팁: Docker, EC2, VSCode 연동까지 💻

1. PostgreSQL이란? 왜 사용해야 할까? 🧐

PostgreSQL은 전 세계적으로 많은 개발자와 기업들이 사용하는 오픈소스 객체-관계형 데이터베이스 시스템입니다.

흔히 Postgres라고도 불리는 이 데이터베이스는 높은 안정성, 확장성, ACID 준수로 정평이 나 있죠.

PostgreSQL은 상용 DB 못지않은 기능을 자랑하면서도 무료라는 점에서, 스타트업부터 대기업까지 폭넓게 사용되고 있어요.

특히 Python과의 궁합이 좋아서, Flask, FastAPI, Django 같은 백엔드 프레임워크와도 매우 유연하게 연동할 수 있습니다.

PostgreSQL의 주요 특징 💡

ACID 보장: 트랜잭션의 무결성을 철저하게 보장해요.
JSON 지원: 관계형 + NoSQL 스타일을 동시에 쓸 수 있어요.
확장성: 사용자 정의 타입, 함수, 인덱스 등 커스터마이징 가능!
다양한 플랫폼 지원: Windows, Linux, macOS 모두 OK

다른 RDBMS와의 차이점은?

항목	PostgreSQL	MySQL
JSON 지원	강력하고 완전한 지원	제한적 기능
표준 SQL 준수	높음	중간
트리거나 프로시저	복잡한 로직 가능	기본 수준

언제 PostgreSQL을 선택해야 할까?

✅ 아래와 같은 상황이라면 PostgreSQL이 최적의 선택일 수 있어요.

대량의 데이터를 빠르게 처리하면서도 정확성을 유지하고 싶을 때
데이터 무결성과 트랜잭션이 중요한 금융/의료 분야
NoSQL 스타일의 JSON 데이터를 함께 쓰고 싶을 때
무료이면서도 신뢰할 수 있는 고성능 DB가 필요할 때

PostgreSQL을 한 번 익혀두면 다양한 환경에서 활용할 수 있어서, 장기적으로 볼 때 효율적인 선택이 될 수 있어요.

특히 Python 개발자라면, 앞으로 다룰 psycopg2 라이브러리를 통해 쉽게 연동하고, 데이터를 자유자재로 다룰 수 있습니다.

2. psycopg2란? 설치와 기본 사용법 ⚙️

PostgreSQL을 파이썬에서 사용하려면 반드시 필요한 게 바로 psycopg2라는 모듈입니다.

처음 보면 이름부터 생소하고 어려워 보이지만, 막상 사용해보면 굉장히 직관적이고 간단해요.

그야말로 PostgreSQL 연동의 표준 라이브러리라고 할 수 있죠!

psycopg2의 특징은? 🔍

PostgreSQL 공식 권장 Python 드라이버
DBAPI 2.0 표준 준수로 다른 DB 라이브러리와 유사한 사용법
커넥션 풀, 트랜잭션 처리, 복잡한 쿼리 실행 등 다양한 기능 지원

설치 방법부터 알아볼까요? 💾

파이썬 가상환경을 사용하는 걸 추천드리며,

설치는 아래 명령어 하나로 끝납니다:

pip install psycopg2-binary

💡 psycopg2와 psycopg2-binary의 차이점은?

psycopg2: 컴파일 필요. 성능 최적화 가능.
psycopg2-binary: 사전 빌드된 바이너리 제공. 설치 간편.

기본 사용법 예제 ✨

이제 psycopg2를 사용해서 PostgreSQL과 연결하는 기본 코드 예제를 살펴보죠:

import psycopg2

# DB 연결
conn = psycopg2.connect(
    host="localhost",
    database="testdb",
    user="postgres",
    password="1234"
)

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

# 쿼리 실행
cur.execute("SELECT version();")

# 결과 가져오기
db_version = cur.fetchone()
print("DB 버전:", db_version)

# 연결 종료
cur.close()
conn.close()

이 코드는 PostgreSQL 데이터베이스에 접속한 뒤, 버전을 조회하고 종료하는 가장 기본적인 흐름입니다.

앞으로 다룰 CRUD, 트랜잭션, 커넥션 풀 등도 이 흐름을 바탕으로 확장됩니다.

처음 한 번만 이해하면, 나머진 정말 쉽고 빠르게 배울 수 있어요!

3. 파이썬에서 PostgreSQL 연결하기: 전체 흐름 정리 🔄

파이썬으로 데이터베이스를 다룰 때 가장 기본이 되는 건 바로 연결 흐름을 이해하는 거예요.

마치 도로 위 자동차처럼, 연결 → 쿼리 → 결과 처리 → 종료까지의 "데이터 드라이브 코스"를 제대로 알아두면, 그 어떤 SQL 작업도 당황하지 않게 됩니다!

기본 연결 흐름 요약 ⛓

PostgreSQL 서버 실행 중인지 확인 (포트 번호 포함)
psycopg2.connect()로 연결 객체 생성
cursor() 객체로 쿼리 수행 준비
SQL 실행 (execute(), fetchone() 등)
커밋/롤백 처리
커서 및 연결 종료

이제 실제 코드 흐름으로 확인해볼게요.

import psycopg2

try:
    # 1. DB 연결
    conn = psycopg2.connect(
        host="localhost",
        dbname="testdb",
        user="postgres",
        password="1234",
        port="5432"
    )
    print("데이터베이스 연결 성공!")

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

    # 3. 쿼리 실행
    cur.execute("SELECT * FROM users;")
    
    # 4. 결과 조회
    rows = cur.fetchall()
    for row in rows:
        print(row)

    # 5. 커밋 (INSERT/UPDATE/DELETE 시 필수)
    conn.commit()

except Exception as e:
    print("오류 발생:", e)
    conn.rollback()

finally:
    # 6. 연결 종료
    cur.close()
    conn.close()

흐름이 정리된 다이어그램 보기 🧭

다음은 우리가 지금까지 봤던 흐름을 단계별로 시각화한 다이어그램이에요:

[1] psycopg2.connect()
[2] conn.cursor()
[3] cursor.execute(sql)
[4] cursor.fetchone() / fetchall()
[5] conn.commit() 또는 rollback()
[6] cursor.close(), conn.close()

연결할 때 꼭 확인해야 할 팁 💡

PostgreSQL 서버가 5432 포트에서 열려 있는지 확인
pg_hba.conf 설정이 외부 접속을 허용하는지 체크
연결 실패 시, socket 관련 오류 메시지 참고

이제 PostgreSQL과의 연결 흐름이 머릿속에 딱! 그려지시죠?

다음 단계에서는 이 흐름을 기반으로 실제 CRUD 작업을 예제로 하나하나 해보겠습니다.

4. CRUD 예제로 배우는 SQL 처리 ✍️

PostgreSQL에 연결하는 법을 배웠다면,

이제는 본격적으로 데이터를 생성(Create), 조회(Read), 수정(Update), 삭제(Delete)하는 예제를 통해 활용해볼 차례입니다.

이걸 바로 CRUD 작업이라고 해요.

테이블 준비하기 🍽

먼저 실습을 위해 users라는 간단한 테이블을 만들어볼게요.

아래 SQL을 실행해주세요.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100),
  age INTEGER
);

1️⃣ Create - 데이터 삽입

import psycopg2

conn = psycopg2.connect(...)
cur = conn.cursor()

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s);"
data = ("홍길동", "hong@example.com", 28)
cur.execute(sql, data)

conn.commit()
cur.close()
conn.close()

%s는 SQL 인젝션을 방지할 수 있는 안전한 방식이에요.

항상 이렇게 파라미터 바인딩 방식으로 작성해주세요.

2️⃣ Read - 데이터 조회

cur = conn.cursor()
cur.execute("SELECT * FROM users;")
rows = cur.fetchall()

for row in rows:
    print(row)

조회 시에는 fetchall()로 여러 개를,

fetchone()으로 하나만 가져올 수 있어요.

3️⃣ Update - 데이터 수정

sql = "UPDATE users SET age = %s WHERE name = %s;"
data = (30, "홍길동")
cur.execute(sql, data)
conn.commit()

수정 후에는 반드시 commit()을 호출해야 실제 DB에 반영됩니다.

4️⃣ Delete - 데이터 삭제

sql = "DELETE FROM users WHERE name = %s;"
data = ("홍길동",)
cur.execute(sql, data)
conn.commit()

삭제할 때도 WHERE 조건은 꼭 넣어주세요.

전체 삭제되는 실수… 생각보다 자주 나와요 😱

CRUD 흐름을 마스터하면?

이제 여러분은 DB와 대화하는 방법을 완전히 익힌 거예요.

다음 단계에서는 성능 향상에 필수인 커넥션 풀과 트랜잭션 처리를 알아볼게요.

한 단계 더 고급으로 들어가 봅시다! 💪

5. 커넥션 풀과 트랜잭션 처리로 성능 향상하기 🚀

이제 단순한 CRUD 작업을 넘어서, 성능 향상과 안정성을 위한 다음 단계로 나아가볼 시간이에요.

바로 "커넥션 풀(Connection Pool)"과 "트랜잭션(Transaction)"입니다.

커넥션 풀은 뭐고 왜 필요할까? 🧩

커넥션 풀은 말 그대로 DB 연결을 미리 여러 개 확보해두고 재사용하는 기술이에요.

매번 새롭게 connect()를 호출하면 속도 저하나 연결 누수가 발생할 수 있거든요.

psycopg2에서의 커넥션 풀 사용 예시

from psycopg2 import pool

# 최소 1개, 최대 5개 커넥션 풀 생성
pg_pool = pool.SimpleConnectionPool(
    1, 5,
    user="postgres",
    password="1234",
    host="localhost",
    port="5432",
    database="testdb"
)

# 커넥션 가져오기
conn = pg_pool.getconn()
cur = conn.cursor()
cur.execute("SELECT NOW();")
print(cur.fetchone())

# 사용 후 반환
cur.close()
pg_pool.putconn(conn)

이렇게 하면 멀티스레드 환경이나 서버 애플리케이션에서 병목 현상을 줄일 수 있어요.

트랜잭션 처리 이해하기 💼

트랜잭션(Transaction)은 여러 SQL 작업을 하나의 묶음으로 처리하는 걸 의미해요.

즉, 모두 성공하거나 하나라도 실패하면 전체를 되돌리는(rollback) 방식이죠.

트랜잭션 예제 (여러 SQL 동시 처리)

try:
    conn = psycopg2.connect(...)
    cur = conn.cursor()

    cur.execute("INSERT INTO users (name, email, age) VALUES (%s, %s, %s);", ("user1", "u1@mail.com", 25))
    cur.execute("INSERT INTO users (name, email, age) VALUES (%s, %s, %s);", ("user2", "u2@mail.com", 30))

    conn.commit()
except Exception as e:
    print("에러 발생:", e)
    conn.rollback()
finally:
    cur.close()
    conn.close()

commit() 전에 오류가 발생하면 rollback()으로 모든 변경사항을 취소할 수 있어요. 이게 바로 ACID의 Atomicity(원자성)을 구현하는 핵심입니다.

정리 및 주의할 점 ⚠️

커넥션 풀은 무조건 getconn → putconn 흐름을 지켜야 함
트랜잭션은 묶음으로 처리되는 작업일수록 유리함 (ex. 주문+결제)
예외 처리는 반드시 try-except-finally 구조로 할 것!

이제 여러분은 데이터베이스 운영의 실전 핵심까지 마스터하셨습니다.

다음 파트에서는 이 모든 내용을 실제 프로젝트처럼 Docker, EC2, VSCode 환경에서 테스트하고 관리하는 방법을 소개할게요!

6. 실습 환경 구성 팁: Docker, EC2, VSCode 연동까지 💻

마지막으로,

우리가 지금까지 배운 PostgreSQL + Python 연동을 더 실전처럼 경험하려면

Docker, AWS EC2, VSCode 원격 개발 환경을 함께 활용하면 정말 좋아요!

이렇게 하면 언제 어디서나 동일한 환경에서 테스트하고 서버로 바로 배포까지 연계할 수 있거든요.

Docker로 PostgreSQL 띄우기 🐳

docker run --name pg_test -e POSTGRES_PASSWORD=1234 -e POSTGRES_DB=testdb \
-p 5432:5432 -d postgres

위 명령으로 PostgreSQL 컨테이너를 띄우면 로컬에서 바로 연결 테스트가 가능해요.

localhost:5432로 접속하면 우리가 기존에 작성한 코드 그대로 사용할 수 있습니다.

AWS EC2에서 PostgreSQL 설치 및 접속 🖥️

Amazon Linux 2023 기준: sudo yum install postgresql15-server
sudo systemctl enable --now postgresql 로 실행
EC2 보안그룹에서 5432 포트 열기
pg_hba.conf, postgresql.conf 수정으로 외부 접속 허용

EC2에서는 IP 접근 제어와 방화벽 설정이 중요해요.

꼭 확인하고 테스트해주세요!

VSCode에서 원격 PostgreSQL 개발하기

Remote-SSH 확장 설치
EC2와 SSH 연결 후 서버에 있는 DB 코드 작성
DB 접속은 psycopg2 + Docker 또는 직접 실행 PostgreSQL 사용

이렇게 하면 완전히 클라우드 개발 환경을 갖추고 언제든 실습 → 배포까지 바로 이어갈 수 있답니다.

📌 마무리하며

지금까지 PostgreSQL과 파이썬 psycopg2를 이용한 데이터베이스 연동의 모든 흐름을 살펴봤습니다.

기본 개념부터 실전 예제, 성능 개선, 그리고 환경 구성까지 완전히 익히셨다면 이제 어디서든 자신 있게 활용하실 수 있어요.

처음엔 생소했지만, 하나씩 직접 따라 하면서 익히면 어느새 손에 익게 됩니다.

앞으로도 다양한 DB 연동, 실전 예제, 프로젝트 활용 팁을 계속 다룰 예정이니 기대해주세요!

그럼 우리 다음 글에서 또 만나요 😊

티스토리