KR_SQLite - somaz94/python-study GitHub Wiki

import sqlite3

conn = sqlite3.connect('example.db')
try:
    conn.execute('BEGIN TRANSACTION')
    conn.execute('INSERT INTO users (name, email) VALUES (?, ?)', ('Kim', '[email protected]'))
    conn.execute('UPDATE balances SET amount = amount - 100 WHERE user_id = 1')
    conn.execute('UPDATE balances SET amount = amount + 100 WHERE user_id = 2')
    conn.commit()
    print("트랜잭션이 성공적으로 완료되었다")
except Exception as e:
    conn.rollback()
    print(f"오류 발생으로 트랜잭션이 롤백되었다: {e}")
finally:
    conn.close()

class TransactionManager:
    def __init__(self, db_path):
        self.db_path = db_path
        self.connection = None
        
    def __enter__(self):
        self.connection = sqlite3.connect(self.db_path)
        self.connection.execute('BEGIN TRANSACTION')
        return self.connection
        
    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type is None:
            self.connection.commit()
        else:
            self.connection.rollback()
        self.connection.close()
        return False  # 예외 전파

# 사용 예시
def transfer_money(from_id, to_id, amount):
    with TransactionManager('finance.db') as conn:
        # 잔액 확인
        cursor = conn.execute('SELECT balance FROM accounts WHERE id = ?', (from_id,))
        from_balance = cursor.fetchone()[0]
        
        if from_balance < amount:
            raise ValueError("잔액이 부족하다")
            
        # 송금 실행
        conn.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', (amount, from_id))
        conn.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', (amount, to_id))
        
        # 거래 기록 저장
        conn.execute(
            'INSERT INTO transactions (from_id, to_id, amount, timestamp) VALUES (?, ?, ?, datetime("now"))',
            (from_id, to_id, amount)
        )

# 격리 수준 지정
conn = sqlite3.connect('example.db')
conn.execute('BEGIN IMMEDIATE TRANSACTION')
# 작업 수행
conn.commit()

import sqlite3

conn = sqlite3.connect('library.db')
# FTS5 테이블 생성
conn.execute('''
CREATE VIRTUAL TABLE IF NOT EXISTS books_fts USING fts5(
    title, 
    author, 
    content,
    tokenize='unicode61'
)
''')

# 데이터 삽입
conn.execute('''
INSERT INTO books_fts (title, author, content) VALUES 
(?, ?, ?)
''', ('파이썬 프로그래밍', '홍길동', '파이썬은 간결하고 읽기 쉬운 문법을 가진 프로그래밍 언어이다.'))

# 검색 예시
cursor = conn.execute('SELECT * FROM books_fts WHERE books_fts MATCH ?', ('프로그래밍',))
for row in cursor:
    print(row)

conn.close()

import sqlite3
import json

conn = sqlite3.connect('config.db')

# JSON 데이터를 저장할 테이블 생성
conn.execute('''
CREATE TABLE IF NOT EXISTS configs (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    settings TEXT
)
''')

# JSON 데이터 저장
app_config = {
    "version": "1.0.0",
    "features": {
        "dark_mode": True,
        "notifications": {
            "enabled": True,
            "sound": "default"
        }
    },
    "limits": [10, 20, 30]
}

conn.execute(
    "INSERT OR REPLACE INTO configs (name, settings) VALUES (?, ?)",
    ("app_config", json.dumps(app_config))
)

# JSON 속성으로 쿼리
cursor = conn.execute(
    "SELECT * FROM configs WHERE json_extract(settings, '$.features.dark_mode') = 1"
)
for row in cursor:
    config = json.loads(row[2])
    print(f"ID: {row[0]}, Name: {row[1]}")
    print(f"설정: {config}")

conn.close()

import sqlite3

conn = sqlite3.connect('locations.db')

# R-Tree 테이블 생성
conn.execute('''
CREATE VIRTUAL TABLE IF NOT EXISTS locations USING rtree(
    id,              -- 정수 기본 키
    min_lat, max_lat,  -- 위도 범위
    min_lng, max_lng   -- 경도 범위
)
''')

# 위치 데이터 삽입 (서울시 강남구 좌표 범위 예시)
conn.execute('''
INSERT INTO locations VALUES (?, ?, ?, ?, ?)
''', (1, 37.45, 37.53, 127.02, 127.08))

# 특정 지점(37.5, 127.05)에서 가까운 위치 검색
cursor = conn.execute('''
SELECT id FROM locations
WHERE min_lat <= ? AND max_lat >= ?
  AND min_lng <= ? AND max_lng >= ?
''', (37.5, 37.5, 127.05, 127.05))

for row in cursor:
    print(f"위치 ID: {row[0]}")

conn.close()