47 lines
1.4 KiB
Python
47 lines
1.4 KiB
Python
import sqlite3
|
|
con = sqlite3.connect('test.db', isolation_level=None)
|
|
con.execute('PRAGMA journal_mode=wal')
|
|
con.row_factory = lambda *args: dict(sqlite3.Row(*args))
|
|
sql = lambda *args: list(con.execute(*args))
|
|
|
|
# init
|
|
|
|
sql('''
|
|
CREATE TABLE IF NOT EXISTS books
|
|
(id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
author TEXT NOT NULL,
|
|
price REAL NOT NULL,
|
|
stock INTEGER NOT NULL)
|
|
''')
|
|
|
|
# write
|
|
|
|
for book in [
|
|
('To Kill a Mockingbird', 'Harper Lee', 12.99, 25),
|
|
('1984', 'George Orwell', 10.99, 30),
|
|
('Pride and Prejudice', 'Jane Austen', 9.99, 20),
|
|
('The Great Gatsby', 'F. Scott Fitzgerald', 11.99, 15),
|
|
('The Catcher in the Rye', 'J.D. Salinger', 10.50, 22)
|
|
]:
|
|
sql('INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?)', book)
|
|
|
|
# read
|
|
|
|
sql('SELECT * FROM books')
|
|
|
|
|
|
|
|
# 특정 조건으로 데이터 조회
|
|
expensive_books = sql('SELECT title, author, price FROM books WHERE price > 11.00 ORDER BY price DESC')
|
|
print("\nBooks priced over $11:")
|
|
for book in expensive_books:
|
|
print(f"{book['title']} by {book['author']} - ${book['price']:.2f}")
|
|
|
|
# 재고 업데이트
|
|
sql('UPDATE books SET stock = stock - 1 WHERE title = ?', ('1984',))
|
|
|
|
# 업데이트 확인
|
|
updated_book = sql('SELECT title, stock FROM books WHERE title = ?', ('1984',))[0]
|
|
print(f"\nUpdated stock for '1984': {updated_book['stock']}")
|