Home

Overview

david8 - the next generation Weyland Robot is lightweight Python SQL builder

• I initially started this project just for fun
• Feel free to open a bug report / feature request / question
• Dialects: DuckDB, ClickHouse, PostgreSQL
• ⚠️ Current state: still beta, but full (100%) test coverage. The API changes infrequently. Use the breaking change label to track backward-incompatible changes across future releases.

IDE-friendly

Comparison

SQL query from benchmarks. Syntax comparison: pypika, sqlalchemy, david8. 1.9.0b1 results:

Package	Size	Commits	Pull Requests	Coverage
david8
david8_clickhouse
david8_duckdb
david8_postgresql
pypika

Query example

from datetime import datetime, timedelta
from david8 import get_default_qb
from david8.expressions import val, desc
from david8.functions import count
from david8.predicates import lt, gt, eq, between

qb = get_default_qb()


class Users:
    @staticmethod
    def last_active():
        end = datetime.now()
        start = end - timedelta(days=10)
        return (
            qb
            .select('*')
            .from_table('users')
            .where(
                between('last_login_day', val(str(start.date())), val(str(end.date()))),
                eq('status', val('active')),
            )
        )

query = (qb
    .with_(
        ('group_a', Users.last_active().where(lt('age', 18), gt('height', 150))),
        ('group_b', Users.last_active().where(gt('age', 30), gt('height', 170))),
    )
    .select(
        count('*').as_('total_users'),
        'age',
        'gender',
        'last_login_day',
    )
    .from_expr(
        qb
        .select('age', 'gender', 'last_login_day')
        .from_table('group_a')
        .union(qb.select('age', 'gender', 'last_login_day').from_table('group_b'))
    )
    .group_by('age', 'gender', 'last_login_day')
    .order_by(desc('total_users', 'last_login_day'))
    .limit(10)
 )


print(query.get_sql())
# WITH group_a AS (
#     SELECT * FROM users
#      WHERE last_login_day BETWEEN '2025-12-07' AND '2025-12-10'
#        AND status = 'active'
#        AND age < %(p1)s
#        AND height > %(p2)s
# ),
# group_b AS (
#     SELECT * FROM users
#      WHERE last_login_day BETWEEN '2025-12-07' AND '2025-12-10'
#        AND status = 'active'
#        AND age > %(p3)s
#        AND height > %(p4)s
# )
# SELECT count(*) AS total_users,
#                    age,
#                    gender,
#                    last_login_day
#   FROM (
#       SELECT age,
#              gender,
#              last_login_day
#         FROM group_a
#        UNION ALL
#       SELECT age, gender, last_login_day
#         FROM group_b
#  )
#   GROUP BY age,
#            gender,
#            last_login_day
#   ORDER BY total_users DESC,
#            last_login_day DESC
#   LIMIT 10
print(query.get_parameters())
# {'p1': 18, 'p2': 150, 'p3': 30, 'p4': 170}

Links

• docs
• Examples
• Tests
• Milestones

Package Structure

├── core                 # core logic and dialects API
├── functions_pg_duck.py # Deprecated since 1.6.0b1, will be removed in 0.1.0. Use functions.py
# public API
├── cast_types.py        # common types: text, varchar
├── expressions.py       # common tiny sql expressions: desc(), val(), param(), interval()
├── frames.py            # window modes and frames: ROWS, RANGE, CURRENT ROW
├── functions.py         # common functions: lower(), avg(), rank()
├── joins.py             # common join types: left(), right()
├── logical_operators.py # or_(), and_(), xor()
├── param_styles.py      # QMark, Numeric, PyFormat etc
├── predicates.py        # eq(), ne(), between() etc
└── protocols