Basic Usage

from pydantic import BaseModel
from pydantic_sqlite import DataBase

class Product(BaseModel):
    uuid: str
    name: str
    price: float
    in_stock: bool

db = DataBase()

# Create and add a product
product = Product(
    uuid="prod-001",
    name="Laptop",
    price=999.99,
    in_stock=True
)
db.add("Products", product)

# Add more products
db.add("Products", Product(uuid="prod-002", name="Mouse", price=29.99, in_stock=True))
db.add("Products", Product(uuid="prod-003", name="Keyboard", price=79.99, in_stock=False))

# Iterate through all products
for product in db("Products"):
    status = "Available" if product.in_stock else "Out of stock"
    print(f"{product.name}: ${product.price} ({status})")

# Get all products in stock
for product in db("Products", where="in_stock = :stock", where_args={"stock": True}):
    print(f"{product.name} is available")

# Get products over $50
for product in db("Products", where="price > :min_price", where_args={"min_price": 50}):
    print(f"{product.name}: ${product.price}")

# Complex queries
for product in db(
    "Products",
    where="price > :min_price AND in_stock = :stock",
    where_args={"min_price": 50, "stock": True}
):
    print(product.name)

from typing import Optional
from pydantic import BaseModel
from pydantic_sqlite import DataBase

class DataExample(BaseModel):
    uuid: str
    name: str
    count: int
    rating: float
    active: bool
    description: Optional[str] = None
    notes: Optional[str] = None
    categories: list[str]

db = DataBase()

# Add one example with all fields
db.add("Examples", DataExample(
    uuid="ex-1",
    name="Complete Example",
    count=42,
    rating=4.5,
    active=True,
    description="A complete entry",
    notes=None,  # This optional field is None
    categories=["python", "database", "tutorial"]
))

# Retrieve and verify all types
for item in db("Examples"):
    assert isinstance(item.uuid, str)
    assert isinstance(item.name, str)
    assert isinstance(item.count, int)
    assert isinstance(item.rating, float)
    assert isinstance(item.active, bool)
    assert isinstance(item.categories, list)
    # Optional fields can be None or their type
    assert item.notes is None
    assert item.description == "A complete entry"

    print(f"{item.name}: {item.rating}★")
    print(f"  Description: {item.description}")
    print(f"  Categories: {', '.join(item.categories)}")

from pydantic import BaseModel
from pydantic_sqlite import DataBase

class User(BaseModel):
    uuid: str
    username: str
    email: str

db = DataBase()

# Initial user
user = User(uuid="user-1", username="alice", email="alice@example.com")
db.add("Users", user)

# Update: add the same UUID with new data
updated_user = User(uuid="user-1", username="alice", email="alice.new@example.com")
db.add("Users", updated_user)

# Only one entry with uuid="user-1", with the new email
for user in db("Users"):
    print(user.email)  # alice.new@example.com

# Check by primary key value (uuid)
if db.model_in_table("Users", "user-1"):
    print("User exists")

# Check by model instance
if db.model_in_table("Users", user):
    print("Alice is in the database")

# Get a specific model by uuid
user = db.model_from_table("Users", "user-1")
if user:
    print(f"Found: {user.username}")

from pydantic_sqlite import DataBase

# Create a database backed by a file
db = DataBase("my_database.db")

# ... add and query data ...

# Save to disk
db.save("my_database.db")

db = DataBase("my_database.db")

# The database file is automatically loaded if it exists
for person in db("Persons"):
    print(person)