from pathlib import Path

import pandas as pd
from sqlalchemy import create_engine, text


database = Path("orders.db")
engine = create_engine(f"sqlite:///{database}")

orders = pd.DataFrame(
    {
        "order_id": ["A100", "A101", "A102"],
        "customer": ["Ada", "Lin", "Mira"],
        "status": ["open", "closed", "open"],
        "total_usd": [125.50, 88.00, 212.25],
    }
)

with engine.begin() as connection:
    orders.to_sql(
        "orders",
        connection,
        if_exists="replace",
        index=False,
    )

with engine.connect() as connection:
    table = pd.read_sql("orders", connection)
    open_orders = pd.read_sql(
        text(
            "SELECT order_id, customer, total_usd "
            "FROM orders "
            "WHERE status = :status "
            "ORDER BY order_id"
        ),
        connection,
        params={"status": "open"},
    )
    row_count = connection.execute(text("SELECT COUNT(*) FROM orders")).scalar_one()

print(open_orders.to_string(index=False))
print()
print(open_orders.dtypes)
print()
print(f"table columns: {', '.join(table.columns)}")
print(f"database rows: {row_count}")
print(f"query rows: {len(open_orders)}")
print(f"order IDs match: {open_orders['order_id'].tolist() == ['A100', 'A102']}")