ZetCode

Python pyDAL

last modified November 17, 2026 Python 3.15

In this article we show how to work with databases in Python using the pyDAL database abstraction layer. Most examples use SQLite; at the end we run the same code against PostgreSQL.

pyDAL is a pure Python Database Abstraction Layer. It started as the database layer of the web2py framework and is also used by py4web, but it works as a standalone library. pyDAL builds SQL in the dialect of the configured back end at run time, so the same Python code works with SQLite, PostgreSQL, MySQL, MSSQL, Oracle, and other databases.

pyDAL is not an object-relational mapper. We do not write a class for each table; tables are defined with define_table, queries are built from Python expressions, and rows come back as dictionary-like Row objects. The API stays close to SQL.

The examples were tested with Python 3.15, pyDAL 20261107.0 (pyDAL uses dates as version numbers), SQLite 3.53, and PostgreSQL 18 on Linux.

Installing pyDAL

pyDAL is installed with pip. SQLite support comes from the sqlite3 module of the standard library; for PostgreSQL, pyDAL needs the psycopg2 driver.

$ pip install pydal psycopg2-binary

Creating a table

In the first example, we create a database table and fill it with data.

create_table.py
from pathlib import Path
from pydal import DAL, Field

Path('dbs').mkdir(exist_ok=True)

db = DAL('sqlite://test.db', folder='dbs')

db.define_table('cars', Field('name'), Field('price', 'integer'))

db.cars.insert(name='Audi', price=52642)
db.cars.insert(name='Mercedes', price=57127)
db.cars.insert(name='Skoda', price=9000)
db.cars.insert(name='Volvo', price=29000)
db.cars.insert(name='Bentley', price=350000)
db.cars.insert(name='Citroen', price=21000)
db.cars.insert(name='Hummer', price=41400)
db.cars.insert(name='Volkswagen', price=21600)

db.commit()
db.close()

The example creates a cars table with eight rows in an SQLite database.

Path('dbs').mkdir(exist_ok=True)

db = DAL('sqlite://test.db', folder='dbs')

DAL represents a database connection. Its first argument is a connection string, also called a URI. The folder parameter tells pyDAL where to store the database file and its metadata. pyDAL does not create the folder; if it does not exist, define_table fails with FileNotFoundError.

db.define_table('cars', Field('name'), Field('price', 'integer'))

A table is defined with define_table. If the table does not exist, pyDAL creates it. The name field has the default string type, the price field is an integer. pyDAL adds an auto-incremented id primary key to every table.

db.cars.insert(name='Audi', price=52642)

The defined table is available as an attribute of the db object. The insert method adds a new row and returns its id.

db.commit()
db.close()

pyDAL runs all statements in a transaction, which we commit with commit. The close method closes the connection; it also commits pending changes.

$ python create_table.py
$ ls dbs
c95cf9bab36fcb04c2424cdf9be0f6e3_cars.table  sql.log  test.db

The migration log

Besides the test.db database, the folder contains a file with the .table extension and the sql.log file. The .table file stores the table definition that pyDAL last applied. When the definition in the code changes, pyDAL compares it with the file and alters the table; this is called a migration. The prefix of the file name is a hash of the connection string. The log records the SQL that pyDAL executed to create or alter the tables.

$ cat dbs/sql.log
timestamp: 2026-11-17T22:25:46.962779
CREATE TABLE "cars"(
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" CHAR(512),
    "price" INTEGER
);
success!

Defining tables in a module

A pyDAL program must define its tables every time it runs; the define_table calls tell pyDAL how the tables look, and the database itself is only changed when the definition differs from the stored one. Most applications therefore put the definitions in a separate module.

models.py
from pathlib import Path
from pydal import DAL, Field

Path('dbs').mkdir(exist_ok=True)

db = DAL('sqlite://test.db', folder='dbs')

db.define_table('cars', Field('name'), Field('price', 'integer'))

The models module connects to the database and defines the cars table. The following examples import the db object from it.

select_all.py
from models import db

rows = db().select(db.cars.ALL)

for row in rows:
    print(row.id, row.name, row.price)

The example retrieves all rows from the cars table.

rows = db().select(db.cars.ALL)

Calling db creates a set of records; with no argument, the set is not limited by any condition. The select method runs the query. db.cars.ALL selects all columns of the table.

for row in rows:
    print(row.id, row.name, row.price)

Field values are available as attributes of the row.

$ python select_all.py
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

The Rows object

The select method returns a Rows object.

rows_object.py
from models import db

rows = db(db.cars).select()

print(type(rows))
print(len(rows))
print(rows.first())
print(rows.last())
print(rows[2].name)
print(rows[2]['price'])

print(rows.as_list()[:2])

db(db.cars) is a shorter way to select all rows of a table; without field arguments, select returns all columns. Rows supports len and indexing. The first and last methods return the first and the last row, or None for an empty result. A Row is a dictionary with attribute access, so rows[2].name and rows[2]['price'] both work. The as_list method turns the rows into a list of plain dictionaries.

$ python rows_object.py
<class 'pydal.objects.Rows'>
8
<Row {'id': 1, 'name': 'Audi', 'price': 52642}>
<Row {'id': 8, 'name': 'Volkswagen', 'price': 21600}>
Skoda
9000
[{'id': 1, 'name': 'Audi', 'price': 52642}, {'id': 2, 'name': 'Mercedes', 'price': 57127}]

Rows as CSV

When we print a Rows object, we get its data in CSV format.

rows_csv.py
from models import db

rows = db(db.cars).select()
print(rows)

The header contains the table-qualified column names.

$ python rows_csv.py
cars.id,cars.name,cars.price
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600

Fetching a single row

A table object can be indexed by id or called with a condition to fetch a single row.

single_row.py
from models import db

print(db.cars[3])
print(db.cars(name='Volvo'))
print(db.cars(db.cars.price > 100000))
print(db.cars[100])
print(db.cars(name='Tesla'))

db.cars[3] returns the row with id 3. Calling the table with keyword arguments or a query returns the first matching row. In both cases, we get None if no row matches.

$ python single_row.py
<Row {'id': 3, 'name': 'Skoda', 'price': 9000}>
<Row {'id': 4, 'name': 'Volvo', 'price': 29000}>
<Row {'id': 5, 'name': 'Bentley', 'price': 350000}>
None
None

Selecting columns

To retrieve only some columns, we pass the fields to select.

select_columns.py
from models import db

rows = db(db.cars).select(db.cars.name, db.cars.price)

for row in rows:
    print(f'{row.name:<12} {row.price:>7}')

print(rows.first())

The generated SQL contains only the name and price columns. The rows do not have the id field.

$ python select_columns.py
Audi           52642
Mercedes       57127
Skoda           9000
Volvo          29000
Bentley       350000
Citroen        21000
Hummer         41400
Volkswagen     21600
<Row {'name': 'Audi', 'price': 52642}>

Where conditions

A condition is created by comparing a field with a value. The condition is passed to db and becomes the WHERE clause of the query.

where.py
from models import db

cars = db.cars

rows = db(cars.price > 40000).select()
print([r.name for r in rows])

rows = db((cars.price > 20000) & (cars.price < 30000)).select()
print([r.name for r in rows])

rows = db((cars.name == 'Skoda') | (cars.name == 'Volvo')).select()
print([r.name for r in rows])

rows = db(~(cars.price > 40000)).select()
print([r.name for r in rows])

Conditions are combined with the & (AND), | (OR), and ~ (NOT) operators. Python does not allow and and or to be overloaded, so pyDAL uses the bitwise operators. They bind more tightly than comparisons, so each comparison must be put in parentheses.

$ python where.py
['Audi', 'Mercedes', 'Bentley', 'Hummer']
['Volvo', 'Citroen', 'Volkswagen']
['Skoda', 'Volvo']
['Skoda', 'Volvo', 'Citroen', 'Volkswagen']

Queries and sets

A comparison of a field with a value creates a Query object. Passing it to db creates a Set, a description of a group of records. No SQL runs until we call a method such as select, count, update, or delete on the set.

query_set.py
from models import db

query = db.cars.price > 40000
print(type(query))
print(query)

expensive = db(query)
print(type(expensive))

print(expensive._select(db.cars.name))
print(expensive._count())
print(expensive._delete())

The methods with a leading underscore, such as _select, _count, and _delete, return the SQL that the method would execute without running it. This is useful for debugging. _select shows the values as SQL literals, while the other methods show the placeholders of the driver.

$ python query_set.py
<class 'pydal.objects.Query'>
("cars"."price" > 40000)
<class 'pydal.objects.Set'>
SELECT "cars"."name" FROM "cars" WHERE ("cars"."price" > 40000);
SELECT COUNT(*) FROM "cars" WHERE ("cars"."price" > ?);
DELETE FROM "cars" WHERE ("cars"."price" > ?);

String conditions

Fields have methods for pattern matching and membership tests.

string_ops.py
from models import db

cars = db.cars

def names(query):
    return [r.name for r in db(query).select(cars.name)]

print(names(cars.name.startswith('V')))
print(names(cars.name.endswith('n')))
print(names(cars.name.contains('ER')))
print(names(cars.name.like('%e%e%')))
print(names(cars.name.belongs(['Audi', 'Skoda', 'Tesla'])))
print(names(cars.name.upper() == 'AUDI'))

startswith, endswith, and like translate to SQL LIKE. contains is case-insensitive; it compares lowercased values, so 'ER' matches Mercedes and Hummer. belongs creates an IN clause. Expressions such as upper can be used on both sides of a comparison.

$ python string_ops.py
['Volvo', 'Volkswagen']
['Citroen', 'Volkswagen']
['Mercedes', 'Hummer']
['Mercedes', 'Bentley']
['Audi', 'Skoda']
['Audi']

Ordering rows

The orderby parameter of select sorts the rows.

order_by.py
from models import db

cars = db.cars

for row in db(cars).select(orderby=cars.price):
    print(row.name, row.price)

print('----------')

for row in db(cars).select(orderby=~cars.price, limitby=(0, 3)):
    print(row.name, row.price)

print('----------')

for row in db(cars).select(orderby=cars.name.len() | cars.name, limitby=(0, 3)):
    print(row.name)

The example orders the cars by price in ascending order and then shows the three most expensive cars. The ~ operator reverses the order (DESC). Multiple sort keys are joined with |; the last query orders by the length of the name and then by the name.

$ python order_by.py
Skoda 9000
Citroen 21000
Volkswagen 21600
Volvo 29000
Hummer 41400
Audi 52642
Mercedes 57127
Bentley 350000
----------
Bentley 350000
Mercedes 57127
Audi 52642
----------
Audi
Skoda
Volvo

Limiting rows

The limitby parameter takes a tuple with the start and end index of the rows to return.

limit_by.py
from models import db

rows = db(db.cars).select(orderby=db.cars.id, limitby=(2, 5))

for row in rows:
    print(row.id, row.name, row.price)

print(db._lastsql[0])

limitby=(2, 5) skips two rows and returns the next three; the second number is an end index, not a count. pyDAL translates it to LIMIT and OFFSET or the equivalent clause of the database. The _lastsql attribute holds the last executed statement and its duration.

$ python limit_by.py
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
SELECT "cars"."id", "cars"."name", "cars"."price" FROM "cars" WHERE ("cars"."id" IS NOT NULL) ORDER BY "cars"."id" LIMIT 3 OFFSET 2;

Counting rows

The count method of a set returns the number of matching rows.

count_rows.py
from models import db

n = db(db.cars).count()
print(f'There are {n} cars in the table')

n = db(db.cars.price > 50000).count()
print(f'{n} cars cost more than 50000')

print(db(db.cars.price > 500000).isempty())

The isempty method checks whether the set contains any rows.

$ python count_rows.py
There are 8 cars in the table
3 cars cost more than 50000
True

Aggregate functions

Fields have sum, min, max, avg, and count methods, which create aggregate expressions.

aggregates.py
from models import db

price = db.cars.price

total = price.sum()
cheapest = price.min()
dearest = price.max()
average = price.avg()
exact_average = price.avg().cast('double')

row = db(db.cars).select(total, cheapest, dearest, average, exact_average).first()

print('sum:', row[total])
print('min:', row[cheapest])
print('max:', row[dearest])
print('avg:', row[average])
print('avg:', row[exact_average])

The expressions are passed to select and their values are read from the row by using the expression as a key. pyDAL converts the result of an expression to the type of the field. The average of the integer price field is therefore truncated to an integer. To get the exact value, we cast the expression to double.

$ python aggregates.py
sum: 581769
min: 9000
max: 350000
avg: 72721
avg: 72721.125

Inserting rows

Single rows are added with insert, multiple rows with bulk_insert.

insert_rows.py
from models import db

car_id = db.cars.insert(name='Tesla', price=45000)
print(car_id)

ids = db.cars.bulk_insert([
    {'name': 'Ford', 'price': 27300},
    {'name': 'Toyota', 'price': 31500},
])
print(ids)

db.commit()

print(db(db.cars).count())

insert returns the id of the new row. bulk_insert takes a list of dictionaries and returns a list of ids.

$ python insert_rows.py
9
[10, 11]
11

Updating rows

The update method of a set changes all rows in the set.

update_rows.py
from models import db

cars = db.cars

n = db(cars.name == 'Tesla').update(price=43900)
print(n, 'row updated')

n = db(cars.price < 30000).update(price=cars.price + 1000)
print(n, 'rows updated')

db.commit()

for row in db(cars.price < 32000).select(orderby=cars.price):
    print(row.name, row.price)

update returns the number of changed rows. The new value can be an expression; cars.price + 1000 is evaluated by the database for each row, as SET price = price + 1000.

$ python update_rows.py
1 row updated
5 rows updated
Skoda 10000
Citroen 22000
Volkswagen 22600
Ford 28300
Volvo 30000
Toyota 31500

Updating a record

A fetched row can update itself with update_record.

update_record.py
from models import db

car = db.cars(name='Ford')
print(car)

car.update_record(price=26500)
print(car)

car.update(price=1)
print(car)
print(db.cars(name='Ford'))

db.commit()

update_record writes the change to the database and updates the row object. The update method of a Row is the ordinary dictionary method; it changes only the object in memory, as the last line shows.

$ python update_record.py
<Row {'id': 10, 'name': 'Ford', 'price': 28300}>
<Row {'id': 10, 'name': 'Ford', 'price': 26500}>
<Row {'id': 10, 'name': 'Ford', 'price': 1}>
<Row {'id': 10, 'name': 'Ford', 'price': 26500}>

Deleting rows

Rows are deleted with the delete method of a set or with delete_record of a row.

delete_rows.py
from models import db

n = db(db.cars.name.belongs(['Ford', 'Toyota'])).delete()
print(n, 'rows deleted')

car = db.cars(name='Tesla')
car.delete_record()

db.commit()

print(db(db.cars).count())

The example deletes Ford and Toyota with a single DELETE statement and then deletes the Tesla row.

$ python delete_rows.py
2 rows deleted
8

Update or insert

The update_or_insert method updates the rows that match a query or inserts a new row if no row matches.

update_or_insert.py
from models import db

cars = db.cars

print(cars.update_or_insert(cars.name == 'Skoda', name='Skoda', price=9900))
print(cars.update_or_insert(cars.name == 'Lada', name='Lada', price=8000))

db.commit()

for row in db(cars.price < 10000).select():
    print(row)

Skoda exists, so its price is updated and the method returns None. Lada does not exist, so it is inserted and the method returns the new id.

$ python update_or_insert.py
None
12
<Row {'id': 3, 'name': 'Skoda', 'price': 9900}>
<Row {'id': 12, 'name': 'Lada', 'price': 8000}>

Uncommitted changes

pyDAL starts a transaction with the first statement and does not commit it automatically.

no_commit.py
from models import db

db.cars.insert(name='Trabant', price=500)
print(db(db.cars).count())

The example inserts a row but neither calls commit nor close. Inside the program, the count includes the new row. When the program ends, the connection is closed without a commit and the change is lost; the count_only.py program, which only prints the count, sees nine rows.

$ python no_commit.py
10
$ python count_only.py
9

Rollback

The rollback method discards all changes made since the last commit.

rollback.py
from models import db

cars = db.cars

def transfer(old, new, price):
    try:
        cars.insert(name=new, price=price)
        if cars(name=old) is None:
            raise LookupError(f'no car named {old}')
        db(cars.name == old).delete()
    except Exception as e:
        db.rollback()
        print('rolled back:', e)
    else:
        db.commit()
        print('committed')

transfer('Lada', 'Dacia', 15000)
transfer('Trabant', 'Wartburg', 900)

print([r.name for r in db(cars.price < 20000).select(orderby=cars.price)])

The transfer function replaces one car with another. It inserts the new car first and then deletes the old one. If the old car does not exist, the function raises an exception and the insert is rolled back. The two statements are applied together or not at all.

$ python rollback.py
committed
rolled back: no car named Trabant
['Skoda', 'Dacia']

Single transaction

The single_transaction context manager commits on success and rolls back on an exception. It also closes the connection at the end.

single_transaction.py
from models import db

with db.single_transaction():
    db.cars.insert(name='Wartburg', price=900)
    raise ValueError('something went wrong')

print('no exception reached this line')
print(db.cars(name='Wartburg'))

The insert is rolled back, but the exception does not propagate: single_transaction catches it and the program continues as if nothing happened. If the caller needs to know about the failure, explicit commit and rollback calls in a try/except block, as in the previous example, are a better choice.

$ python single_transaction.py
no exception reached this line
None

Validators

Fields can have validators in the requires attribute. The validators are run by validate_and_insert and validate_and_update.

validators.py
from models import db
from pydal import Field
from pydal.validators import IS_EMAIL, IS_INT_IN_RANGE, IS_NOT_EMPTY, IS_NOT_IN_DB

db.define_table(
    'users',
    Field('name', requires=IS_NOT_EMPTY()),
    Field('email'),
    Field('age', 'integer', requires=IS_INT_IN_RANGE(18, 120)),
)
db.users.email.requires = [IS_EMAIL(), IS_NOT_IN_DB(db, db.users.email)]

print(db.users.validate_and_insert(name='Jan', email='jan@example.com', age=42))
print(db.users.validate_and_insert(name='', email='peter', age=12))
print(db.users.validate_and_insert(name='Paul', email='jan@example.com', age=30))

db.users.insert(name='', email='peter', age=12)
print(db(db.users).count())

db.rollback()

validate_and_insert returns a dictionary with the new id, the errors, and a success flag. IS_NOT_IN_DB checks the database, so Paul is refused because Jan already uses the address. The plain insert method does not run the validators; it inserted the invalid row. The final rollback discards both rows and the new table is left empty.

$ python validators.py
{'id': 1, 'errors': {}, 'success': True}
{'id': None, 'errors': {'name': 'Enter a value', 'email': 'Enter a valid email address', 'age': 'Enter an integer between 18 and 119'}, 'success': False}
{'id': None, 'errors': {'email': 'Value already in database or empty'}, 'success': False}
2

Constraints

The unique and notnull attributes of a field become constraints in the database.

constraints.py
import sqlite3
from models import db
from pydal import Field

db.define_table(
    'products',
    Field('code', unique=True, notnull=True),
    Field('title', notnull=True),
    Field('stock', 'integer', default=0),
)

db.products.insert(code='A100', title='Keyboard')
print(db.products(code='A100'))

try:
    db.products.insert(code='A100', title='Mouse')
except sqlite3.IntegrityError as e:
    print('error:', e)

try:
    db.products.insert(code='A200')
except sqlite3.IntegrityError as e:
    print('error:', e)

db.commit()

Unlike validators, constraints are checked by the database. A violation raises the exception of the database driver; for SQLite it is sqlite3.IntegrityError. The default value is used when the field is not given in the insert.

$ python constraints.py
<Row {'id': 1, 'code': 'A100', 'title': 'Keyboard', 'stock': 0}>
error: UNIQUE constraint failed: products.code
error: NOT NULL constraint failed: products.title

Field types

pyDAL converts values between Python types and database columns.

field_types.py
import datetime
from models import db
from pydal import Field

db.define_table(
    'tasks',
    Field('title', notnull=True),
    Field('done', 'boolean', default=False),
    Field('due', 'date'),
    Field('created', 'datetime', default=datetime.datetime(2026, 11, 17, 9, 30)),
    Field('tags', 'list:string'),
    Field('meta', 'json'),
    Field('slug', compute=lambda r: r['title'].lower().replace(' ', '-')),
)

db.tasks.insert(title='Write report', due=datetime.date(2026, 12, 1),
                tags=['work', 'urgent'], meta={'priority': 1, 'hours': 2.5})
db.tasks.insert(title='Buy milk', tags=['home'], done=True)
db.commit()

task = db.tasks(title='Write report')

for name in db.tasks.fields:
    print(f'{name:8} {task[name]!r}')

rows = db(db.tasks.tags.contains('home')).select(db.tasks.title)
print([r.title for r in rows])

date and datetime fields return datetime objects and boolean fields bool values. A list:string field stores a list in a single column, as the text |work|urgent|, and its contains method searches the list. A json field stores any JSON-serializable value. The default can be a value or a callable such as datetime.datetime.now. A compute function sets the field from the other values on insert and update.

$ python field_types.py
id       1
title    'Write report'
done     False
due      datetime.date(2026, 12, 1)
created  datetime.datetime(2026, 11, 17, 9, 30)
tags     ['work', 'urgent']
meta     {'priority': 1, 'hours': 2.5}
slug     'write-report'
['Buy milk']

Migrations

When we change a table definition, pyDAL alters the table to match it.

migration.py
from models import db
from pydal import Field

db.define_table('cars', Field('name'), Field('price', 'integer'),
                Field('country', default='unknown'), redefine=True)

print(db.cars.fields)
print(db.cars(name='Audi'))

db.cars.insert(name='Tatra', price=110000, country='Czechia')
print(db.cars(name='Tatra'))

db.rollback()

We add a country field to the cars table. The redefine=True argument allows us to define the table again after models has defined it. pyDAL compares the new definition with the .table file and runs ALTER TABLE. Existing rows get NULL in the new column; the default is only used for new inserts. The schema change is written immediately, so the rollback discards only the inserted row. Migrations can be turned off with migrate=False for a table or for the whole connection, for example when the schema is managed by a database administrator.

$ python migration.py
['id', 'name', 'price', 'country']
<Row {'id': 1, 'name': 'Audi', 'price': 52642, 'country': None}>
<Row {'id': 14, 'name': 'Tatra', 'price': 110000, 'country': 'Czechia'}>
$ tail -n 3 dbs/sql.log
timestamp: 2026-11-17T22:25:53.159595
ALTER TABLE "cars" ADD "country" CHAR(512);
success!

Reference fields

A field of type reference <table> is a foreign key to another table. In this and the following examples, we work with a small library database with authors and their books.

library.py
from pathlib import Path
from pydal import DAL, Field

Path('dbs').mkdir(exist_ok=True)

db = DAL('sqlite://library.db', folder='dbs')

db.define_table('authors', Field('name'), format='%(name)s')
db.define_table('books', Field('title'), Field('year', 'integer'),
                Field('author', 'reference authors'))

The library module defines the authors and books tables. Each book references its author. The format argument tells pyDAL how to display a referenced author, for example in forms and in the represent function of the reference field.

library_data.py
from library import db

austen = db.authors.insert(name='Jane Austen')
tolstoy = db.authors.insert(name='Leo Tolstoy')
kafka = db.authors.insert(name='Franz Kafka')
db.authors.insert(name='Emily Bronte')

db.books.bulk_insert([
    {'title': 'Pride and Prejudice', 'year': 1813, 'author': austen},
    {'title': 'Emma', 'year': 1815, 'author': austen},
    {'title': 'Persuasion', 'year': 1817, 'author': austen},
    {'title': 'War and Peace', 'year': 1869, 'author': tolstoy},
    {'title': 'Anna Karenina', 'year': 1878, 'author': tolstoy},
    {'title': 'The Trial', 'year': 1925, 'author': kafka},
])

db.commit()

print(db(db.authors).count(), 'authors,', db(db.books).count(), 'books')

The ids returned by insert are used as the values of the author reference. Emily Bronte has no books.

$ python library_data.py
4 authors, 6 books

Inner join

A query that compares fields of two tables joins the tables.

inner_join.py
from library import db

books, authors = db.books, db.authors

rows = db(books.author == authors.id).select(
    books.title, authors.name, orderby=books.year)

for row in rows:
    print(f'{row.books.title:<20} {row.authors.name}')

rows = db(books).select(books.title, authors.name,
                        join=authors.on(books.author == authors.id),
                        orderby=books.year, limitby=(0, 2))
print(rows.first())
print(db._lastsql[0])

When the selected fields come from several tables, each row has one attribute per table: row.books.title and row.authors.name. The first query is an implicit join; both tables appear in the FROM clause and the condition in the WHERE clause. The second query uses the join parameter and on, which produces an INNER JOIN clause.

$ python inner_join.py
Pride and Prejudice  Jane Austen
Emma                 Jane Austen
Persuasion           Jane Austen
War and Peace        Leo Tolstoy
Anna Karenina        Leo Tolstoy
The Trial            Franz Kafka
<Row {'books': {'title': 'Pride and Prejudice'}, 'authors': {'name': 'Jane Austen'}}>
SELECT "books"."title", "authors"."name" FROM "books" JOIN "authors" ON ("books"."author" = "authors"."id") WHERE ("books"."id" IS NOT NULL) ORDER BY "books"."year" LIMIT 2 OFFSET 0;

Left join

The left parameter creates a LEFT OUTER JOIN.

left_join.py
from library import db

books, authors = db.books, db.authors

rows = db(authors).select(authors.name, books.title,
                          left=books.on(books.author == authors.id),
                          orderby=authors.name | books.year)

for row in rows:
    print(f'{row.authors.name:<12} {row.books.title}')

A left join returns all authors, including those without books. Emily Bronte's book title is None.

$ python left_join.py
Emily Bronte None
Franz Kafka  The Trial
Jane Austen  Pride and Prejudice
Jane Austen  Emma
Jane Austen  Persuasion
Leo Tolstoy  War and Peace
Leo Tolstoy  Anna Karenina

Grouping rows

The groupby and having parameters correspond to the SQL clauses with the same names.

group_by.py
from library import db

books, authors = db.books, db.authors
n_books = books.id.count()

rows = db(authors).select(authors.name, n_books,
                          left=books.on(books.author == authors.id),
                          groupby=authors.id, orderby=~n_books | authors.name)

for row in rows:
    print(row.authors.name, row[n_books])

print('----------')

rows = db(books.author == authors.id).select(
    authors.name, n_books, groupby=authors.id, having=n_books > 1)

for row in rows:
    print(row.authors.name, row[n_books])

The first query counts the books of each author, with the most productive author first. With a left join, Emily Bronte appears with zero books. The second query keeps only authors with more than one book.

$ python group_by.py
Jane Austen 3
Leo Tolstoy 2
Franz Kafka 1
Emily Bronte 0
----------
Jane Austen 3
Leo Tolstoy 2

Following references

The value of a reference field can be used to load the referenced row, and a row can select the rows that reference it.

references.py
from library import db

book = db.books(title='Emma')

print(book.author)
print(type(book.author))
print(book.author.name)

austen = db.authors(name='Jane Austen')

for b in austen.books.select(orderby=db.books.year):
    print(b.year, b.title)

print(db.books.author.represent(book.author, book))

The value of book.author is a Reference, an integer subclass. Accessing its attributes loads the author with another query. In the other direction, austen.books is a set of the books whose author field references the row. The represent function uses the format of the authors table.

$ python references.py
1
<class 'pydal.helpers.classes.Reference'>
Jane Austen
1813 Pride and Prejudice
1815 Emma
1817 Persuasion
Jane Austen

Cascading deletes

Reference fields have the ondelete attribute, which defaults to CASCADE.

cascade.py
from library import db

print(db(db.books).count())

db(db.authors.name == 'Leo Tolstoy').delete()

print(db(db.books).count())

db.rollback()

print(db(db.books).count())

Deleting Leo Tolstoy also deletes his two books. To refuse the deletion of an author who still has books, define the field with ondelete='RESTRICT'; use ondelete='SET NULL' to keep the books without an author.

$ python cascade.py
6
4
6

Callbacks

Tables have lists of callbacks that run before and after inserts, updates, and deletes.

callbacks.py
from models import db

def before_insert(fields):
    print('before insert:', fields)
    fields['name'] = fields['name'].strip().title()

def after_insert(fields, new_id):
    print('after insert:', new_id, fields)

def before_update(dbset, fields):
    print('before update:', dbset, fields)

db.cars._before_insert.append(before_insert)
db.cars._after_insert.append(after_insert)
db.cars._before_update.append(before_update)

car_id = db.cars.insert(name='  alfa romeo ', price=38000)
db(db.cars.id == car_id).update(price=37500)

print(db.cars[car_id])

db.rollback()

The _before_insert callbacks receive the fields to be inserted and can modify them; here the name is cleaned up. The _after_insert callbacks also get the new id. The update callbacks receive the set being updated and the new values. A callback that returns True cancels the operation.

$ python callbacks.py
before insert: <OpRow {'name': '  alfa romeo ', 'price': 38000}>
after insert: 14 <OpRow {'name': 'Alfa Romeo', 'price': 38000}>
before update: <Set ("cars"."id" = 14)> <OpRow {'price': 37500}>
<Row {'id': 14, 'name': 'Alfa Romeo', 'price': 37500}>

Raw SQL

The executesql method executes an SQL statement directly.

raw_sql.py
from models import db

rows = db.executesql('SELECT name, price FROM cars WHERE price > ?',
                     placeholders=(50000,))
print(rows)

rows = db.executesql('SELECT name, price FROM cars WHERE price > ?',
                     placeholders=(50000,), as_dict=True)
print(rows)

rows = db.executesql('SELECT name, price FROM cars WHERE id = :id',
                     placeholders={'id': 6}, fields=[db.cars.name, db.cars.price])
print(type(rows))
print(rows.first().name)

Values should be passed in the placeholders argument, never formatted into the SQL string. The placeholder style is that of the driver; sqlite3 accepts ? and :name. By default, executesql returns a list of tuples; as_dict=True returns dictionaries, and the fields argument turns the result into a Rows object.

$ python raw_sql.py
[('Audi', 52642), ('Mercedes', 57127), ('Bentley', 350000)]
[{'name': 'Audi', 'price': 52642}, {'name': 'Mercedes', 'price': 57127}, {'name': 'Bentley', 'price': 350000}]
<class 'pydal.objects.Rows'>
Citroen

The last SQL statement

The _lastsql attribute shows the last statement that pyDAL executed.

last_sql.py
from models import db

db(db.cars.price > 50000).select(db.cars.name, orderby=db.cars.name)

sql, duration = db._lastsql
print(sql)
print(type(duration))

print(db(db.cars.name == "O'Brien")._select(db.cars.id))

_lastsql is a tuple of the SQL text and the time it took in seconds. The text generated by _select contains the values quoted as SQL literals; this is for display only. When pyDAL runs a statement, it passes the values as parameters.

$ python last_sql.py
SELECT "cars"."name" FROM "cars" WHERE ("cars"."price" > ?) ORDER BY "cars"."name";
<class 'float'>
SELECT "cars"."id" FROM "cars" WHERE ("cars"."name" = 'O''Brien');

JSON output

The as_json method serializes the rows to JSON.

json_output.py
import json
from models import db

rows = db(db.cars.price > 40000).select(db.cars.name, db.cars.price,
                                        orderby=db.cars.price)

print(rows.as_json())

data = json.loads(rows.as_json())
print(data[0]['name'])

The result is a JSON array of objects, which the json module can read back.

$ python json_output.py
[{"name": "Hummer", "price": 41400}, {"name": "Audi", "price": 52642}, {"name": "Mercedes", "price": 57127}, {"name": "Bentley", "price": 350000}]
Hummer

Exporting to CSV

The export_to_csv_file method of Rows writes the rows to a CSV file.

csv_export.py
from models import db

with open('cars.csv', 'w', newline='') as f:
    db(db.cars).select(orderby=db.cars.id).export_to_csv_file(f)

The file has the same format as the printed Rows object.

$ python csv_export.py
$ cat cars.csv
cars.id,cars.name,cars.price
1,Audi,52642
2,Mercedes,57127
3,Skoda,9900
4,Volvo,30000
5,Bentley,350000
6,Citroen,22000
7,Hummer,41400
8,Volkswagen,22600
13,Dacia,15000

Importing from CSV

The import_from_csv_file method of a table reads such a file back.

csv_import.py
from pathlib import Path
from pydal import DAL, Field

Path('dbs').mkdir(exist_ok=True)

db = DAL('sqlite://copy.db', folder='dbs')
db.define_table('cars', Field('name'), Field('price', 'integer'))

with open('cars.csv', newline='') as f:
    db.cars.import_from_csv_file(f)

db.commit()

print(db(db.cars).count())
print(db.cars(name='Dacia'))

We copy the cars into a table in another database. The method matches the columns by name and inserts the rows; the ids in the file are not used, so the new rows get new ids. The whole database can be exported and imported with the export_to_csv_file and import_from_csv_file methods of db.

$ python csv_import.py
9
<Row {'id': 9, 'name': 'Dacia', 'price': 15000}>

Iterating over large results

The select method loads all rows into memory. For large results, iterselect returns the rows one by one.

iterselect.py
from models import db

rows = db(db.cars).iterselect(db.cars.name, orderby=db.cars.name)
print(type(rows))

for row in rows:
    print(row.name, end=' ')

print()

iterselect returns an IterRows object, which creates each Row as the loop asks for it.

$ python iterselect.py
<class 'pydal.objects.IterRows'>
Audi Bentley Citroen Dacia Hummer Mercedes Skoda Volkswagen Volvo

Running on PostgreSQL

The main benefit of pyDAL is that the code does not depend on the database. In the last example, we read the connection string from the DATABASE_URL environment variable and run the same program on SQLite and on PostgreSQL.

portable.py
import os
from pathlib import Path
from pydal import DAL, Field

Path('dbs').mkdir(exist_ok=True)

uri = os.environ.get('DATABASE_URL', 'sqlite://portable.db')
db = DAL(uri, folder='dbs')

db.define_table('cars', Field('name'), Field('price', 'integer'))

if db(db.cars).isempty():
    db.cars.bulk_insert([
        {'name': 'Audi', 'price': 52642},
        {'name': 'Skoda', 'price': 9000},
        {'name': 'Volvo', 'price': 29000},
    ])
    db.commit()

print(db._adapter.dbengine, db._adapter.driver_name)

for row in db(db.cars.price > 10000).select(orderby=~db.cars.price):
    print(row.name, row.price)

print(db._lastsql[0])

The program creates the table, inserts three rows if the table is empty, and runs a query. A PostgreSQL connection string has the form postgres://user:password@host:port/database; the port defaults to 5432. pyDAL ignores the PGPORT environment variable, so a non-default port must be given in the string.

For the test, we start a PostgreSQL server in a Docker container. In the output, the same query produces SQL with a different placeholder style: ? for SQLite, %s for psycopg2. pyDAL created the table with PostgreSQL types: the id is filled from a sequence and the name is character varying(512).

$ docker run -d --name pg -e POSTGRES_PASSWORD=s3cret -e POSTGRES_DB=testdb -p 5432:5432 postgres:18
$ python portable.py
sqlite sqlite3
Audi 52642
Volvo 29000
SELECT "cars"."id", "cars"."name", "cars"."price" FROM "cars" WHERE ("cars"."price" > ?) ORDER BY "cars"."price" DESC;
$ DATABASE_URL=postgres://postgres:s3cret@localhost/testdb python portable.py
postgres psycopg2
Audi 52642
Volvo 29000
SELECT "cars"."id", "cars"."name", "cars"."price" FROM "cars" WHERE ("cars"."price" > %s) ORDER BY "cars"."price" DESC;
$ docker exec pg psql -U postgres testdb -c '\d cars'
                                    Table "public.cars"
 Column |          Type          | Collation | Nullable |             Default
--------+------------------------+-----------+----------+----------------------------------
 id     | integer                |           | not null | nextval('cars_id_seq'::regclass)
 name   | character varying(512) |           |          |
 price  | integer                |           |          |
Indexes:
    "cars_pkey" PRIMARY KEY, btree (id)

Source

py4web – The database abstraction layer

pyDAL on GitHub

pyDAL API documentation

In this article we have worked with SQLite and PostgreSQL databases in Python using pyDAL. See also Python SQLite, Python PostgreSQL, Python Peewee, and Python PyMongo.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Python tutorials.