[DataBase] PostgreSQL 파헤치기

PostgreSQL이란?

PostgreSQL은 관계형 데이터베이스 관리 시스템(RDBMS) 중 하나로, 
JSON 및 XML 데이터 유형을 지원하는 오픈소스이다.

PostgreSQL은 클라이언트/서버 모델을 사용한다.
서버에서는 데이터베이스 내의 파일을 관리하며, 클라이언트에서 들어오는 연결들을 수용하고 데이터베이스 액션을 수행한다.
또한 클라이언트는, 기존 서버와의 간섭 없이 새로 생성된 서버 프로세스와 통신한다.

PostgreSQL 사용방법은 크게 두 가지로 나뉜다.
GUI(그래픽 사용자 인터페이스) 도구와 명령줄 인터페이스를 통한 커맨드 라인 사용이다.
각각의 방법에 대해 간단히 알아보자.

사용방법

1) GUI(Graphic User Interface, 그래픽 사용자 인터페이스) 도구
- pgAdmin
  PostgreSQL의 가장 널리 사용되는 GUI 도구 중 하나. DB 관리, Query 실행, 테이블 생성 및 수정 등의 작업을 그래픽 사용자 인터페이스를 통해 수행 가능하다. 참고로 필자가 현재 인턴으로 근무하는 회사에서도 pgAdmin GUI를 사용한다.
- DBeaver
  데이터베이스 관리 도구로서, PostgreSQL을 비롯한 다양한 데이터베이스와 호환되는 강력한 기능을 제공한다.
- Navicat
  다중 데이터베이스 관리 및 개발 도구로 PostgreSQL을 지원하며, 사용자 친화적인 인터페이스를 제공한다.


2) CLI(Command Line Interface, 명령줄 인터페이스)
- psql
  PostgreSQL의 기본 CLI 도구이다. 명령줄에서 데이터베이스에 접속하고, SQL 쿼리를 실행하며, 데이터베이스 객체를 관리할 수 있다.

psql -U username-d dbname -h hostname

위와 같은 명령어로 데이터베이스에 접속할 수 있다.
'username'은 PostgreSQL 사용자의 이름, 'dbname'은 데이터베이스 이름, 'hostname'은 호스트의 이름을 적어주면 된다.

CLI 명령어 소개

	사용 용도	명령어
1	데이터베이스 목록 표시	/l 또는 /list
2	특정 데이터베이스에 연결	/c 또는 /connect
3	현재 데이터베이스에 있는 테이블 목록 표시	/dt 또는 /d
4	데이터베이스 사용자 목록 표시	/du
5	스키마 목록 표시	/dn
6	사용자 정의 함수 목록 표시	/df
7	인덱스 목록 표시	di
8	psql 세션을 종료하고 나감	/q
9	도움말 표시	/?

 

GUI 커리문 예시

1) INNER JOIN
- 동일한 term_id 값을 가진 두 개의 테이블을 조인하는 쿼리문 예시 (JOIN 함수 사용)

SELECT * 
FROM public.tat_geofence g
INNER JOIN public.tat_terminal t 
ON g.term_id = t.term_id;

2) COUNT
- axis_seq의 값이 1인 데이터 개수 파악 (집계함수 COUNT 사용)

SELECT COUNT (*)
FROM public.tat_geofence
where axis_seq = '1';

Python으로 PostgreSQL 사용하기

필자는 PostgreSQL에서 데이터를 불러와 파이썬으로 작업을 하고자 하였다.
이를 위해서는 psycopg2 라이브러리를 사용할 수 있는데,
psycopg2는 PostgreSQL 데이터베이스에 연결하고 상호 작용하기 위한 파이썬 언어의 PostgreSQL 어댑터이다.

0) psycopg2-binary 설치

$ pip install psycopg2-binary

1) Python과 PostgreSQL 연결, connect

# PostgreSQL에 연결
conn = psycopg2.connect(
    dbname="your_dbname", user="your_username", password="your_password", host="your_host", port="your_port"
)

    → 연결에 필요한 데이터베이스 이름, 사용자 이름, 비밀번호, 호스트 및 포트를 입력하여 연결할 수 있다.

[연결 정보를 잊었다면?]
- 왼쪽 server 탭 클릭 → 연결된 서버 그룹 찾기
- 우클릭 → 속성(properties....)
- "Connection" → host, port, dbname, user 확인 가능

2) 커서 생성, conn.cursor()

연결된 데이터베이스에서 쿼리를 실행하기 위해 커서를 생성해야 한다. 커서는 쿼리를 실행하고 결과를 검색하는 데 사용됩니다.

cur = conn.cursor()

 

3) 커서 실행, cur.execute

# 쿼리 실행
cur.execute("SELECT * FROM public.tat_geofence
				ORDER BY term_id ASC, axis_seq ASC;")
                
# 결과 가져오기
cur.fetchall()

위와 같이 execute() 메서드를 사용하여 쿼리를 실행하고,
fetchall(), fetchone(), fetchmany() 등의 메서드를 사용하여 실행 결과를 검색할 수 있다.

4) 연결 종료, conn.close()

연결을 종료하는 것은 데이터베이스와의 연결을 해제하는 과정이다.
이는 데이터베이스 리소스를 효율적으로 관리하고 메모리 누수를 방지하기 위해 중요하며,
psycopg2를 사용하여 PostgreSQL에 연결한 후에는 반드시 연결을 종료해야 한다.

연결을 닫기 전에 모든 커서가 닫혀 있어야 한다. 따라서 커서 종류 -> 연결 종료의 순으로 진행하면 된다.
마찬가지로 예시 코드는 아래와 같다.

# 커서 닫기
cur.close()

# 연결 종료
conn.close()