Chapter 18. PostgreSQL 연결

18.1 PostgreSQL이란?

PostgreSQL은 오픈소스로 제공되는 관계형 데이터베이스 관리 시스템(RDBMS)입니다. 무료로 사용할 수 있으면서도 안정성과 기능이 뛰어나 기업, 공공기관, 웹 서비스, 데이터 분석 시스템 등에서 널리 사용됩니다.

파이썬에서는 psycopg2 라이브러리를 이용하여 PostgreSQL과 연동할 수 있습니다.

PostgreSQL의 특징

무료로 사용할 수 있는 오픈소스 데이터베이스입니다.
대용량 데이터 처리에 적합합니다.
트랜잭션 처리와 데이터 안정성이 뛰어납니다.
다양한 운영체제에서 사용할 수 있습니다.
SQL 표준을 잘 지원합니다.
JSON 데이터를 저장하고 조회할 수 있습니다.
Python, Java, PHP, Node.js 등 다양한 언어와 연동할 수 있습니다.

PostgreSQL 활용 분야

웹 서비스
기업 업무 시스템
공공기관 시스템
데이터 분석 시스템
위치 및 지도 정보 시스템
쇼핑몰 및 회원관리 시스템
인공지능 및 머신러닝 데이터 저장

PostgreSQL과 MySQL 비교

PostgreSQL	MySQL
오픈소스 DB	오픈소스 DB
복잡한 데이터 처리에 강함	웹 서비스에서 많이 사용
SQL 표준을 잘 지원	설치와 사용이 간편
데이터 분석에 적합	중·소규모 웹 서비스에 적합
JSON 및 확장 기능이 강력	빠른 조회와 간단한 관리에 적합

18.2 PostgreSQL 설치

PostgreSQL은 공식 홈페이지에서 다운로드할 수 있습니다.

PostgreSQL 다운로드 →

설치 순서는 다음과 같습니다.

PostgreSQL Server 설치
pgAdmin 설치
postgres 관리자 비밀번호 설정
서버 실행
데이터베이스 생성

PostgreSQL 접속 정보

Host: localhost
Port: 5432
User: postgres
Password: 1234
Database: postgres

PostgreSQL의 기본 관리자 계정은 postgres이며, 기본 포트 번호는 5432입니다.

명령 프롬프트에서 PostgreSQL에 접속하는 방법은 다음과 같습니다.

PostgreSQL 접속

psql -U postgres -d postgres

비밀번호를 입력합니다.

비밀번호 입력

Password for user postgres:

다음 명령어로 데이터베이스 목록을 조회할 수 있습니다.

데이터베이스 목록 조회

postgres=# \l

데이터베이스를 생성하는 방법은 다음과 같습니다.

데이터베이스 생성

postgres=# CREATE DATABASE pyschool;

생성된 데이터베이스에 접속하는 방법은 다음과 같습니다.

데이터베이스 접속

postgres=# \c pyschool

데이터베이스를 제거하는 방법은 다음과 같습니다.

데이터베이스 제거

postgres=# DROP DATABASE pyschool;

18.3 회원 테이블 생성

PostgreSQL에서는 자동 증가 번호를 만들 때 SERIAL을 사용할 수 있습니다.

회원 테이블 생성

CREATE TABLE member(
    member_id SERIAL PRIMARY KEY,  
    name VARCHAR(50),
    age INTEGER
);

테이블 구조를 확인하는 방법은 다음과 같습니다.

테이블 구조 확인

\d member

SERIAL은 데이터를 추가할 때 번호를 자동으로 증가시킵니다. MySQL의 AUTO_INCREMENT와 비슷한 기능입니다.

생성된 테이블을 조회하는 방법은 다음과 같습니다.

테이블 조회

SELECT * FROM member;

자동 증가 번호의 다른 작성 방법으로 최근 PostgreSQL에서는 IDENTITY 방식도 사용할 수 있습니다.

IDENTITY 방식으로 테이블 생성

CREATE TABLE member(
    member_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50),
    age INTEGER
);

초보자는 비교적 사용하기 쉬운 SERIAL을 사용해도 됩니다.

18.4 psycopg2 (사이코 피지2)

psycopg2는 파이썬에서 PostgreSQL을 사용할 수 있도록 해주는 라이브러리입니다. SQLite는 파이썬에 기본으로 포함되어 있지만 PostgreSQL은 별도의 라이브러리를 설치해야 합니다.

설치

psycopg2 설치

pip install psycopg2-binary

설치 확인

pip show psycopg2-binary

라이브러리 가져오기

import psycopg2

PostgreSQL 연결

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

print("PostgreSQL 연결 성공")

연결이 성공하면 다음과 같은 메시지가 출력됩니다.

실행 결과

PostgreSQL 연결 성공

연결 정보 설명

host: PostgreSQL 서버 주소
port: PostgreSQL 포트 번호
user: 접속 사용자
password: 사용자 비밀번호
database: 사용할 데이터베이스

Cursor 생성

SQLite와 MySQL처럼 Cursor를 생성합니다.

Cursor 생성

cursor = conn.cursor()

Cursor는 SQL 명령을 PostgreSQL에 전달하고 조회 결과를 받아오는 역할을 합니다.

18.5 CRUD

CRUD는 데이터베이스에서 가장 많이 사용하는 네 가지 작업을 의미합니다.

Create: INSERT
Read: SELECT
Update: UPDATE
Delete: DELETE

CREATE 데이터 추가

실습 1) postgresqlinsert.py

3개의 레코드를 추가합니다.

postgresqlinsert.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()
cursor.execute(
    "INSERT INTO member(name, age) VALUES(%s, %s)",
    ("홍길동", 20)
)

cursor.execute(
    "INSERT INTO member(name, age) VALUES(%s, %s)",
    ("이순신", 30)
)

cursor.execute(
    "INSERT INTO member(name, age) VALUES(%s, %s)",
    ("강감찬", 40)
)

conn.commit()

print("회원 데이터가 저장되었습니다.")

cursor.close()
conn.close()

commit()을 실행해야 INSERT, UPDATE, DELETE 결과가 실제 데이터베이스에 저장됩니다.

READ 데이터 조회

실습 2) postgresqlselect.py

postgresqlselect.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

cursor.execute("SELECT * FROM member ORDER BY member_id")

rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

실행 결과

(1, '홍길동', 20)
(2, '이순신', 30)
(3, '강감찬', 40)

조회된 한 행은 튜플 형태로 반환됩니다. (member_id, name, age)

UPDATE 데이터 수정

실습 3) postgresqlupdate.py

postgresqlupdate.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

cursor.execute(
    "UPDATE member SET age=%s WHERE member_id=%s",
    (21, 1)
)

conn.commit()

print("회원 정보가 수정되었습니다.")

cursor.close()
conn.close()

DELETE 데이터 삭제

실습 4) postgresqldelete.py

postgresqldelete.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

cursor.execute(
    "DELETE FROM member WHERE member_id=%s",
    (1,)
)

conn.commit()

print("회원 정보가 삭제되었습니다.")

cursor.close()
conn.close()

값이 한 개만 들어 있는 튜플은 반드시 쉼표를 작성해야 합니다. (1,)

다음과 같이 작성하면 튜플이 아니라 일반 정수로 처리됩니다. (1)

18.6 fetchone()

fetchone()은 조회된 결과에서 한 건만 가져옵니다.

실습 5) postgresqlselectcount.py

postgresqlselectcount.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

cursor.execute("SELECT COUNT(*) FROM member")

count = cursor.fetchone()

print(count)

cursor.close()
conn.close()

실행 결과

(3,)

fetchone()의 결과는 튜플입니다. 숫자만 출력하려면 인덱스 번호 0을 사용합니다.

숫자만 출력하기

count = cursor.fetchone()[0]

18.7 fetchall()

fetchall()은 조회된 모든 데이터를 가져옵니다.

실습 6) postgresqlselectwhere.py

postgresqlselectwhere.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

cursor.execute(
    "SELECT * FROM member WHERE age > %s",
    (20,)
)

rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

실행 결과

(2, '이순신', 30)
(3, '강감찬', 40)

18.8 사용자 입력으로 데이터 추가

실습 7) postgresqlinput.py

postgresqlinput.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

name = input("이름: ")
age = int(input("나이: "))

sql = """
INSERT INTO member(name, age)
VALUES(%s, %s)
"""

cursor.execute(sql, (name, age))

conn.commit()

print("회원이 등록되었습니다.")

cursor.close()
conn.close()

18.9 이름으로 회원 검색

실습 8) postgresqlselectname.py

postgresqlselectname.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

name = input("검색할 이름: ")

cursor.execute(
    "SELECT * FROM member WHERE name=%s",
    (name,)
)

row = cursor.fetchone()

if row is None:
    print("검색된 회원이 없습니다.")
else:
    print(row)

cursor.close()
conn.close()

조회된 데이터가 없으면 fetchone()은 None을 반환합니다.

18.10 LIKE 검색

LIKE는 문자열의 일부가 포함된 데이터를 검색할 때 사용합니다.

실습 9) postgresqlselectlike.py

postgresqlselectlike.py

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

cursor = conn.cursor()

cursor.execute(
    "SELECT * FROM member WHERE name LIKE %s",
    ("%이%",)
)

rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

%는 글자가 없거나 여러 개 있을 수 있다는 의미입니다.

이%: 이로 시작하는 이름을 검색합니다.
%이: 이로 끝나는 이름을 검색합니다.
%이%: 이가 포함된 이름을 검색합니다.

사용자가 검색어를 입력하도록 만들 수도 있습니다.

사용자 입력 검색

keyword = input("검색어: ")
cursor.execute(
    "SELECT * FROM member WHERE name LIKE %s",
    ("%" + keyword + "%",)
)

18.11 SQL Injection 방지

사용자의 입력값을 SQL 문장에 직접 연결하면 보안 문제가 발생할 수 있습니다.

잘못된 예

name = input("이름: ")
sql = "SELECT * FROM member WHERE name='" + name + "'"
cursor.execute(sql)

사용자가 입력한 값이 SQL 문장에 직접 연결되기 때문에 SQL Injection 공격에 노출될 수 있습니다.

좋은 예

name = input("이름: ")
sql = "SELECT * FROM member WHERE name=%s"
cursor.execute(sql, (name,))

%s 자리에 값을 직접 넣지 않고 두 번째 매개변수로 전달해야 합니다. 이 방식을 매개변수 바인딩이라고 합니다.

매개변수 바인딩을 사용하면 특수문자가 포함된 입력값도 안전하게 처리할 수 있습니다.

주의할 점은 %s에 따옴표를 직접 작성하지 않는 것입니다.

올바른 예

sql = "SELECT * FROM member WHERE name=%s"

18.12 try-except를 이용한 오류 처리

데이터베이스 연결이나 SQL 실행 중에는 오류가 발생할 수 있습니다.

try-except-finally를 사용하면 오류를 안전하게 처리할 수 있습니다.

실습 10) postgresqltry.py

postgresqltry.py

import psycopg2

conn = None
cursor = None

try:
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        user="postgres",
        password="1234",
        database="pyschool"
    )

    cursor = conn.cursor()

    cursor.execute(
        "INSERT INTO member(name, age) VALUES(%s, %s)",
        ("유관순", 18)
    )

    conn.commit()

    print("회원이 등록되었습니다.")

except psycopg2.Error as error:
    print("데이터베이스 오류:", error)

    if conn is not None:
        conn.rollback()

finally:
    if cursor is not None:
        cursor.close()

    if conn is not None:
        conn.close()

rollback()을 사용하여 작업 전 상태로 되돌릴 수 있습니다.

rollback 예시

conn.rollback()

finally는 오류 발생 여부와 관계없이 항상 실행됩니다. 따라서 Cursor와 데이터베이스 연결을 종료할 때 사용할 수 있습니다.

18.13 PostgreSQL 전체 회원관리 프로그램

실습 11) postgresqlmember.py (함수형태)

postgresqlmember.py

import psycopg2

conn = None
cursor = None

def connect_db():
    return psycopg2.connect(host='localhost', 
       database='pyschool2', user='postgres', 
       password='postgres', port=5432)

def insert_member():
    try:
       conn = connect_db()
        cursor = conn.cursor()

        print("****회원 정보 입력****")
        name = input("이름: ")
        age = int(input("나이: "))
        cursor.execute("INSERT INTO member (name, age) VALUES (%s, %s)", 
             (name, age))
        print("회원 정보 입력 완료")
    except Exception as e:
        print(e)
        conn.rollback()
    finally:
        conn.commit()
        cursor.close()
        conn.close()

def select_member():
    try:
        conn = connect_db()
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM member")
        rows = cursor.fetchall()
        for row in rows:
            print(row[0], row[1], row[2])
        print("회원 정보 조회 완료")
    except Exception as e:
        print(e)    
        print("회원 정보 조회 실패")
    finally:
        conn.commit()
        cursor.close()
        conn.close()

def update_member():
    try:
        conn = connect_db()
        cursor = conn.cursor()

        print("****회원 정보 수정****")
        member_id = input("아이디를 입력하세요")
        name = input("수정할 이름을 입력하세요 (엔터키 누르면 수정하지 않습니다.)")
        strage = input("수정할 나이를 입력하세요 (엔터키 누르면 수정하지 않습니다.)")

        if strage != "" :
            age = int(strage)
            cursor.execute("""UPDATE member SET age = %s 
                        WHERE member_id = %s""", 
                        (age, member_id))

        if name != "" :
            cursor.execute("""UPDATE member SET name = %s 
                        WHERE member_id = %s""", 
                        (name, member_id))

        if name != "" and strage != "":
            cursor.execute("""UPDATE member SET name = %s, age = %s 
                        WHERE member_id = %s""", 
                        (name, age, member_id))
    except Exception as e:
            print(e)
            conn.rollback()
            print("회원 정보 수정 실패")    
    finally:
            conn.commit()
            cursor.close()
            conn.close()

def delete_member():
    try:
        conn = connect_db()
        cursor = conn.cursor()

        print("****회원 정보 삭제****")
        member_id = input("삭제할 아이디를 입력하세요 (엔터키 누르면 삭제하지 않습니다.)")

        if member_id != "":
            cursor.execute("DELETE FROM member WHERE member_id = %s", (member_id,))
 
    except Exception as e:
        print(e)
        conn.rollback()
        print("회원 정보 삭제 실패")
    finally:
        conn.commit()
        cursor.close()
        conn.close()

def main():
    while True:
        print("****회원 관리 프로그램****")
        print("1. 회원 정보 입력")
        print("2. 회원 정보 조회")
        print("3. 회원 정보 수정")
        print("4. 회원 정보 삭제")
        print("5. 종료")
        choice = input("메뉴를 선택하세요: ")
        if choice == "1":
            insert_member()
        elif choice == "2":
            select_member()
        elif choice == "3":
            update_member()
        elif choice == "4":
            delete_member()
        elif choice == "5":
            break
        else:
            print("메뉴를 다시 선택하세요")

if __name__ == "__main__":
    main()

실습 12) postgresqlmember.py (클래스형태)

postgresqlmember.py

import psycopg2

class MemberManager:

    def __init__(self):
        self.conn = None
        self.cursor = None

    def connect_db(self):
        return psycopg2.connect(host='localhost', 
        database='pyschool2', user='postgres', 
        password='postgres', port=5432)

    def insert_member(self):
        try:
            self.conn = self.connect_db()
            self.cursor = self.conn.cursor()

            print("****회원 정보 입력****")
            name = input("이름: ")
            age = int(input("나이: "))
            self.cursor.execute("INSERT INTO member (name, age) VALUES (%s, %s)", 
                (name, age))
            print("회원 정보 입력 완료")
        except Exception as e:
            print(e)
            self.conn.rollback()
        finally:
            self.conn.commit()
            self.cursor.close()
            self.conn.close()

    def select_member(self):
        try:
            self.conn = self.connect_db()
            self.cursor = self.conn.cursor()

            self.cursor.execute("SELECT * FROM member")
            rows = self.cursor.fetchall()
            for row in rows:
                print(row[0], row[1], row[2])
            print("회원 정보 조회 완료")
        except Exception as e:
            print(e)    
            print("회원 정보 조회 실패")
        finally:
            self.conn.commit()
            self.cursor.close()
            self.conn.close()

    def update_member(self):
        try:
            self.conn = self.connect_db()
            self.cursor = self.conn.cursor()

            print("****회원 정보 수정****")
            member_id = input("아이디를 입력하세요")
            name = input("수정할 이름을 입력하세요 (엔터키 누르면 수정하지 않습니다.)")
            strage = input("수정할 나이를 입력하세요 (엔터키 누르면 수정하지 않습니다.)")

            if strage != "" :
                age = int(strage)
                self.cursor.execute("""UPDATE member SET age = %s 
                            WHERE member_id = %s""", 
                            (age, member_id))

            if name != "" :
                self.cursor.execute("""UPDATE member SET name = %s 
                            WHERE member_id = %s""", 
                            (name, member_id))

            if name != "" and strage != "":
                self.cursor.execute("""UPDATE member SET name = %s, age = %s 
                            WHERE member_id = %s""", 
                            (name, age, member_id))
        except Exception as e:
                print(e)
                self.conn.rollback()
                print("회원 정보 수정 실패")    
        finally:
                self.conn.commit()
                self.cursor.close()
                self.conn.close()

    def delete_member(self):
        try:
            self.conn = self.connect_db()
            self.cursor = self.conn.cursor()

            print("****회원 정보 삭제****")
            member_id = input("삭제할 아이디를 입력하세요 (엔터키 누르면 삭제하지 않습니다.)")

            if member_id != "":
                self.cursor.execute("DELETE FROM member WHERE member_id = %s", (member_id,))
    
        except Exception as e:
            print(e)
            self.conn.rollback()
            print("회원 정보 삭제 실패")
        finally:
            self.conn.commit()
            self.cursor.close()
            self.conn.close()

def main():
        manager = MemberManager()
        while True:
            print("****회원 관리 프로그램****")
            print("1. 회원 정보 입력")
            print("2. 회원 정보 조회")
            print("3. 회원 정보 수정")
            print("4. 회원 정보 삭제")
            print("5. 종료")
            choice = input("메뉴를 선택하세요: ")
            if choice == "1":
                manager.insert_member()
            elif choice == "2":
                manager.select_member()
            elif choice == "3":
                manager.update_member()
            elif choice == "4":
                manager.delete_member()
            elif choice == "5":
                break
            else:
                print("메뉴를 다시 선택하세요")

if __name__ == "__main__":
    main()

18.14 SQLite, MySQL, PostgreSQL, Oracle 비교

구분	SQLite	MySQL	PostgreSQL	Oracle
파이썬 라이브러리	sqlite3	pymysql	psycopg2	oracledb
별도 설치	필요 없음	필요함	필요함	필요함
기본 포트	없음	3306	5432	1521
기본 관리자	없음	root	postgres	SYSTEM 또는 SYS
데이터베이스 형태	파일	서버	서버	서버
자동 증가	AUTOINCREMENT	AUTO_INCREMENT	SERIAL 또는 IDENTITY	IDENTITY 또는 SEQUENCE
매개변수	?	%s	%s	:1, :2
주요 용도	개인·소규모 프로그램	웹 서비스	기업·데이터 분석	대기업·금융·공공기관

연결 방법 비교

SQLite

import sqlite3
conn = sqlite3.connect("sample.db")

MySQL

import pymysql

conn = pymysql.connect(
    host="localhost",
    user="root",
    password="1234",
    database="pyschool",
    charset="utf8"
)

PostgreSQL

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="1234",
    database="pyschool"
)

Oracle

import oracledb

conn = oracledb.connect(
    user="system",
    password="1234",
    dsn="localhost:1521/XEPDB1"
)

매개변수 사용 비교

SQLite

cursor.execute(
    "INSERT INTO member(name, age) VALUES(?, ?)",
    ("홍길동", 20)
)

MySQL

cursor.execute(
    "INSERT INTO member(name, age) VALUES(%s, %s)",
    ("홍길동", 20)
)

PostgreSQL

cursor.execute(
    "INSERT INTO member(name, age) VALUES(%s, %s)",
    ("홍길동", 20)
)

Oracle

cursor.execute(
    "INSERT INTO member(name, age) VALUES(:1, :2)",
    ("홍길동", 20)
)

LIMIT과 OFFSET 기준으로 정리

DBMS	페이징(21~25번째 조회)
SQLite	LIMIT 5 OFFSET 20
MySQL	LIMIT 5 OFFSET 20 또는 LIMIT 20, 5
MariaDB	LIMIT 5 OFFSET 20 또는 LIMIT 20, 5
PostgreSQL	LIMIT 5 OFFSET 20
Oracle 11g 이하	ROWNUM 또는 서브쿼리 사용
Oracle 12c 이상	OFFSET 20 ROWS FETCH FIRST 5 ROWS ONLY
SQL Server	OFFSET 20 ROWS FETCH NEXT 5 ROWS ONLY

프로그램·홈페이지·강의가 필요하신가요?