LabEx
로그인무료 가입

SQLite JSON 처리

SQLiteBeginner
지금 연습하기

소개

이 랩에서는 SQLite 내에서 JSON 데이터를 처리하는 방법을 배우게 됩니다. SQLite 데이터베이스 내에서 JSON 데이터를 저장, 추출, 필터링 및 업데이트하는 방법을 탐구할 것입니다. 이 랩은 현대 데이터 관리에서 점점 더 중요해지고 있는 기술인 SQLite 에서 JSON 데이터를 사용하는 방법에 대한 실질적인 소개를 제공합니다.

데이터베이스 및 테이블 생성

이 단계에서는 JSON 데이터를 저장하기 위해 SQLite 데이터베이스와 테이블을 생성합니다. SQLite 는 데이터를 단일 파일에 저장하는 경량 데이터베이스로, 관리가 용이합니다.

먼저, 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.

이제 데이터베이스를 저장할 디렉토리를 생성해 보겠습니다.

mkdir sqlite_json
cd sqlite_json

이 명령어는 sqlite_json이라는 디렉토리를 생성한 다음 현재 디렉토리를 해당 디렉토리로 변경합니다. 이렇게 하면 프로젝트 파일이 정리됩니다.

다음으로, mydatabase.db라는 SQLite 데이터베이스를 생성합니다.

sqlite3 mydatabase.db

이 명령어는 SQLite 셸을 열어 mydatabase.db 데이터베이스에 연결합니다. 데이터베이스 파일이 존재하지 않으면 SQLite 가 생성합니다.

이제 id, name, details 열이 있는 products라는 테이블을 생성합니다. details 열은 JSON 데이터를 텍스트로 저장합니다.

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    details TEXT
);

이 SQL 명령어는 products 테이블을 생성합니다.

  • id: 각 새 제품에 대해 자동으로 증가하는 고유한 정수입니다.
  • name: 제품 이름 (예: Laptop, Smartphone).
  • details: 제품에 대한 JSON 데이터를 저장하는 텍스트 필드입니다.

JSON 데이터 삽입

이 단계에서는 products 테이블에 JSON 데이터를 삽입합니다.

products 테이블에 두 개의 샘플 레코드를 삽입해 보겠습니다.

INSERT INTO products (name, details) VALUES (
    'Laptop',
    '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}'
);

INSERT INTO products (name, details) VALUES (
    'Smartphone',
    '{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}'
);

이 INSERT 문은 products 테이블에 두 개의 행을 추가합니다. details 열에는 JSON 데이터가 텍스트 문자열로 포함되어 있습니다.

데이터가 올바르게 삽입되었는지 확인하려면 다음 쿼리를 실행합니다.

SELECT * FROM products;

예상 출력:

1|Laptop|{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}
2|Smartphone|{"brand": "Samsung", "model": "Galaxy S21", "specs": {"display": "6.2 inch", "camera": "12MP", "storage": "128GB"}}

이 출력은 JSON 데이터가 products 테이블에 성공적으로 저장되었음을 확인합니다.

사용자 정의 함수로 JSON 필드 추출

SQLite 에는 내장 JSON 함수가 없으므로 JSON 문자열에서 데이터를 추출하기 위해 사용자 정의 Python 함수를 생성합니다.

먼저 SQLite 셸을 종료합니다.

.exit

이제 json_extractor.py라는 Python 파일을 생성합니다.

nano json_extractor.py

다음 Python 코드를 json_extractor.py 파일에 붙여넣습니다.

## 필요한 라이브러리 가져오기
import sqlite3
import json

## 경로를 사용하여 JSON 문자열에서 값을 추출하는 함수 정의
def json_extract(json_str, path):
    try:
        ## JSON 문자열을 Python 사전으로 파싱
        json_data = json.loads(json_str)
        ## 경로를 구성 요소로 분할 (예: 'specs.cpu'는 ['specs', 'cpu'] 가 됨)
        path_components = path.split('.')
        ## 전체 JSON 객체로 시작
        value = json_data
        ## 경로 구성 요소를 사용하여 JSON 객체 탐색
        for component in path_components:
            ## 현재 구성 요소의 값 가져오기
            value = value.get(component)
        ## 최종 값 반환
        return value
    ## JSON 이 유효하지 않거나 경로가 존재하지 않는 경우 오류 처리
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## 데이터베이스에 연결하고 사용자 정의 함수를 등록하는 함수 정의
def connect_db(db_path):
    ## 지정된 경로의 SQLite 데이터베이스에 연결
    conn = sqlite3.connect(db_path)
    ## 'json_extract' Python 함수를 사용자 정의 SQL 함수로 등록
    ## "json_extract"는 SQL 에서의 이름, 2 는 인수의 개수, json_extract 는 Python 함수
    conn.create_function("json_extract", 2, json_extract)
    ## 데이터베이스 연결 반환
    return conn

## 스크립트가 직접 실행될 때 실행되는 블록
if __name__ == '__main__':
    ## 데이터베이스에 연결하고 함수 등록
    conn = connect_db('mydatabase.db')
    ## SQL 쿼리를 실행하기 위한 커서 객체 생성
    cursor = conn.cursor()

    ## 사용자 정의 SQL 함수를 사용하여 'products' 테이블의 'name'이 'Laptop'인 행의 'details' 열에서 'brand' 추출
    cursor.execute("SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'")
    ## 결과 가져와서 출력
    print(cursor.fetchone()[0])

    ## 사용자 정의 SQL 함수를 사용하여 'products' 테이블의 'name'이 'Laptop'인 행의 중첩된 'specs' 객체에서 'cpu' 추출
    cursor.execute("SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'")
    ## 결과 가져와서 출력
    print(cursor.fetchone()[0])

    ## 데이터베이스 연결 종료
    conn.close()

이 Python 코드는 JSON 문자열과 경로를 입력으로 받아 해당 경로의 값을 반환하는 json_extract 함수를 정의합니다. 또한 SQLite 데이터베이스에 연결하고 json_extract 함수를 등록하는 connect_db 함수도 포함합니다.

  • json.loads(json_str): 이 줄은 JSON 문자열을 Python 사전으로 파싱합니다.
  • path.split('.'): 이 줄은 경로를 구성 요소 목록으로 분할합니다. 예를 들어 'specs.cpu'는 ['specs', 'cpu']가 됩니다.
  • 루프는 경로 구성 요소를 반복하며 JSON 데이터의 중첩된 값에 액세스합니다.

파일을 저장하고 nano를 종료합니다.

이제 Python 스크립트를 실행합니다.

python3 json_extractor.py

예상 출력:

Dell
Intel i7

이 스크립트는 데이터베이스에 연결하고 json_extract 함수를 등록한 다음 이를 사용하여 Laptop 의 브랜드와 CPU 를 추출합니다.

JSON 쿼리를 사용하여 데이터 필터링

이 단계에서는 사용자 정의 json_extract 함수를 사용하여 JSON 필드 내의 값을 기반으로 데이터를 필터링합니다.

json_extractor.py 파일을 다시 엽니다.

nano json_extractor.py

데이터베이스를 쿼리하는 함수를 포함하도록 json_extractor.py 파일을 수정합니다.

## 필요한 라이브러리 가져오기
import sqlite3
import json

## 경로를 사용하여 JSON 문자열에서 값을 추출하는 함수 정의
def json_extract(json_str, path):
    try:
        ## JSON 문자열을 Python 사전으로 파싱
        json_data = json.loads(json_str)
        ## 경로를 구성 요소로 분할 (예: 'specs.cpu'는 ['specs', 'cpu'] 가 됨)
        path_components = path.split('.')
        ## 전체 JSON 객체로 시작
        value = json_data
        ## 경로 구성 요소를 사용하여 JSON 객체 탐색
        for component in path_components:
            ## 현재 구성 요소의 값 가져오기
            value = value.get(component)
        ## 최종 값 반환
        return value
    ## JSON 이 유효하지 않거나 경로가 존재하지 않는 경우 오류 처리
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## 데이터베이스에 연결하고 사용자 정의 함수를 등록하는 함수 정의
def connect_db(db_path):
    ## 지정된 경로의 SQLite 데이터베이스에 연결
    conn = sqlite3.connect(db_path)
    ## 'json_extract' Python 함수를 사용자 정의 SQL 함수로 등록
    conn.create_function("json_extract", 2, json_extract)
    ## 데이터베이스 연결 반환
    return conn

## JSON 필드를 기반으로 제품을 필터링하는 함수 정의
def filter_products(db_path, json_path, value):
    ## 데이터베이스에 연결
    conn = connect_db(db_path)
    ## 커서 객체 생성
    cursor = conn.cursor()
    ## JSON 값으로 필터링하기 위해 f-string 을 사용하여 SQL 쿼리 생성
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    ## 쿼리 실행
    cursor.execute(query)
    ## 일치하는 모든 결과 가져오기
    results = cursor.fetchall()
    ## 데이터베이스 연결 닫기
    conn.close()
    ## 결과 반환
    return results

## 스크립트가 직접 실행될 때 실행되는 블록
if __name__ == '__main__':
    ## 예시 사용법:
    ## 브랜드가 'Dell'인 제품 필터링
    dell_products = filter_products('mydatabase.db', 'brand', 'Dell')
    print("Products with brand 'Dell':", dell_products)

    ## CPU 가 'Intel i7'인 제품 필터링
    intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')
    print("Products with CPU 'Intel i7':", intel_products)

이 코드는 데이터베이스 경로, JSON 경로 및 값을 입력으로 받는 filter_products 함수를 추가합니다. 그런 다음 데이터베이스에 연결하고 json_extract 함수를 등록하며 지정된 JSON 경로의 값이 주어진 값과 일치하는 모든 제품을 찾기 위한 쿼리를 실행합니다.

파일을 저장하고 nano를 종료합니다.

이제 Python 스크립트를 실행합니다.

python3 json_extractor.py

예상 출력:

Products with brand 'Dell': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]
Products with CPU 'Intel i7': [(1, 'Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "16GB", "storage": "512GB SSD"}}')]

이 출력은 지정된 기준과 일치하는 제품을 보여줍니다.

JSON 값 업데이트

이 단계에서는 JSON 필드 내의 값을 업데이트하는 방법을 배웁니다.

json_extractor.py 파일을 엽니다.

nano json_extractor.py

JSON 및 데이터베이스 업데이트를 위한 함수를 포함하도록 json_extractor.py 파일을 수정합니다.

## 필요한 라이브러리 가져오기
import sqlite3
import json

## 경로를 사용하여 JSON 문자열에서 값을 추출하는 함수 정의
def json_extract(json_str, path):
    try:
        ## JSON 문자열을 Python 사전으로 파싱
        json_data = json.loads(json_str)
        ## 경로를 구성 요소로 분할
        path_components = path.split('.')
        ## 전체 JSON 객체로 시작
        value = json_data
        ## JSON 객체 순회
        for component in path_components:
            value = value.get(component)
        return value
    except (json.JSONDecodeError, AttributeError, TypeError):
        return None

## 데이터베이스에 연결하고 사용자 정의 함수를 등록하는 함수 정의
def connect_db(db_path):
    ## 데이터베이스에 연결
    conn = sqlite3.connect(db_path)
    ## 사용자 정의 SQL 함수 등록
    conn.create_function("json_extract", 2, json_extract)
    return conn

## JSON 필드를 기준으로 제품을 필터링하는 함수 정의
def filter_products(db_path, json_path, value):
    ## 데이터베이스에 연결
    conn = connect_db(db_path)
    ## 커서 객체 생성
    cursor = conn.cursor()
    ## JSON 값을 기준으로 필터링하는 SQL 쿼리 생성
    query = f"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'"
    ## 쿼리 실행
    cursor.execute(query)
    ## 일치하는 모든 결과 가져오기
    results = cursor.fetchall()
    ## 연결 종료
    conn.close()
    return results

## JSON 문자열 내의 값을 업데이트하는 함수 정의
def update_json_value(json_str, path, new_value):
    try:
        ## JSON 문자열을 Python 사전으로 파싱
        json_data = json.loads(json_str)
        ## 경로를 구성 요소로 분할
        path_components = path.split('.')
        ## 대상 값의 부모로 이동
        target = json_data
        for i in range(len(path_components) - 1):
            target = target.get(path_components[i])

        ## 대상 값 업데이트
        target[path_components[-1]] = new_value
        ## Python 사전을 다시 JSON 문자열로 변환
        return json.dumps(json_data)
    except (json.JSONDecodeError, AttributeError, TypeError):
        ## 오류가 발생하면 원본 JSON 문자열 반환
        return json_str

## 데이터베이스에서 제품 세부 정보를 업데이트하는 함수 정의
def update_product_details(db_path, product_name, json_path, new_value):
    ## 데이터베이스에 연결
    conn = sqlite3.connect(db_path)
    ## 커서 객체 생성
    cursor = conn.cursor()

    ## 지정된 제품의 현재 세부 정보 가져오기
    ## '?'는 SQL Injection 을 방지하기 위한 플레이스홀더입니다.
    cursor.execute("SELECT details FROM products WHERE name = ?", (product_name,))
    result = cursor.fetchone()
    ## 제품이 존재하지 않으면 연결을 종료하고 False 를 반환합니다.
    if not result:
        conn.close()
        return False

    ## 현재 JSON 세부 정보 문자열 가져오기
    current_details = result[0]

    ## 새 값으로 JSON 문자열 업데이트
    updated_details = update_json_value(current_details, json_path, new_value)

    ## 수정된 JSON 문자열로 데이터베이스 업데이트
    cursor.execute("UPDATE products SET details = ? WHERE name = ?", (updated_details, product_name))
    ## 변경 사항을 데이터베이스에 커밋
    conn.commit()
    ## 연결 종료
    conn.close()
    return True

## 스크립트가 직접 실행될 때 실행되는 블록
if __name__ == '__main__':
    ## 예시 사용법: 노트북 메모리를 32GB 로 업데이트
    update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB')
    print("Laptop memory updated to 32GB")

    ## 데이터를 다시 가져와 업데이트 확인
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    cursor.execute("SELECT details FROM products WHERE name = 'Laptop'")
    ## 업데이트된 세부 정보 가져오기
    updated_details = cursor.fetchone()[0]
    print("Updated Laptop details:", updated_details)
    ## 연결 종료
    conn.close()

이 코드는 두 가지 함수를 추가합니다.

  • update_json_value: JSON 문자열, 경로 및 새 값을 입력으로 받습니다. JSON 문자열을 파싱하고 지정된 경로의 값을 업데이트한 다음 업데이트된 JSON 문자열을 반환합니다.
  • update_product_details: 데이터베이스 경로, 제품 이름, JSON 경로 및 새 값을 입력으로 받습니다. 데이터베이스에 연결하고 제품의 현재 JSON 데이터를 검색한 다음 update_json_value를 사용하여 지정된 경로의 값을 업데이트한 다음 수정된 JSON 데이터로 데이터베이스를 업데이트합니다.

파일을 저장하고 nano를 종료합니다.

이제 Python 스크립트를 실행합니다.

python3 json_extractor.py

예상 출력:

Laptop memory updated to 32GB
Updated Laptop details: {"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "memory": "32GB", "storage": "512GB SSD"}}

이 출력은 노트북의 메모리가 데이터베이스에서 32GB 로 업데이트되었음을 확인합니다.

요약

이 랩에서는 SQLite 내에서 JSON 데이터를 처리하는 방법을 배웠습니다. 먼저 데이터베이스와 JSON 데이터를 저장할 테이블을 만들었습니다. 그런 다음, JSON 데이터를 테이블에 삽입하는 방법을 배웠습니다. JSON 데이터에서 특정 필드를 추출하는 사용자 정의 Python 함수를 만들고, 이 함수를 사용하여 JSON 필드 내의 값을 기반으로 데이터를 필터링했습니다. 마지막으로, 사용자 정의 Python 함수를 사용하여 JSON 필드 내의 값을 업데이트하는 방법을 배웠습니다. 이러한 기술은 SQLite 데이터베이스 내에서 JSON 데이터를 효과적으로 관리하기 위한 기반을 제공합니다.

관련 SQLite 코스
SQLite 초보자를 위한 안내

SQLite 초보자를 위한 안내

databasesqlite
SQLite 중급에서 고급

SQLite 중급에서 고급

databasesqlite