::: IT인터넷 :::

psycopg2으로 PostgreSQL 사용하기

곰탱이푸우 2022. 5. 30. 08:20
psycopg2-binary 라이브러리에서 제공하는 함수로 PostgreSQL을 사용하는 방법이다.
psycopg2에 대한 소개와 설치 방법은 다음 포스팅을 참고한다.
 

psycopg2의 소개와 설치 방법

Python을 이용하여 PostgreSQL을 사용하는 방법에 대해 알아본다. PostgreSQL의 psql에서 SQL과 JOIN을 사용하는 방법에 대해 다뤘다. 해당 내용은 다음 포스팅을 참고한다. PostgreSQL의 SQL 기본 사용법 오픈

www.bearpooh.com

 

해당 함수들은 CRUD 클래스로 래핑 (Wrapping)할 때 기능 정의에 사용한다.
 
사용하기 전에 코드 상단에 해당 라이브러리를 사용할 것으로 정의해야 한다.
# 라이브러리 이름은 psycopg2-binary이지만
# 코드에서 사용할때는 psycopg2로 지정한다.

import psycopg2

 

 

연결 (Connect)

PostgreSQL과 연결하기 위해서는 connect 함수를 사용한다.
연결할 데이터베이스의 주소, 포트, 계정과 비밀번호를 전달해야 한다.
 
다음과 같이 사용한다.
# 한개 인자로 전달
conn = psycopg2.connect("host=DB주소 dbname=DB이름 user=사용자계정 password=비밀번호 port=포트")

# 여러 인자로 전달
conn = psycopg2.connect(host="DB주소", dbname="DB이름",
                        user="사용자계정", password="비밀번호", port=포트)
 
해당 함수에 대한 자세한 설명은 아래 문서를 참고한다.
정상적으로 연결되면 데이터를 조작하기 위한 인스턴스를 생성한다.
cur = conn.cursor()

 

해당 함수에 대한 자세한 설명은 아래 문서를 참고한다.
이후 과정은 cur를 통해 사용한다.
 
예제 코드는 다음과 같다.
conn = psycopg2.connect(hhost="10.0.0.2", dbname="postgres",
    user="postgres", password="postgresql", port=5432)
cur = conn.cursor()

 

 

변경 (Insert, Delete, Update)

PostgreSQL에서 SQL 명령을 실행하기 위해 execute 함수를 사용한다.
앞서 생성한 인스턴스인 cur를 사용한다.
 
execute 함수의 전달 인자로는 실행할 SQL 문을 문자열 형태로 전달한다.
물론 %s와 같은 Placeholder를 사용하여 컬럼명이나 레코드 부분에 변수를 전달하는 방법도 가능하다.
 
해당 함수에 대한 자세한 설명은 아래 문서를 참고한다.
PostgreSQL과의 연결 정보가 저장 된 conn 인스턴스의 commit() 함수를 호출하여 실제 트랜잭션을 발생시킨다.
Database의 상태를 변경하거나 데이터를 입력, 수정, 삭제하는 경우 commit() 함수를 반드시 호출해야 한다.
 
해당 함수에 대한 자세한 설명은 아래 문서를 참고한다.
Table 생성 (Create)
데이터 입력을 테스트하기 위해 테이블을 생성하는 CREATE TABLE 쿼리를 사용한다.
테이블의 수정을 위한 ALTER TABLE과 삭제를 위한 DELETE TABLE도 사용 가능하다.
 
다음과 같이 사용한다.
cur.execute("CREATE TABLE 테이블명 (컬럼명 데이터타입, ... );")
conn.commit()    # 쿼리를 PostgreSQL에 전달하여 실행 (트랜잭션 발생)
 
예제 코드는 다음과 같다.
cur.execute("CREATE TABLE team (id SERIAL PRIMARY KEY, name VARCHAR(5));")
conn.commit()

 

 

데이터 입력 (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()
 
예제 코드는 다음과 같다.
# SQL 쿼리만 사용
cur.execute("INSERT INTO team (id, name) VALUES (1, 'Human Resource');")
conn.commit()

# placeholder 사용
cur.execute("INSERT INTO team (id, name) VALUES (%s, %s);", (1, 'Human Resource'))
conn.commit()
 
Placeholder의 값을 지정한 부분에 변수를 전달하는 것도 가능하다.
# placeholder에 변수 사용
id = 1
name = 'Human Resource'

cur.execute("INSERT INTO team VALUES (%s, %s);", (id, name)) 
conn.commit()
 
 
데이터 수정 (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()

 

예제 코드는 다음과 같다.
# SQL 쿼리만 사용
cur.execute("UPDATE team SET name='Development' WHERE id=1;")
conn.commit()

# placeholder 사용 
cur.execute("UPDATE team SET name=%s WHERE id=%s;", ('Development', 1))
conn.commit()

 

Placeholder의 값을 지정한 부분에 변수를 전달하는 것도 가능하다.
 
또한 아래와 같이 변경할 값 부분에 컬럼 단위의 연산도 가능하다.
# 컬럼 단위 연산 사용 (id 컬럼 값에 1을 더함)
cur.execute("UPDATE team SET id=id+%s WHERE name=%s;", (1, 'Development'))
conn.commit()
 
데이터 삭제 (Delete)
테이블에 입력 된 데이터를 삭제하기 위해 DELETE FROM 쿼리를 사용한다.
# SQL 쿼리만 사용
cur.execute("DELETE FROM 테이블명 WHERE 컬럼명=값1;")
conn.commit()

# placeholder 사용
cur.execute("DELETE FROM 테이블명 WHERE 컬럼명=%s;", (값1)) # 값1 위치에 변수 사용 가능
conn.commit()
 
예제 코드는 다음과 같다.
# SQL 쿼리만 사용
cur.execute("DELETE FROM team WHERE name='Human Resource';")
conn.commit()

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

 

 

조회 (Select)

PostgreSQL에서 SQL 명령을 실행하기 위해 execute 함수를 사용한다.
execute 관련 내용은 '변경 (Insert, Delete, Update)' 항목의 내용을 참고한다.
 
조회 결과를 파이썬 코드에서 사용하기 위해서는 fetchone(), fetchmany(), fetchall()  등 세가지 함수를 사용한다.
fetchone()  함수는 Tuple 타입으로 반환하고, fetchmany()와 fetchall() 함수는 Array of Tuple 타입으로 반환한다.
쿼리 결과가 너무 큰 경우에 fetchall() 함수를 사용하면 Out of Memory와 같은 오류가 발생할 수 있으므로 주의해야 한다.
 
fetchone()과 fetchall() 함수의 전달 인자는 없다.
해당 함수에 대한 자세한 설명은 아래 문서를 참고한다.
 
fetchmany() 함수는 가져올 레코드 개수를 정수형 인자로 전달한다.
지정하지 않으면 Cursor 클래스에 정의 된  arraysize 값인 1이 기본 값으로 전달 된다.
해당 함수에 대한 자세한 설명은 아래 문서를 참고한다.
다음과 같이 사용한다.
# 단순 조회 쿼리 실행
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)
 
예제 코드는 다음과 같다.
cur.execute("SELECT id, name FROM team WHERE id < 3;")

result_one = cur.fetchone()  # Tuple
result_many = cur.fetchmany(2)  # Array of Tuple
result_all = cur.fetchall()    # Array of Tuple

# 이후 과정은 파이썬의 Tuple 또는 Array of Tuple 처리 방법 적용

 

 

해제 (Close, Disconnect)

사용이 완료 된 인스턴스는 연결을 해제한다.
 
PostgreSQL과 연결 된 conn 인스턴스를 생성하고, 해당 인스턴스로 cursor 객체를 생성했다.
따라서 연결을 해제하는 경우 두 개의 연결을 모두 해제해야 한다.
 
연결하지 않으면 PostgreSQL에는 해제 되지 않은 세션이 계속 존재하게 된다.
 
연결과 반대 순서로 cursor의 연결을 해제하고, conn 인스턴스의 연결을 해제한다.
두 경우 모두 close() 함수를 사용한다.
 
해당 함수에 대한 설명은 아래 문서를 참고한다.
다음과 같이 사용한다.
cur.close()  # cur 객체 연결 해제
conn.close()  # conn 인스턴스 연결 해제
 
참고로 2.5 버전부터 cursor 함수를 호출할 때 with 구문을 사용하면, 자동으로 호출되므로 생략할 수 있다.
아래와 같이 직접 변수에 할당한 경우 connect 함수의 기술 문서에는 는 해당 사항이 없으므로 생략하지 않도록 주의한다.
conn = psycopg2.connect('~~~')

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

conn.close()
 
예외적으로 connect 함수도 아래와 같이 with 구문과 함께 사용하면 생략 가능하다.
with psycopg2.connect('~~~'):
    with conn.cursor() as cur:
        cur.execute(SQL_QUERY)

 

 
기술 문서에는 언급되어 있지 않지만, Python의 with 구문 특징에 의해 close 함수가 호출된다.
with 구문이 종료되면서 호출 되는 __exit__ 특수 메소드 내부에서, 해당 객체의 close 함수를 호출하기 때문이다.
 
그러나 프로그램 실행 도중 빈번하게 연결과 해제를 반복하는 것이 좋은 방법은 아니다.
가급적 프로그램 시작 부분에서 연결을 생성하고, 종료할때 명시적으로 연결을 해제하는 것이 좋다.