pyDAL Documentation

pyDAL allows you to extend table behavior using computed fields, virtual fields, and common filters.

Computed vs Virtual Fields

• Computed Field: Calculated during insert or update and physically stored in the database. Defined via the compute argument in Field().
• Virtual Field: Calculated on-the-fly every time the field is accessed from a result set. It is not stored in the database and cannot be used in queries. Defined using Field.Virtual().

Common Filters

You can attach a query to a table using _common_filter. Every Set (query) performed against that table will automatically include this filter. This is useful for implementing soft-delete or multi-tenant isolation.

• To bypass the common filter, use db(query, ignore_common_filters=True).

Callbacks

You can hook into the lifecycle of a record using callbacks:

• _before_insert
• _after_update
• _after_delete

Note: If a _before_* callback returns a truthy value, the operation is cancelled.

# Computed Field (Stored)
db.define_table("person",
    Field("first"),
    Field("last"),
    Field("full", compute=lambda row: f"{row['first']} {row['last']}"),
)

# Virtual Field (Not stored, computed on access)
class PersonMethods:
    def full(row):
        return row.first + " " + row.last

db.person.full = Field.Virtual("full", lambda row: row.first + " " + row.last)

# Common Filter (e.g., for soft-delete)
db.thing._common_filter = lambda q: db.thing.deleted == False

# Callbacks
db.thing._before_insert.append(lambda fields: ...)
db.thing._after_update.append(lambda set, fields: ...)
db.thing._after_delete.append(lambda set: ...)

pyDAL's backend logic is split into two primary modules: pydal.backend_base (the framework) and pydal.backends.<name> (specific database implementations).

Core Abstractions

The backend relies on four collaborating layers:

1. Adapter (SQLAdapter / NoSQLAdapter): Manages the session-level connection.
2. Dialect (SQLDialect / NoSQLDialect): Responsible for converting Abstract Syntax Tree (AST) nodes into database-specific strings (e.g., SQL).
3. Representer (SQLRepresenter / NoSQLRepresenter / JSONRepresenter): Converts Python values into database literals.
4. Parser (BasicParser + mixins): Converts driver row values back into Python values.

How Connection URIs Work

When you instantiate DAL("protocol://..."), pyDAL uses the URI prefix to select a registered adapter from the adapters registry. The selected adapter then automatically selects the appropriate dialect, representer, and parser through its Method Resolution Order (MRO).

pyDAL provides three ways to construct subqueries, with AST-native forms being recommended for better parameter handling.

Subquery Methods

1. AST-native (Recommended): db(query).subselect(field).
2. Legacy nested_select: db(query).nested_select(field). This can also be used as a join source by calling .with_alias(name) on the result.
3. Raw SQL (Inline only): db(query)._select(field).

Common Table Expressions (CTEs)

• Standard CTE: Created using set.cte(name, *fields).
• Recursive CTE: Use .union(lambda self: ...) to add the recursive step to a CTE.

Using a SELECT as a Join Source

You can use a nested_select as a table in a join by providing an alias.

# 1. Recommended AST-native subquery
sub = db(db.thing.color == "red").subselect(db.thing.owner_id)
db(db.person.id.belongs(sub)).select()

# 2. Using nested_select as a join source
sub = db(db.thing.color == "red").nested_select(
    db.thing.owner_id, db.thing.name
).with_alias("red_things")

db(db.person).select(
    db.person.name, sub.name,
    join=sub.on(sub.owner_id == db.person.id),
)

# 3. Standard CTE
recent = db(db.event.created > "2026-01-01").cte(
    "recent", db.event.id, db.event.user_id
)
db(db.user.id.belongs(recent.user_id)).select()

# 4. Recursive CTE
descendants = (
    db(db.org.id == root_id).cte(
        "descendants",
        db.org.id, db.org.name, db.org.parent_id,
    )
    .union(lambda descendants: 
        db(db.org.parent_id == descendants.id).nested_select(
            db.org.id, db.org.name, db.org.parent_id,
        )
    )
)

You can use pyDAL as a pure SQL generator without requiring a running database or specific drivers. This is useful for inspecting generated SQL or cross-dialect comparisons.

Process:

1. Connect to an in-memory SQLite database: DAL("sqlite:memory", migrate=False).
2. Define your tables and fields.
3. Swap the adapter's dialect to your target backend (e.g., PostgresDialect or MySQLDialect).
4. Disable parameterization if you want human-readable SQL with inline values: db._adapter.compiler.parameterize = False.
5. Use the underscore-prefixed methods to return SQL strings instead of executing them:
   • db(query)._select(...)
   • db(query)._delete()
   • db(query)._update(...)
   • db.table._insert(...)
   • db(query)._count()

from pydal import DAL, Field
from pydal.backends.postgres import PostgresDialect

# Scratch connection
db = DAL("sqlite:memory", migrate=False)

# Retarget to PostgreSQL and disable parameterization for inspection
db._adapter.dialect = PostgresDialect(db._adapter)
db._adapter.compiler.parameterize = False

db.define_table("person", Field("name"), Field("age", "integer"))

q = (db.person.age >= 18) & (db.person.name.like("A%"))

# Returns SQL string without execution
print(db(q)._select(db.person.id, db.person.name))
# SELECT "person"."id", "person"."name" FROM "person" 
# WHERE (("person"."age" >= 18) AND ("person"."name" LIKE 'A%' ESCAPE '\'));

print(db.person._insert(name="Alice", age=30))
# INSERT INTO "person"("name","age") VALUES ('Alice',30);