# PostgreSQL DB pool + with문 + dynamic query

psycopg2를 사용한 PostgreSQL DB 연동에 대해 설명한다.

 

[주요 포인트]

1. DB connection pool 사용

2. with문 (resource auto closeable)

3. dynamic query

 

[1. DB connection pool]

Connection 객체를 미리 pool에 생성해두고 DB connection 시 마다 pool에서 가져다 사용하고 반환한다. ( Connection 재사용 )

* 이 코드에서는 단일 쓰레드 운용에 적합한 SimpleConnectionPool 사용했다.

# DB connection pool 생성 & 연결
pg_pool = pg.pool.SimpleConnectionPool(pool_min_conn, pool_max_conn,
                                       host=host,
                                       database=dbname,
                                       user=user,
                                       password=password,
                                       port=port)

 

[2. with문 (resource auto closeable)]

DB conn 객체, cursor 객체를 사용한 후 일일이 close하는 것은 귀찮다. 

따라서, with문을 사용하면 자동으로 close 해주기 때문에 편히 사용할 수 있다.

* 스프링 : try-with-resources 와 유사

# dbconn auto close
# connection이 with 문을 벗어날 때, 예외가 없으면 commit 발생하고, 예외 시에는 rollback 발생
with pg_pool.getconn() as dbconn:
    # cursor auto close / 트랜잭션 상태는 바뀌지 않는다.
    with dbconn.cursor() as cursor:

 

[3. dynamic query]

cursor.execute(sql) 등을 수행할 때, 해당 dynamic query문을 사용하는 것이 좋다.

 

dynamic query문 사용 시 장점은,

스프링 : preparedstatement => 미리 컴파일 되어 똑같은 쿼리문을 매번 컴파일 하지 않아도 된다는 장점이 있다.

 

이와 같이, preparedstatement와 같은 기능을 PostgreSQL에서도 제공한다.

PREPARE라는 준비된 명령문을 제공한다. (현재 데이터베이스 세션 동안만 유지됨 )

# prepareStateMent / 미리 컴파일
cursor.execute("""PREPARE testPlan as SELECT pw FROM test WHERE id = $1""")

cursor.execute("execute testPlan (%s)", (test_id,))

# row 하나 리턴
record = cursor.fetchone()

# 할당 해제
cursor.execute("DEALLOCATE testPlan")

* DEALLOCATE 로 필요시 수동 해제가 가능하다.

 

[전체 코드]

import psycopg2 as pg
from psycopg2 import pool
import os
import configparser

# DB 연결 설정 정보 세팅 ( config.ini에 정의 )
config = configparser.ConfigParser()
data_set = config.read(os.getcwd() + os.sep + 'config.ini', encoding='utf-8')

try:
    with open(os.getcwd() + os.sep + 'config.ini', encoding='utf-8') as f:
        config.read_file(f)
except IOError:
    raise Exception("config File does not exist")

user = config['DB_CONFIG']['USER']
host = config['DB_CONFIG']['HOST']
port = config['DB_CONFIG']['PORT']
password = config['DB_CONFIG']['PASSWORD']
dbname = config['DB_CONFIG']['DBNAME']

pool_min_conn = config['DB_CONFIG']['POOL_MIN_CONN']
pool_max_conn = config['DB_CONFIG']['POOL_MAX_CONN']

# DB connection pool 연결
pg_pool = pg.pool.SimpleConnectionPool(pool_min_conn, pool_max_conn,
                                       host=host,
                                       database=dbname,
                                       user=user,
                                       password=password,
                                       port=port)

if (pg_pool == False):
    raise ConnectionError("## DB connection pool created Fail")
    
    
# Test 테이블 조회
def getTest(test_id):

    dbconn = None

    try:
        # dbconn auto close
        # connection이 with 문을 벗어날 때, 예외가 없으면 commit 발생하고, 예외 시에는 rollback 발생
        with pg_pool.getconn() as dbconn:
            # cursor auto close / 트랜잭션 상태는 바뀌지 않는다.
            with dbconn.cursor() as cursor:
                try:
                    # prepareStateMent / 미리 컴파일
                    cursor.execute("""PREPARE testPlan as SELECT pw FROM test WHERE id = $1""")

                    cursor.execute("execute testPlan (%s)", (test_id,))

                    # row 하나 리턴
                    record = cursor.fetchone()
                finally:
                    # 할당 해제
                    cursor.execute("DEALLOCATE testPlan")

                return record
    except (Exception, pg.Error) as e:
        raise e
    finally:
        if pg_pool:
            # pool에 연결 반환
            pg_pool.putconn(dbconn)
            
pwd = getTest('1')
print(pwd[0])