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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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
List all Python tutorials.