Psycopg 사용하기

1.psycopg2

• Python에서 PostgreSQL을 사용하기 위해 사용하는 라이브러리
• Database 연결/해제, SQL 실행과 트랜잭션 발생, 결과 반환 등 지원

psycopg2
pip로 설치 가능하지만 whl 패키지가 아닌 소스코드 수준으로 설치
PostgreSQL이 없는 환경에서 설치하면 오류
소스코드 수준으로 사용해서 세밀한 설정이나 수정이 가능

psycopg2-binary
바이너리 형태인 whl 패키지로 배포되어 pip로 설치 가능
단순한 라이브러리를 로드하여 사용하거나 해당 함수들을 사용해 래핑하는 경우 권장
외부 라이브러리의 변경 사항에 대해 유연하게 대처 가능

1-1.psycopg2-binary 설치하기

pip install psycoph2-binary

import psycopg2

1-2.PostgreSQL과 연결하기

• 연결할 데이터베이스의 주소, 포트, 계정, 비밀번호 전달

# 한개 인자로 전달
conn = psycopg2.connect("host=DB주소 dbname=DB이름 user=사용자계정 password=비밀번호 port=포트")

# 여러 인자로 전달
conn = psycopg2.connect(
		host="DB주소",
        dbname="DB이름",
        user="사용자계정",
        password="비밀번호",
        port=포트)

1-3..env파일로 정보 숨기기

# .env
dbname=DB명
user=사용자계정
host=DB주소
password=비밀번호
port=포트번호

# db 가져올 파일
import os
from dotenv import load_dotenv

# .env 파일에서 환경 변수를 로드
load_dotenv()
DBNAME = os.getenv('DB명')
USER = os.getenv('사용자계정')
HOST = os.getenv('DB주소')
PASSWORD = os.getenv('비밀번호')
PORT = os.getenv('포트번호')

1-4.cursor() 인스턴스 생성

cur = conn.cursor()

1-5.execute() SQL 명령 실행

• execute() 함수의 전달 인자로는 실행할 SQL문을 문자열 형태로 전달
• %s와 같은 Placeholder를 사용하여 컬럼명이나 레코드 부분에 변수를 전달하는 방법도 가능

1-6.commit() 호출하여 실제 트랜잭션 발생

• DB의 상태를 변경하거나 데이터를 입력, 수정, 삭제하는 경우 commit() 함수를 반드시 호출

---

2-1.Table 생성 (Create)

• 테이블을 생성하는 CREATE TABLE 쿼리 사용
• 테이블의 수정을 위한 ALTER TABLE
• 테이블의 삭제를 위한 DELETE TABLE

cur.execute(
	"CREATE TABLE 테이블명 (컬럼명 데이터타입, ... );"
)
conn.commit()    # 쿼리를 PostgreSQL에 전달하여 실행 (트랜잭션 발생)

2-2.데이터 입력 (Insert)

• 생성 된 테이블에 데이터를 입력하기 위해 INSERT INTO 쿼리 사용

# SQL 쿼리 사용
cur.execute(
	"INSERT INTO 테이블명(컬럼명1, 컬럼명2, ...) VALUES(값1, 값2, ...);"
)
conn.commit()

# placeholder 사용
cur.execute(
	"INSERT INTO 테이블명(컬럼명1, 컬럼명2, ...) VALUES (%s, %s, ...);",
    (값1, 값2, ...))  # 값1, 값2 위치에 변수 사용 가능
conn.commit()

# placeholder에 변수 사용
id = 1
name = 'Human Resource'

cur.execute(
	"INSERT INTO team VALUES (%s, %s);", (id, name)
) 
conn.commit()

2-3.데이터 수정 (Update)

• 테이블에 입력된 데이터를 수정하기 위해 UPDATE 쿼리 사용
• 변경할 값 부분에 컬럼 단위의 연산 가능

# SQL 쿼리 사용
cur.execute(
	"UPDATE 테이블명 SET 컬럼명=값1 WHERE 컬럼명=값2;"
)
conn.commit()

# placeholder 사용
cur.execute(
	"UPDATE 테이블명 SET 컬럼명=%s WHERE 컬럼명=%s;",
    (값1, 값2)) # 값1, 값2 위치에 변수 사용 가능
conn.commit()

# 컬럼 단위 연산 사용 (id 컬럼 값에 1을 더함)
cur.execute(
	"UPDATE team SET id=id+%s WHERE name=%s;", (1, 'Development')
)
conn.commit()

2-4.데이터 삭제 (Delete)

• 테이블에 입력 된 데이터를 삭제하기 위해 DELETE FROM 쿼리 사용

# SQL 쿼리 사용
cur.execute(
	"DELETE FROM 테이블명 WHERE 컬럼명=값1;"
)
conn.commit()

# placeholder 사용
cur.execute(
"DELETE FROM team WHERE name=%s;", ('Human Resource')
)
conn.commit()

2-5.데이터 조회 (Select)

• 조회 결과를 파이썬 코드에서 사용하기 위해서는 fetchone(), fetchmany(), fetchall() 등 세가지 함수를 사용
• fetchone() 함수는 Tuple 타입으로 반환
• fetchmany()와 fetchall() 함수는 Array of Tuple 타입으로 반환
• 쿼리 결과가 너무 큰 경우에 fetchall() 함수를 사용하면 Out of Memory와 같은 오류가 발생할 수 있으므로 주의
• fetchone()과 fetchall() 함수의 전달 인자는 없음
• fetchmany() 함수는 가져올 레코드 개수를 정수형 인자로 전달

# 단순 조회 쿼리 실행
cur.execute(
	"SELECT 컬럼명, ... FROM 테이블명;"
)

# 조건 지정
cur.execute(
	"SELECT 컬럼명, ... FROM 테이블명 WHERE 컬럼명 연산자 값 [논리연산자 컬럼명 연산자 값]";
)

result_one = cur.fetchone()  # 단일 결과 반환 (Tuple)
result_many = cur.fetchmany(정수형숫자)  # 여러 결과 반환 (List of Tuple)
result_all = cur.fetchall()  # 모든 결과 반환 (List of Tuple)

---

3-1.PostgreSQL 연결 해제 (Close, Disconnect)

• PostgreSQL과 연결 된 conn 인스턴스를 생성하고, 해당 인스턴스로 cursor 객체를 생성
• 연결을 해제하는 경우 두 개의 연결을 모두 해제
• 연결과 반대 순서로 cursor의 연결을 해제하고, conn 인스턴스의 연결을 해제

cur.close()  # cur 객체 연결 해제
conn.close()  # conn 인스턴스 연결 해제

• 2.5 버전부터 cursor() 함수를 호출할 때 with 구문을 사용하면, 자동으로 호출되므로 생략할 수 있음
• 직접 변수에 할당한 경우 생략하지 않도록 주의

with conn.cursor() as cur:
    cur.execute(SQL_QUERY)

conn.close()

• 가급적 프로그램 시작 부분에서 연결을 생성하고, 종료할때 명시적으로 연결을 해제하는 것이 좋음