books_service/app/database.py - task-7 - Flyer/library-api

books_service/app/database.py
52 строки2 KB
Alex
Task 3: Add SQLite databases per service
31 май 2026, 17:43
31 май 2026, 17:430417f59
import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(__file__), "..", "data", "books.db")

def get_connection():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            author_id INTEGER NOT NULL,
            year INTEGER
        )
    """)
    # Добавим тестовые книги, если таблица пуста
    cursor.execute("SELECT COUNT(*) FROM books")
    if cursor.fetchone()[0] == 0:
        cursor.execute("INSERT INTO books (id, title, author_id, year) VALUES (1, 'Мастер и Маргарита', 1, 1967)")
        cursor.execute("INSERT INTO books (id, title, author_id, year) VALUES (2, 'Преступление и наказание', 2, 1866)")
        cursor.execute("INSERT INTO books (id, title, author_id, year) VALUES (3, 'Война и мир', 3, 1869)")
        conn.commit()
    conn.close()

def get_all_books() -> list[dict]:
    conn = get_connection()
    rows = conn.execute("SELECT id, title, author_id, year FROM books").fetchall()
    conn.close()
    return [dict(row) for row in rows]

def get_book_by_id(book_id: int) -> dict | None:
    conn = get_connection()
    row = conn.execute("SELECT id, title, author_id, year FROM books WHERE id = ?", (book_id,)).fetchone()
    conn.close()
    if row:
        return dict(row)
    return None

def add_book(title: str, author_id: int, year: int) -> dict:
    conn = get_connection()
    cursor = conn.execute("INSERT INTO books (title, author_id, year) VALUES (?, ?, ?)", (title, author_id, year))
    conn.commit()
    book_id = cursor.lastrowid
    conn.close()
    return {"id": book_id, "title": title, "author_id": author_id, "year": year}