쿼리 노트
PostgreSQL로 개발을 진행하다가 헷갈리는 쿼리의 문법이 등장했을 때, 기록을 위한 페이지 입니다.
대부분은 파이썬 dash를 활용한 대시보드에서 사용하는 쿼리를 기록하고 있습니다.
S1. 동적 쿼리
query = text(
f"SELECT f_id, d_id, i_group, q, a, user, uid
FROM qadb.id_set
WHERE {d_value} LIKE :x {operator} {d_value2} LIKE :y AND d_id IN ({c_values})
ORDER BY uid ASC")
이 쿼리의 전체적인 목적은 qadb.id_set 테이블에서 특정 조건(d_value, c_value)을 만족하는 행을 조회하고, 이를 uid 값에 따라 오름차순으로 정렬하는 것입니다. 이 쿼리는 동적으로 구성되며, 실제 실행 시점에 {d_value}, {d_value2}, {operator}, {c_values} 등의 변수들이 구체적인 값으로 대체됩니다.
- { }내의 변수들은 쿼리가 실행될 때 대체될 변수들로, 대시보드에서 동적으로 선택된 값들을 나타냅니다.
- LIKE :x와 LIKE :y는 부분 일치 검색을 수행합니다. 여기서 :x와 :y는 바인딩 변수로, 실제 값은 쿼리가 실행될 때 제공됩니다.
- {operator}는 'AND'나 'OR' 같은 논리 연산자를 나타내며, 두 조건 사이의 관계를 정의합니다.
쿼리문 바인딩 변수
connection.execute(
query, {"x": '%' + input_value + '%', "y": '%' + input_value2 + '%'}).fetchall()
{"x": '%' + input_value + '%', "y": '%' + input_value2 + '%'} : 이는 쿼리의 :x와 :y 변수를 실제 값으로 바인딩합니다.
input_value = 'A0001'이 입력되면, 첫 번째 LIKE 조건은 intent_id LIKE '%A0001%'가 됩니다.
input_value2 = ''이므로, 두 번째 LIKE 조건은 intent_id LIKE '%%'가 됩니다. 이는 모든 f_id값을 포함하는 조건입니다.
operator = 'AND'이므로, 두 조건 사이에는 'AND' 논리 연산자가 사용됩니다.
결과적으로, WHERE 절은 f_id LIKE '%A0001%' AND f LIKE '%%'와 같이 됩니다.
리스트 -> 문자열 형식 변환
c_values = ['a', 'b', 'c']
c_values = ', '.join(f"'{item}'" for item in c_values)
c_values 리스트의 값들을 문자형 형식으로 변환하는 이유는 SQL 쿼리 내에서 이 값을 문자열로 적절하게 포맷팅하고 사용하기 위함입니다.
- 리스트 컴프리헨션:
- 리스트 컴프리헨션 f"'{item}'" for item in checkbox_value는 checkbox_value 리스트에 있는 각 item에 대해 독립적으로 실행됩니다.
- 이 과정에서 각 item은 따옴표로 감싸진 문자열로 변환됩니다. 예를 들어, c_values = ['a', 'b', 'c']라면, 리스트 컴프리헨션의 결과는 ["'a'", "'b'", "'c'"]가 됩니다.
- join 메소드:
- join 메소드는 이 리스트 컴프리헨션의 결과를 입력으로 받습니다.
- 그런 다음 ' '.join(["'a'", "'b'", "'c'"])는 리스트의 모든 요소를 콤마와 공백 ', '을 구분자로 사용하여 하나의 문자열로 결합합니다.
- 따라서 최종 결과는 "'a', 'b', 'c'"입니다.
이 과정은 다음 두 가지 주요 목적을 가지고 있습니다:
- SQL 쿼리에 적합한 형식으로 변환: 데이터베이스 쿼리에서 리스트를 직접 사용할 수 없기 때문에, 리스트를 SQL 쿼리에 적합한 문자열 형식으로 변환해야 합니다. 예를 들어, SQL의 IN 절은 ('a', 'b', 'c')와 같은 형식을 요구합니다. ', '.join(...) 메소드를 사용하여 리스트의 각 항목을 콤마와 공백으로 구분된 단일 문자열로 결합하는 것은 이 요구사항을 충족시키는 효율적인 방법입니다.
- SQL 인젝션 방지: 문자열 포맷팅을 통해 각 항목을 따옴표로 감싸서 SQL 인젝션을 방지할 수 있습니다. f"'{item}'" 형식은 각 항목을 따옴표로 묶어 SQL에서 사용될 때 문자열로 인식되게 합니다. 이는 리스트의 값이 문자열인 경우에 특히 중요합니다.
S2. 동적 데이터베이스 업데이트
engine = create_engine(get_db_info())
with engine.connect() as connection:
# 데이터베이스에 추가하는 쿼리 실행
query = text(f"""
UPDATE bdb.test_set
SET brand_id = '{edit_b_id}',
intent_group = '{edit_i_group}',
question = '{edit_question}',
answer_type = '{edit_as_type}',
call_type = '{edit_cl_type}',
button_label = '{edit_button_label}',
url_call = '{edit_url}',
intent_desc = '{edit_i_desc}',
use_yn = '{edit_yn}',
answer = '{edit_answer}'
WHERE i_id = '{edit_i_id}'
""")
result = connection.execute(query)
connection.commit()
query = text(f"""..."""): 여기서 text 함수는 문자열을 SQL 쿼리로 변환하는 데 사용됩니다. f 문자는 f-string으로, Python에서 변수를 문자열에 직접 삽입할 수 있게 해줍니다. 쿼리는 UPDATE 문을 사용하여 bdb.test_set 테이블의 특정 레코드를 업데이트합니다. 여기서 edit_b_id, edit_i_group 등의 변수는 콜백 함수의 인자에서 받은 값으로, 사용자가 입력한 값을 나타냅니다.
WHERE intent_id = '{edit_intent_id}': 이 부분은 어떤 레코드를 업데이트할지 지정합니다. i_id가 edit_i_id와 일치하는 레코드에 대해 업데이트를 진행합니다.
result = connection.execute(query): 이 줄은 준비된 쿼리를 실제로 데이터베이스에 전송하고 실행합니다. connection.execute() 메소드는 query를 데이터베이스에 보내고 그 결과를 result에 저장합니다.
connection.commit(): 데이터베이스에 대한 변경사항(여기서는 업데이트된 레코드)을 확정하기 위해 트랜잭션을 커밋합니다. 이것은 수행된 모든 변경사항이 데이터베이스에 영구적으로 반영되도록 합니다.
이 코드는 사용자의 입력을 기반으로 특정 데이터베이스 레코드를 업데이트하는 전형적인 방법을 나타냅니다. 이 방식은 웹 애플리케이션에서 사용자 인터페이스를 통해 데이터베이스를 관리할 때 흔히 사용됩니다.
S3. IN 연산자 구문 오류
check_values=[]
if checkbox1_value :
check_values.append('A')
elif checkbox2_value :
check_values.append('B')
elif checkbox3_value :
check_values.append('C')
elif checkbox4_value :
check_values.append('D')
elif checkbox5_value :
check_values.append('E')
c_values = ', '.join(f"'{item}'" for item in check_values)대시보드 화면에서 선택된 checkbox의 값만 데이터베이스에서 데이터를 불러오도록 하고 싶어 위와 같이 코드를 구성했습니다.
query = text(
f"SELECT f_id, d_id, i_group, q, a, user, uid
FROM qadb.id_set
WHERE {d_value} LIKE :x {operator} {d_value2} LIKE :y AND d_id IN ({c_values})
ORDER BY uid ASC")SyntaxError: 오류: 구문 오류, ")" 부근
LINE 1: ...IKE '%%' AND i_id LIKE '%%' AND d_id IN () ORDER BY...그러나 5개의 checkbox값 모두 선택하지 않는 경우 위와 같은 구문 오류가 발생했습니다. 구체적으로는, brand_id IN () 부분에서 문제가 발생했습니다. IN 연산자 뒤에는 하나 이상의 값을 포함한 괄호가 와야 하기 때문입니다. checkbox값을 모두 선택하지 않아 c_values의 값이 모두 비어 있기 때문에 구문 오류(SyntaxError)가 발생한 것입니다.
이 문제를 해결하기 위해 아래와 같이 두 가지 로직을 수정했습니다.
check_values=[]
if checkbox1_value :
check_values.append('A')
if checkbox2_value :
check_values.append('B')
if checkbox3_value :
check_values.append('C')
if checkbox4_value :
check_values.append('D')
if checkbox5_value :
check_values.append('E')
# check_values가 비어 있으면 더미 값 '-1'을 사용
if not check_values:
check_values.append('-1')
if문과 elif 문
첫 번째 코드 블록
- 이 코드는 if와 elif를 사용하여 각 체크박스를 확인합니다.
- elif는 이전 조건이 거짓일 때만 평가되므로, 이 코드는 오직 하나의 체크박스 값만 check_values 리스트에 추가합니다. 즉, 여러 체크박스가 선택되어도 첫 번째로 참인 체크박스에 해당하는 값만 리스트에 추가됩니다.
두 번째 코드 블록
- 이 코드는 여러 개의 독립적인 if 문을 사용합니다.
- 각 if 문은 서로 독립적으로 평가되므로, 여러 체크박스가 선택될 경우 각각의 참인 체크박스에 해당하는 값들이 모두 checkbox_values 리스트에 추가됩니다.
첫 번째 코드는 오직 하나의 조건만 충족하는 경우에만 동작하고, 두 번째 코드는 여러 조건이 동시에 충족될 수 있으며, 각각의 참인 조건에 대해 독립적으로 동작합니다.
첫 번째 코드의 경우 데이터베이스에서 370건의 데이터만 조회할 수 있었다면, 로직을 변경한 두 번째 코드는 데이터베이스의 모든 데이터를 조회할 수 있게 됐습니다.
예외처리
d_id IN () 구문에서 발생하는 오류는, check_values 리스트가 비어 있을 때 발생합니다. 이를 해결하기 위해, check_values 리스트가 비어 있을 때 적절한 대체 로직을 적용해야 합니다.
에러 방지용 더미 값 사용
check_values가 비어 있을 때, SQL 에러를 방지하기 위해 더미 값(예: 불가능한 값)을 사용할 수 있습니다. 예를 들어, d_id가 절대로 취할 수 없는 값을 넣어 구문 오류를 방지했습니다.
그 외에도 아래와 같은 방법으로 위의 문제를 해결할 수 있습니다.
- 기본 값 사용: check_values가 비어 있을 경우, d_id에 대해 특정 기본 값을 사용할 수 있습니다. 예를 들어, 모든 d_id를 선택하거나, 특정 기본 d_id를 지정할 수 있습니다.
- 조건문 제거: check_values가 비어 있을 때, d_id IN () 구문을 쿼리에서 완전히 제거할 수 있습니다. 이 경우, d_id에 대한 필터링이 적용되지 않습니다.
'개발 > DB' 카테고리의 다른 글
| [DB]DBeaver에서 데이터베이스 제약 조건 수정 및 데이터 기본값 정의 (0) | 2024.01.17 |
|---|---|
| [DB]PostgreSQL 문법과 기능 정리 (DDL, DML, DCL) (0) | 2024.01.16 |
| [DB]PostgreSQL설치 및 DBeaver에서 연결하기 (로컬) (0) | 2024.01.16 |