### Install fastlite

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Install the fastlite library using pip.

```bash
pip install fastlite
```

--------------------------------

### Fastlite Database Initialization Examples

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/configuration.md

Demonstrates various ways to initialize a Fastlite database instance using the `database()` function with different configurations.

```python
from fastlite import database

# Default configuration (WAL enabled)
db = database("myapp.sqlite")

# In-memory database
db = database(":memory:")

# Without WAL
db = database("myapp.sqlite", wal=False)

# With custom APSW flags (example: read-only mode)
from apsw import SQLITE_OPEN_READONLY
db = database("myapp.sqlite", flags=SQLITE_OPEN_READONLY)
```

--------------------------------

### Example: Using DEFAULT Sentinel

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/types.md

Illustrates the difference between using the DEFAULT sentinel for explicit defaults and providing specific values or None for overrides.

```python
from fastlite import DEFAULT

# Explicit default vs explicit override
table.insert_all(records, pk=DEFAULT)  # Use table's existing pk
table.insert_all(records, pk='id')  # Override with 'id'
table.insert_all(records, pk=None)  # Explicitly no pk
```

--------------------------------

### Import CSV Data Examples

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Demonstrates importing data from CSV strings into a database table. Shows importing into a new table, appending to an existing table, and handling schema mismatches.

```python
db = Database(":memory:")
csv1 = "id,name,age\n1,Alice,30\n2,Bob,25"
csv2 = "id,name,age\n3,Charlie,35\n4,David,40"
csv3 = "id,name,age,city\n5,Eve,45,New York"

# import file to new table
tbl = db.import_file("people", csv1)
assert len(tbl()) == 2

# import file to existing table (same schema)
tbl = db.import_file("people", csv2)
assert len(tbl()) == 4

# import file to existing table (schema change fails)
test_fail(lambda: db.import_file("people", csv3),contains='city')
```

--------------------------------

### Example: Using UNSET Sentinel for Partial Updates

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/types.md

Demonstrates how UNSET fields are handled during dataclass creation and database updates, contrasting with fields explicitly set to None.

```python
from fastlite import database, UNSET

db = database("myapp.sqlite")

@dataclass
class User:
    id: int = None
    name: str = None
    email: str = None

users = db.create(User)

# Partial user update
user_update = User(id=1, name='Alice')  # email left unset
# When converted to dict for database, only id and name are included

# Explicit None handling
user_none = User(id=2, name='Bob', email=None)  # email explicitly None
# When converted, email=None is included and sets DB column to NULL
```

--------------------------------

### Python Database Access Examples

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/accessors.md

Demonstrates various ways to access database tables using the _Getter class. This includes printing the string representation, checking for table existence, accessing single or multiple tables by name, iterating through all tables, and using attribute-style access for convenience.

```python
db = database("chinook.sqlite")

# String representation
print(db.t)  # Output: Artist, Album, Track, Genre, ...

# Membership testing
if 'Artist' in db.t:
    print("Artist table exists")

# Single access
artist = db.t['Artist']

# Multiple access
tables = db.t['Artist', 'Album', 'Track']

# Iteration
for table in db.t:
    print(table.name, len(table.columns))

# Attribute access with auto-complete
artist = db.t.Artist
```

--------------------------------

### Iterate through tables

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Demonstrates iterating through all available tables in the database and printing those whose names start with 'A'.

```python
for tbl in dt:
    if tbl.name[0]=='A': print(tbl)
```

--------------------------------

### Get a Single Artist by Name

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-methods.md

Demonstrates how to retrieve a single artist record using a parameterized WHERE clause.

```python
db = database("chinook.sqlite")
artist = db.t.Artist

# Get a single artist
ac_dc = artist.selectone(where="Name = ?", where_args=["AC/DC"])
```

--------------------------------

### Using _ColsGetter for Column Operations

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/accessors.md

This example shows how to use the _ColsGetter to list columns, retrieve specific columns by name, check for column existence, and construct SQL queries using column objects.

```python
db = database("chinook.sqlite")
artist = db.t.Artist

# Get columns accessor
cols = artist.c

# List all columns
print(cols)  # Output: ArtistId, Name

# Get specific column
name_col = cols.Name

# Check column existence
if 'ArtistId' in cols:
    print("ArtistId column exists")

# Get all columns as list
all_cols = cols()
for col in all_cols:
    print(f"Column: {col.c}")

# Use in SQL
query = f"""
    SELECT {cols.ArtistId}, {cols.Name}
    FROM {artist}
    WHERE {cols.Name} LIKE 'A%'
"""
print(query)
```

--------------------------------

### Python Example: Table.upsert() with Composite Primary Keys

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-modification.md

Illustrates using the upsert() method with tables that have composite primary keys. This example defines a dataclass for order items and then uses upsert to insert and update records based on both order_id and product_id.

```python
@dataclass
class OrderItem:
    order_id: int
    product_id: int
    quantity: int = 1

items = db.create(OrderItem, pk=['order_id', 'product_id'])

# First upsert: inserts
item = items.upsert(OrderItem(order_id=1, product_id=1, quantity=5))

# Second upsert: updates
item = items.upsert(OrderItem(order_id=1, product_id=1, quantity=3))
print(item.quantity)  # 3
```

--------------------------------

### Python Example: Using Table.upsert() for Inserts and Updates

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-modification.md

Demonstrates how to use the upsert() method to insert a new record and update an existing one using its primary key. Also shows inserting a new record and using kwargs for upsert.

```python
db = database("myapp.sqlite")
users = db.t.User

# Initial insert (doesn't exist)
user = users.upsert({'id': 1, 'name': 'Alice', 'email': 'alice@example.com'})
print(user)  # User(id=1, name='Alice', email='alice@example.com')

# Update (id 1 exists)
user = users.upsert({'id': 1, 'name': 'Alice Updated', 'email': 'alice.new@example.com'})
print(user.name)  # 'Alice Updated'

# Insert new record
user = users.upsert({'id': 2, 'name': 'Bob'})

# With kwargs
user = users.upsert(id=3, name='Charlie', email='charlie@example.com')
```

--------------------------------

### Example Usage of Table.lookup() in Python

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-modification.md

Demonstrates how to use the Table.lookup() method to find existing records or create new ones with additional values. It shows lookups for genres and artists, including using kwargs for simpler lookups.

```python
db = database("myapp.sqlite")

@dataclass
class Genre:
    id: int = None
    name: str = None

@dataclass
class Artist:
    id: int = None
    name: str = None
    genre_id: int = None

genres = db.create(Genre)
artists = db.create(Artist)

# First lookup (creates new genre)
rock = genres.lookup({'name': 'Rock'})
print(rock)  # Genre(id=1, name='Rock')

# Second lookup (returns existing)
rock2 = genres.lookup({'name': 'Rock'})
print(rock2.id)  # 1 (same as rock.id)

# Lookup with extra values to set on creation
artist = artists.lookup(
    {'name': 'Led Zeppelin'},
    extra_values={'genre_id': rock.id}
)
print(artist.genre_id)  # rock.id

# Lookup with kwargs
pop = genres.lookup(name='Pop')
```

--------------------------------

### Initialize Fastlite Database

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/test_upsert.ipynb

Initializes an in-memory database using fastlite's database() function. This is a common setup for testing or temporary data storage.

```python
from fastlite import *
db = database(':memory:')
```

--------------------------------

### Get a Single Artist by ID using Named Parameters

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-methods.md

Shows how to fetch a single artist record using named parameters in the WHERE clause.

```python
# Get with named parameter
artist_obj = artist.selectone(
    where="ArtistId = :id",
    where_args={"id": 1}
)
```

--------------------------------

### Generated Module Structure

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/utility-functions.md

This is an example of the Python module file generated by `create_mod`. It includes an `__all__` list, necessary imports, and dataclass definitions for your database tables.

```python
# db_models.py
__all__ = ["User", "Product", "Order"]
from dataclasses import dataclass
from fastlite.kw import UNSET

@dataclass
class User:
    id: int | None = None
    name: str | None = None
    email: str | None = None

@dataclass
class Product:
    id: int | None = None
    name: str | None = None
    price: float | None = None

# ... more classes
```

--------------------------------

### Example: Using Optional Boolean Type

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/types.md

Demonstrates equivalent ways to insert records using the optional boolean type for the 'alter' parameter, showing default, explicit default, always alter, and never alter behaviors.

```python
# These are equivalent ways to insert with default ALTER behavior
table.insert(record)  # Uses underlying default
table.insert(record, alter=DEFAULT)  # Explicit default
table.insert(record, alter=True)  # Always alter
table.insert(record, alter=False)  # Never alter
```

--------------------------------

### Example Usage of Table.xtra() for Filtering

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-methods.md

Demonstrates how to use xtra() to filter all operations on a table, such as retrieving, updating, and inserting records. It also shows how operations on records not matching the xtra filter will fail.

```python
db = database("myapp.sqlite")

@dataclass
class Post:
    id: int
    user_id: int
    title: str
    content: str

posts = db.create(Post)
posts.insert({'user_id': 1, 'title': 'Hello', 'content': 'World'})
posts.insert({'user_id': 2, 'title': 'Bye', 'content': 'World'})

# Filter all operations to user 1
posts.xtra(user_id=1)

# Get only returns user 1's posts
posts[1]  # Works if post 1 is by user 1

# Update respects xtra
posts.update({'id': 1, 'title': 'Updated'})  # Only updates if user_id=1

# Insert automatically adds xtra fields
new_post = posts.insert({'title': 'Another Post', 'content': '...'}) 
print(new_post.user_id)  # 1 (auto-added from xtra)

# Call filters by xtra
my_posts = posts()  # Returns only user 1's posts

# Attempting to update a record not matching xtra raises error
try:
    posts.update({'id': 2, 'title': 'Hacked'})  # Fails: post 2 not by user 1
except NotFoundError:
    print("Record not found")
```

--------------------------------

### Database Creation and Access

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/README.md

Demonstrates how to create a Database object, access tables and views, and execute SQL queries.

```python
db = database("my_database.db")
# Access tables via .t property
tables = db.t
# Access views via .v property
views = db.v
# Execute SQL with .q()
results = db.q("SELECT * FROM my_table")
```

--------------------------------

### Get Record by Primary Key (Fastlite)

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/README.md

Retrieve a single record from a table using its primary key. Supports both bracket notation and a dedicated 'get' method.

```python
table[pk]
table.get(pk)
```

--------------------------------

### Dataclass Definition Example

Source: https://github.com/answerdotai/fastlite/blob/main/README.md

An example of a Python dataclass definition generated for a database table. Note that all fields are defined as optional (e.g., `int | None`) to handle potential null values from the database.

```python
@dataclass
class Album:
    AlbumId: int | None = None
    Title: str | None = None
    ArtistId: int | None = None
```

--------------------------------

### Execute a query using the 'q' shortcut

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Demonstrates using the `q` method on the `Database` object to execute a SQL query, filtering artists by name, and retrieving the results.

```python
acdc = db.q(f"select * from {artist} where {ac.Name} like 'AC/%'")
acdc
```

--------------------------------

### Fastlite Type Inference Example

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/types.md

Illustrates how Fastlite automatically infers SQLite column types from Python type annotations in dataclasses. This example shows various Python types and their corresponding SQLite equivalents.

```python
from dataclasses import dataclass
from fastlite import database

@dataclass
class Product:
    id: int  # INTEGER PRIMARY KEY
    name: str  # TEXT
    price: float  # REAL
    available: bool = True  # INTEGER (0/1)
    description: str = None  # TEXT (nullable)
    metadata: bytes = None  # BLOB (nullable)

db = database("store.sqlite")
products = db.create(Product)
```

--------------------------------

### Create and query a database view

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Create a SQL view using `create_view` and then query it like a regular table.

```python
album = dt.Album

acca_sql = f"""select {album}.*
from {album} join {artist} using (ArtistId)
where {ac.Name} like 'AC/%'"""

db.create_view("AccaDaccaAlbums", acca_sql, replace=True)
acca_dacca = db.q(f"select * from {db.v.AccaDaccaAlbums}")
acca_dacca
```

--------------------------------

### Get dataclass source code

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Retrieve the source code of a generated dataclass using `dataclass_src`.

```python
src = dataclass_src(album_dc)
hl_md(src, 'python')
```

--------------------------------

### Get Type of a Field

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Determines the type of a field within a dataclass. Useful for schema introspection.

```python
get_typ(_Test)
```

```python
get_typ(_Test2)
```

--------------------------------

### Create and Open Databases in Fastlite

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Demonstrates how to create file-based or in-memory databases, and how to disable Write-Ahead Logging (WAL).

```python
from fastlite import database

# File-based database
db = database("myapp.sqlite")

# In-memory database
db = database(":memory:")

# Disable WAL (Write-Ahead Logging)
db = database("myapp.sqlite", wal=False)
```

--------------------------------

### Querying Tables with Options

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/configuration.md

Demonstrates various ways to query tables using different options like filtering, ordering, limiting, and selecting specific columns. Use `where` and `where_args` for filtering, `order_by` for sorting, `limit` and `offset` for pagination, and `select` for specific columns. `with_pk` returns primary key tuples, and `as_cls` controls dataclass conversion.

```python
db = database("myapp.sqlite")
users = db.t.User

# Simple query
users(limit=10)

# Filtered with parameters
users(
    where="age > ? AND status = ?",
    where_args=[18, 'active'],
    limit=20
)

# Named parameters
users(
    where="created_at >= :start AND created_at < :end",
    where_args={'start': '2024-01-01', 'end': '2024-12-31'}
)

# Pagination
page_1 = users(limit=10, offset=0)
page_2 = users(limit=10, offset=10)

# Select specific columns
names = users(select="id, name")

# With primary key tuples
with_pks = users(with_pk=True, limit=5)

# Return as dicts instead of dataclass
dicts = users(as_cls=False)

# Complex query with all options
results = users(
    where="age BETWEEN ? AND ? AND country = ?",
    where_args=[20, 65, 'USA'],
    order_by="name ASC, created_at DESC",
    limit=100,
    offset=0,
    select="id, name, email, created_at",
    as_cls=True
)
```

--------------------------------

### Check Fastlite Version

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/module-structure.md

Retrieve and print the current installed version of the fastlite library using the `__version__` attribute.

```python
from fastlite import __version__
print(__version__)  # "0.2.5"
```

--------------------------------

### Table Creation and Schema Definition

Source: https://github.com/answerdotai/fastlite/blob/main/README.md

Demonstrates how to create tables, define their schemas with data types, and set primary keys. It also shows how to access and inspect the schema.

```APIDOC
## Table Creation and Schema

Access a table that doesn't exist yet:
```python
cats = dt.cats
```

Create a table with specified columns and primary key:
```python
cats.create(id=int, name=str, weight=float, uid=int, pk='id')
```

Inspect the generated SQL schema:
```python
hl_md(cats.schema, 'sql')
```

Create a table from a class definition:
```python
class Cat: id:int; name:str; weight:float; uid:int
cats = db.create(Cat)
```

Inspect the schema for a table created from a class:
```python
hl_md(cats.schema, 'sql')
```
```

--------------------------------

### Get Dataclass Source

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Retrieve the source code of a dataclass using `dataclass_src`. This is useful for generating module files.

```python
src = dataclass_src(artist_dc)
hl_md(src, 'python')
```

--------------------------------

### Create Database Connection with Custom Flags

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/types.md

Illustrates how to create a Connection object with specific APSW flags for advanced configuration. The Database can then be initialized using this connection.

```python
from fastlite import database, Connection

# Create connection with custom flags
conn = Connection("myapp.sqlite", flags=0x00000100)
db = database(":memory:")  # Or pass connection
```

--------------------------------

### Create and query a view

Source: https://github.com/answerdotai/fastlite/blob/main/README.md

Create a SQL view using `create_view` and then query the newly created view. This demonstrates how to define and use database views with fastlite.

```python
album = dt.Album

acca_sql = f"""select {album}.*\nfrom {album} join {artist} using (ArtistId)\nwhere {ac.Name} like 'AC/%'"""

db.create_view("AccaDaccaAlbums", acca_sql, replace=True)
acca_dacca = db.q(f"select * from {db.v.AccaDaccaAlbums}")
acca_dacca
```

--------------------------------

### Create Tables in Fastlite

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Illustrates how to create tables in fastlite using dataclasses or keyword arguments, with options for custom primary keys, NOT NULL constraints, default values, and schema change tolerance.

```python
from dataclasses import dataclass

@dataclass
class User:
    id: int
    name: str
    email: str
    age: int = None

# From dataclass
users = db.create(User)

# With custom options
users = db.create(
    User,
    pk='email',  # Custom primary key
    not_null=['name', 'email'],  # NOT NULL columns
    defaults={'created_at': 'CURRENT_TIMESTAMP'},
    strict=False  # Allow schema changes
)

# From keyword arguments
users = db.t.users.create(
    id=int,
    name=str,
    email=str,
    pk='id'
)
```

--------------------------------

### Initialize Database Connection

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Establishes a connection to a SQLite database file. This is the first step before performing any database operations.

```python
db = Database("chinook.sqlite")
```

--------------------------------

### nbdev Entry Point Configuration

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/module-structure.md

Configure the nbdev entry point for the fastlite project, enabling discoverability and indexing by nbdev-aware tools.

```toml
[project.entry-points.nbdev]
fastlite = "fastlite._modidx:d"

[tool.nbdev]
allowed_metadata_keys = ['solveit']
allowed_cell_metadata_keys = ['solveit_ai']
custom_sidebar = false
```

--------------------------------

### Initialize and Access Database Tables

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Initializes classes for database tables and retrieves a single album record. This requires the `db` object to be set up and `globals()` to be passed for namespace modification.

```python
#| eval: false
db.set_classes(globals())
db.get_tables(globals())

albums(limit=1)
```

--------------------------------

### Access Database Views with Attribute Access

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/database.md

Use the `v` property to get a `_ViewsGetter` object for attribute-based access to database views. This provides auto-completion support for view names.

```python
db = database("chinook.sqlite")

# Create a view
db.create_view("recent_albums", """
    SELECT * FROM Album 
    WHERE ArtistId IN (
        SELECT ArtistId FROM Artist 
        WHERE Name LIKE '%Rock%'
    )
""", replace=True)

# Access the view
view = db.v.recent_albums

# List all views
print(db.v)

# Check if view exists
if 'recent_albums' in db.v:
    print("View exists")
```

--------------------------------

### Display Function Documentation

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Use `show_doc` to display the documentation for a specific function, including its signature and a brief description.

```python
show_doc(Database.import_file)
```

--------------------------------

### List Tables in a Database

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/accessors.md

Use the `dir()` function on the tables accessor to get a list of all table names in the database, excluding system tables. This accessor is typically accessed via `db.t`.

```python
db = database("chinook.sqlite")

# Get the tables accessor (normally accessed as db.t)
tables_getter = db.t

# List all tables
print(dir(tables_getter))
# ["Album", "Artist", "Customer", "Employee", "Genre", ...]

# Access by name
album_table = tables_getter.Album

# Check if specific table exists
if 'Artist' in tables_getter:
    print("Artist table is in the database")

# Iterate over all tables
for table in tables_getter:
    print(f"Table: {table.name}")
```

--------------------------------

### Add 'q' shortcut for database queries

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Patches the `Database` class to include a `q` method, providing a concise shortcut for executing SQL queries and returning results as a list.

```python
#| exports
@patch
def q(self:Database, sql: str, params=None):
    return list(self.query(sql, params=params))
```

--------------------------------

### Foreign Key Constraint Example in Fastlite

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/configuration.md

Define a table with foreign key constraints by specifying a list of tuples, where each tuple contains the referencing column, the referenced table, and the referenced column.

```python
from fastlite import database
from dataclasses import dataclass

db = database("myapp.sqlite")

@dataclass
class Post:
    id: int
    user_id: int
    title: str
    content: str

posts = db.create(
    Post,
    foreign_keys=[
        ('user_id', 'users', 'id')  # user_id refs users.id
    ]
)
```

--------------------------------

### Import necessary libraries

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Import core fastlite components and utility functions from fastcore.

```python
from fastlite import *
from fastcore.utils import *
from fastcore.net import urlsave
```

--------------------------------

### Modify Table Schema

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Use the `transform` method to change column types, rename columns, add constraints, or reorder columns. To get the generated SQL without execution, use `transform_sql`.

```python
# Change column types
users.transform(types={'age': int})
```

```python
# Rename column
users.transform(rename={'user_name': 'username'})
```

```python
# Add NOT NULL constraint
users.transform(not_null=['email'])
```

```python
# Reorder columns
users.transform(column_order=['id', 'name', 'email', 'age'])
```

```python
# Get SQL without executing
sql = users.transform_sql(types={'phone': str})
print(sql)
```

--------------------------------

### Execute a select query

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Use the 'q' method on the database object to execute a SQL query and retrieve results.

```python
db.q(qry)
```

--------------------------------

### Importing Data from Delimited Files with Database.import_file()

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/database.md

Demonstrates various ways to use the import_file method, including importing from file paths, strings, file handles, and specifying primary keys or allowing table alterations.

```python
db = database("myapp.sqlite")

# Import from file path
csv_path = "data/users.csv"
users = db.import_file("users", csv_path)

# Import from CSV string
csv_data = """id,name,age
1,Alice,30
2,Bob,25
3,Charlie,35"""
people = db.import_file("people", csv_data)

# Import from file handle
with open("data/products.csv") as f:
    products = db.import_file("products", f)

# Import with primary key specified
orders = db.import_file("orders", "data/orders.csv", pk="order_id")

# Import with type inference and ALTER
data = "name,email,signup_date\nJohn,john@example.com,2024-01-01"
users = db.import_file("users", data, pk="email", alter=True)
```

--------------------------------

### Execute SQL Queries with Parameter Support

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/database.md

The `q` method executes SQL queries, returning results as a list of dictionaries. It supports both positional and named parameters for safe query construction.

```python
db = database("chinook.sqlite")

# Simple query
results = db.q("SELECT * FROM Artist LIMIT 2")
print(results)
# [{'ArtistId': 1, 'Name': 'AC/DC'}, {'ArtistId': 2, 'Name': 'Accept'}]
```

```python
results = db.q("SELECT * FROM Artist WHERE ArtistId > ?", [5])
```

```python
results = db.q(
    "SELECT * FROM Artist WHERE Name LIKE :pattern",
    {"pattern": "%Rock%"}
)
```

--------------------------------

### Filtering Albums with Extra Fields

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Filter albums by extra fields like `ArtistId`. Indexing after setting `xtra` will only return records matching those fields. This example shows a case where no record matches the filter.

```python
album.xtra(ArtistId=1)

try:
    album[5]
except NotFoundError:
    print("Not found")
```

--------------------------------

### Creating a New Table

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Create a new table with specified columns and their types. The `pk` argument defines the primary key.

```python
cats.create(id=int, name=str, weight=float, uid=int, pk='id')
hl_md(cats.schema, 'sql')
```

--------------------------------

### Generate Database Diagram with diagram()

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/utility-functions.md

The `diagram` function generates a Graphviz DOT diagram of your database schema, showing tables, columns, and relationships. It requires the `graphviz` package. Use `render=False` to get the raw DOT string.

```python
from fastlite import database, diagram

db = database("chinook.sqlite")

# Diagram specific tables
tables = db.t['Artist', 'Album', 'Track', 'Genre']
diagram_obj = diagram(tables, size=10, ratio=0.4)

# Display (in Jupyter)
diagram_obj

# Get raw DOT format
dot_string = diagram(tables, render=False)
print(dot_string)

# Use neato layout (better for complex diagrams)
diagram_obj = diagram(tables, neato=True, ratio=0.5)

# Export to file
diagram_obj.render('database_diagram', format='png', cleanup=True)

# Diagram single table
single = diagram(db.t.Album)
```

--------------------------------

### database()

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/database.md

Creates and returns a Database instance with optional Write-Ahead Logging (WAL) mode enabled. This is the primary entry point for interacting with SQLite databases using fastlite.

```APIDOC
## database()

### Description
Creates and returns a Database instance with optional Write-Ahead Logging (WAL) mode enabled.

### Method
`def`

### Parameters
#### Path Parameters
- **path** (str or Path) - Required - Path to the SQLite database file. Parent directories are created if they don't exist.
- **wal** (bool) - Optional - Enable Write-Ahead Logging mode for improved concurrency. Defaults to True.
- **flags** (int or None) - Optional - APSW connection flags to customize SQLite behavior. Defaults to None.

### Returns
A `Database` instance ready for use.

### Example
```python
from fastlite import database

# Create an in-memory database
db = database(":memory:")

# Create a file-based database with WAL enabled
db = database("my_app.sqlite")

# Disable WAL mode
db = database("my_app.sqlite", wal=False)

# Use custom flags
from apsw import Connection
db = database("my_app.sqlite", flags=0x00000100)
```
```

--------------------------------

### Querying Albums with Primary Keys

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Retrieve albums with their primary keys and records. Use `with_pk=1` to include primary keys in the output.

```python
album(with_pk=1, limit=2)
```

--------------------------------

### Get Row by Primary Key - Python

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-methods.md

Retrieves a single row from a table using its primary key. Supports optional default values for missing rows and can return results as dictionaries or dataclasses. Use for direct row lookups.

```python
def get(
    self: Table,
    pk_values: list | tuple | str | int,
    as_cls: bool = True,
    xtra: dict | None = None,
    default: Any = UNSET
) -> Any:
    ...

```

```python
db = database("myapp.sqlite")
users = db.t.User

# Get with NotFoundError on missing
user = users.get(123)

# Get with default value
user = users.get(999, default=None)
if user is None:
    print("User not found")

# Get with no dataclass conversion
user_dict = users.get(123, as_cls=False)

# Composite key
user = users.get([123, 'email@example.com'])

# Sets last_pk and last_rowid
user = users.get(1)
print(users.last_pk)  # 1

```

--------------------------------

### Basic Queries

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Demonstrates fundamental methods for retrieving data, such as fetching all rows, limiting results, accessing by primary key, and handling missing records.

```APIDOC
## Basic Queries

### Description
Provides methods for basic data retrieval operations.

### Methods
- `users()`: Retrieves all rows from the users table.
- `users(limit=N)`: Retrieves the first N rows.
- `users[PK]`: Retrieves a row by its primary key (PK).
- `users.get(PK, default=None)`: Retrieves a row by primary key, returning a default value if not found.
- `users.selectone(where=..., where_args=...)`: Retrieves exactly one row matching the WHERE clause, raising an error if zero or more than one row is found.
```

--------------------------------

### Access Table Columns with .c

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-methods.md

Use the `.c` property to get a `_ColsGetter` object for accessing table columns. Column objects stringify with proper table and column quoting for SQL. This is useful for building SQL queries dynamically.

```python
db = database("chinook.sqlite")
artist = db.t.Artist

# Access column object
name_col = artist.c.Name

# Use in f-string
query = f"SELECT * FROM {artist} WHERE {artist.c.Name} LIKE 'AC/%'"
print(query)
# Output: SELECT * FROM "Artist" WHERE "Artist"."Name" LIKE 'AC/%'

# List all columns
cols = artist.c()
for col in cols:
    print(col)  # ArtistId, Name

# Check if column exists
if 'Name' in artist.c:
    print("Name column exists")

# Display columns
print(artist.c)  # ArtistId, Name
```

--------------------------------

### Construct and Display SQL Query

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Constructs a SQL query to select albums joined with artists, filtering by artist name. The query is then displayed as Markdown. Ensure `dt.Album` and `artist` are defined.

```python
album = dt.Album
acca_sql = f"select {album}.*\nfrom {album} join {artist} using (ArtistId)\nwhere {ac.Name} like 'AC/%'"

hl_md(acca_sql, 'sql')
```

--------------------------------

### Handle Missing Records and SQL Errors

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Catch `NotFoundError` for missing records when accessing by index or using `get`. `SQLError` can be used to handle general SQL execution errors. Use `xtra` for additional filtering before attempting record access.

```python
from fastlite import NotFoundError, MissingPrimaryKey, SQLError

# Handle missing record
try:
    user = users[999]
except NotFoundError:
    print("User not found")

# Use default instead
user = users.get(999, default=None)

# Handle SQL errors
try:
    results = db.q("INVALID SQL")
except SQLError as e:
    print(f"SQL Error: {e}")

# With xtra filtering
users.xtra(org_id=1)
try:
    user = users[5]  # Fails if user 5 not in org 1
except NotFoundError:
    print("User not found in organization")
```

--------------------------------

### Multi-Tenant Filtering with xtra()

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/README.md

Explains and demonstrates the xtra() method for scoping operations to a specific organization, including automatic filtering and insertion.

```python
table.xtra(organization_id=1)  # All operations now scope to org 1
table()                         # Returns only org 1's rows
table.insert({'name': 'X'})    # organization_id=1 added automatically
```

--------------------------------

### Table.create() and Table.transform() with kwargs

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-modification.md

These methods extend table creation and transformation by allowing column definitions to be passed as keyword arguments (**kwargs), offering a more convenient API.

```APIDOC
## Table.create() and Table.transform() with kwargs

### Description
These methods allow column definitions to be passed as `**kwargs` instead of in the `columns` dict, providing a more convenient API.

### Method Signatures
```python
def create(
    self: Table,
    columns: dict[str, Any] | None = None,
    pk: Any | None = None,
    **kwargs
) -> Table

def transform(
    self: Table,
    *,
    types: dict | None = None,
    **kwargs
) -> Table

def transform_sql(
    self: Table,
    *,
    types: dict | None = None,
    **kwargs
) -> list[str]
```

### Example
```python
db = database("myapp.sqlite")

# Create with kwargs instead of columns dict
users = db.t.users.create(id=int, name=str, email=str, age=int, pk='id')

# Transform columns using kwargs
users.transform(phone=str, address=str)

# Get transformation SQL
sql = users.transform_sql(status=str, updated_at='TEXT DEFAULT CURRENT_TIMESTAMP')
print(sql)  # List of SQL statements that would be executed
```
```

--------------------------------

### Create Fastlite Database Instance

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/database.md

Use the `database` function to create a new Database instance. It supports in-memory databases, file-based databases with WAL enabled by default, and custom APSW connection flags.

```python
from fastlite import database

# Create an in-memory database
db = database(":memory:")

# Create a file-based database with WAL enabled
db = database("my_app.sqlite")

# Disable WAL mode
db = database("my_app.sqlite", wal=False)

# Use custom flags
from apsw import Connection
db = database("my_app.sqlite", flags=0x00000100)
```

--------------------------------

### Fastlite Error Handling Quick Reference

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/INDEX.md

Demonstrates how to catch and handle common Fastlite exceptions including NotFoundError, SQLError, and MissingPrimaryKey. Shows alternative methods like .get() for handling missing records.

```python
from fastlite import NotFoundError, SQLError, MissingPrimaryKey

# NotFoundError - no matching record
try:
    user = users[999]
except NotFoundError:
    print("User not found")

# Use default instead
user = users.get(999, default=None)

# SQLError - SQL syntax or execution error
try:
    db.q("INVALID SQL")
except SQLError as e:
    print(f"SQL Error: {e}")

# MissingPrimaryKey - when pk is required but missing
try:
    table.upsert(record)
except MissingPrimaryKey:
    print("Table needs primary key")
```

--------------------------------

### Fastlite Package Exports

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/module-structure.md

The main entry point (__init__.py) re-exports all public APIs from submodules. This includes functions for database operations, type extraction, and diagram creation, as well as various type definitions.

```python
from fastlite import *

# Available imports:
from fastlite import (
    # Functions
    database,
    all_dcs,
    create_mod,
    get_typ,
    diagram,
    
    # Types
    Database,
    Table,
    View,
    NotFoundError,
    DEFAULT,
    UNSET,
    MISSING,
    MissingPrimaryKey,
    opt_bool,
    SQLError,
    Connection,
    ForeignKeysType,
    Default,
    Queryable,
)
```

--------------------------------

### Access Database Views

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Demonstrates accessing the database views getter and listing available view names. The `dv` object, an instance of `_ViewsGetter`, provides access to view names.

```python
dv = db.v
dv
```

--------------------------------

### Import IPython and fastcore utilities

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Imports modules for displaying Markdown, testing, and saving URLs, commonly used in interactive environments like Jupyter notebooks.

```python
from IPython.display import Markdown
from fastcore.test import test_fail, test_eq
from fastcore.net import urlsave
import logging
```

--------------------------------

### Create and Transform Table Columns with Kwargs

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/table-modification.md

Demonstrates creating and transforming table columns using keyword arguments for a more convenient API. The `transform_sql` method returns a list of SQL statements.

```python
db = database("myapp.sqlite")

# Create with kwargs instead of columns dict
users = db.t.users.create(id=int, name=str, email=str, age=int, pk='id')

# Transform columns using kwargs
users.transform(phone=str, address=str)

# Get transformation SQL
sql = users.transform_sql(status=str, updated_at='TEXT DEFAULT CURRENT_TIMESTAMP')
print(sql)  # List of SQL statements that would be executed
```

--------------------------------

### Using Columns in SQL Queries

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/README.md

Demonstrates referencing table columns directly in SQL queries using column objects.

```python
f"SELECT * FROM {table} WHERE Name = 'Test'"
```

--------------------------------

### Download and connect to the database

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Downloads the chinook database if it doesn't exist and establishes a database connection using fastlite.

```python
url = 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite'
path = Path('chinook.sqlite')
if not path.exists(): urlsave(url, path)

db = database("chinook.sqlite")
```

--------------------------------

### Instantiate dataclass from query result

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Creates a dataclass from the 'Artist' table schema and then instantiates it using the data retrieved from a previous query.

```python
artist_dc = artist.dataclass()
art1_obj = artist_dc(**acdc[0])
art1_obj
```

--------------------------------

### Formatting SQL Queries with _Col

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/accessors.md

Demonstrates how to use the _Col object to construct SQL queries with properly quoted table and column names. The __str__ method is used for SQL compatibility, while __repr__ is for display.

```python
db = database("chinook.sqlite")
artist = db.t.Artist

# Get a column object
name_col = artist.c.Name

# Use in SQL with proper quoting
query = f"SELECT * FROM {artist} WHERE {name_col} LIKE 'A%'"
print(query)
# Output: SELECT * FROM "Artist" WHERE "Artist"."Name" LIKE 'A%'

# String representation for SQL
print(str(name_col))  # "Artist"."Name"

# Repr for display
print(repr(name_col))  # Name

# Iteration (not typical use)
for char in name_col:
    print(char)  # N, a, m, e
```

--------------------------------

### Import Data into Fastlite Tables

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Demonstrates importing data into fastlite tables from CSV files, CSV strings, or file handles, with options to specify the primary key.

```python
# From CSV file
users = db.import_file("users", "data/users.csv")

# From CSV string
csv_data = "id,name,age\n1,Alice,30"
people = db.import_file("people", csv_data, pk="id")

# From file handle
with open("data/products.csv") as f:
    products = db.import_file("products", f)
```

--------------------------------

### Database.q

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/database.md

Executes a SQL query and returns results as a list of dictionaries.

```APIDOC
## Database.q()

### Description
Executes a SQL query and returns results as a list of dictionaries.

### Parameters
#### Path Parameters
* **sql** (str) - Required - SQL query string.
* **params** (tuple, dict, or None) - Optional - Query parameters. Use `?` for positional or `:name` for named parameters.

### Returns
A list of dictionaries, one per result row.

### Example
```python
db = database("chinook.sqlite")

# Simple query
results = db.q("SELECT * FROM Artist LIMIT 2")
print(results)
# [{'ArtistId': 1, 'Name': 'AC/DC'}, {'ArtistId': 2, 'Name': 'Accept'}]

# Query with positional parameters
results = db.q("SELECT * FROM Artist WHERE ArtistId > ?", [5])

# Query with named parameters
results = db.q(
    "SELECT * FROM Artist WHERE Name LIKE :pattern",
    {"pattern": "%Rock%"}
)
```
```

--------------------------------

### Comprehensive Table Creation with Fastlite

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/configuration.md

Use this snippet to define a table with multiple options including name, primary key, not null constraints, default values, and column order. Set `strict=False` to allow schema modifications.

```python
from fastlite import database
from dataclasses import dataclass

db = database("myapp.sqlite")

@dataclass
class User:
    id: int
    username: str
    email: str
    created_at: str = None
    updated_at: str = None
    is_admin: bool = False

# Comprehensive table creation
users = db.create(
    User,
    name='users',
    pk='id',
    not_null=['username', 'email'],
    defaults={
        'created_at': 'CURRENT_TIMESTAMP',
        'updated_at': 'CURRENT_TIMESTAMP'
    },
    column_order=['id', 'username', 'email', 'created_at', 'is_admin'],
    strict=False  # Allow schema modifications
)
```

--------------------------------

### Paginate Records (Fastlite)

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/README.md

Implement pagination for query results by specifying the 'limit' and 'offset' parameters.

```python
table(limit=10, offset=0)
```

--------------------------------

### Insert Record with Keyword Argument

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/test_insert.ipynb

Demonstrates inserting a record using keyword arguments. The method returns the inserted item, and its 'name' attribute is asserted to be 'Alice'.

```python
assert people.insert(name='Alice').name == 'Alice'
```

--------------------------------

### Inserting Data into Table

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Demonstrates inserting data into a table using different formats: a dictionary, a dataclass instance, and a standard Python class instance.

```python
cats.insert({'name': 'Rex', 'weight': 12.2})
```

```python
CatDC = cats.dataclass()
cats.insert(CatDC(name='Tom', weight=10.2))
```

```python
cat = cats.insert(Cat(name='Jerry', weight=5.2))
```

--------------------------------

### Fastlite Star Import

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/module-structure.md

Use this import pattern to bring all items defined in `__all__` from the core and kw modules into the current namespace.

```python
from fastlite import *

# Imports all __all__ items from core and kw modules
```

--------------------------------

### Import Data from File (Fastlite)

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/README.md

Import data from a CSV or TSV file into a database table. Specify the table name and the file path.

```python
db.import_file(name, file)
```

--------------------------------

### Handle MissingPrimaryKey Exception

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/types.md

Demonstrates how to catch and handle the MissingPrimaryKey exception when creating a table without a primary key. It shows the error scenario and the solution by specifying a primary key.

```python
from fastlite import database, MissingPrimaryKey

db = database("myapp.sqlite")

@dataclass
class BadTable:
    col1: str
    col2: int
    # No primary key

try:
    table = db.create(BadTable)  # May fail
except MissingPrimaryKey:
    print("Need a primary key")

# Solution: specify pk
table = db.create(BadTable, pk='col1')
```

--------------------------------

### Paginate Results

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Implement pagination by using `limit` and `offset` for fetching pages of results. Calculate the total number of pages using the total count and page size.

```python
page_size = 20
page = 1

# Get page of results
results = users(limit=page_size, offset=(page-1)*page_size)

# Total count
total = len(list(users()))

# Calculate total pages
import math
total_pages = math.ceil(total / page_size)
```

--------------------------------

### View Table Schema

Source: https://github.com/answerdotai/fastlite/blob/main/README.md

Display the SQL schema of a created table.

```sql
CREATE TABLE [cats] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [weight] FLOAT,
   [uid] INTEGER
)
```

--------------------------------

### Table Querying and Modification

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/README.md

Shows how to query a table using different methods and perform insert, update, and delete operations.

```python
table = db.t.my_table
# Query all rows
all_rows = table()
# Query a specific row by primary key
row = table[1]
# Insert a new row
table.insert({'name': 'New Name', 'value': 100})
# Update a row
table.update({'value': 150}, pk=1)
# Delete a row
table.delete(pk=1)
```

--------------------------------

### Using Column Objects

Source: https://github.com/answerdotai/fastlite/blob/main/_autodocs/quick-reference.md

Explains how to use column objects for dynamic query construction and referencing table columns within SQL queries.

```APIDOC
## Using Column Objects in Queries

### Description
Demonstrates how to interact with column objects for dynamic query building.

### Usage
- Access a column object via `table.c.ColumnName` (e.g., `users.c.Name`).
- Use column objects directly within SQL strings for dynamic queries (e.g., `f"SELECT * FROM {users} WHERE {users.c.age} > 30"`).
- Iterate through `table.c` to list all columns of a table.
```

--------------------------------

### Generate Database Diagram

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/index.ipynb

Create a visual diagram of specified database tables using the `diagram` function, with options to control size and aspect ratio. Requires the `graphviz` library.

```python
diagram(db.t['Artist','Album','Track','Genre','MediaType'], size=8, ratio=0.4)
```

--------------------------------

### Initialize database connection and set logging level

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Sets the logging level for 'apsw' to ERROR to suppress verbose logs and initializes a Database object with the specified SQLite file.

```python
logging.getLogger('apsw').setLevel(logging.ERROR)
db = Database("chinook.sqlite")
```

--------------------------------

### Access multiple tables at once

Source: https://github.com/answerdotai/fastlite/blob/main/nbs/00_core.ipynb

Illustrates retrieving multiple tables, 'Album' and 'Artist', simultaneously using dictionary-like access on the `_TablesGetter` instance.

```python
dt['Album','Artist']
```