Skip to content

Quick Start

Jump to bottom
Danila Ganchar edited this page Jul 13, 2026 · 45 revisions

See docs

Examples

ClickHouse

$ docker run -d --name david8_clickhouse -p 9000:9000 -e CLICKHOUSE_USER=david8 -e CLICKHOUSE_PASSWORD=david8 -d clickhouse/clickhouse-server:25.9.5.21
$ pip install clickhouse-driver==0.2.10

💡 See david8_clickhouse

from david8 import get_default_qb
from david8.functions import count
from david8.predicates import ne
from clickhouse_driver import Client

client = Client('localhost', user='david8', password='david8',)
query = (
    get_default_qb()
    .select(
        'database',
        count('').as_('tables'),
    )
    .from_table('tables', db_name='system')
    .where(ne('database', 'INFORMATION_SCHEMA'))
    .group_by('database')
)

print(client.execute(query.get_sql(), query.get_parameters()))
# [INFO]: SELECT database, count() AS tables FROM system.tables
# WHERE database != %(p1)s GROUP BY database
# {'p1': 'INFORMATION_SCHEMA'}
# [('system', 113), ('information_schema', 20)]

PostgreSQL

docker run --name david8_postgres -e POSTGRES_PASSWORD=david8 -e POSTGRES_DB=david8 -e POSTGRES_USER=david8 -p 5432:5432 -d postgres:16

💡 See david8_postgresql

psycopg2 example(pip install psycopg2==2.9.11):

import psycopg2
from david8 import get_default_qb
from david8.functions import count
from david8.predicates import ne

connection = psycopg2.connect(user='david8', password='david8', host='127.0.0.1', port='5432', database='david8')
query = (
    get_default_qb().
    select(
        'relkind',
        count('*').as_('obj_counter'),
    )
    .from_table('pg_class', db_name='pg_catalog')
    .where(ne('relkind', 't'))
    .group_by('relkind')
    .order_by('relkind')
)

cursor = connection.cursor()
cursor.execute(query.get_sql(), query.get_parameters())
print(cursor.fetchall())

psycopg3 example(pip install psycopg==3.2.12):

import psycopg
from david8 import get_default_qb
from david8.param_styles import FormatParamStyle
from david8.functions import count
from david8.predicates import ne
conn = psycopg.connect('dbname=david8 user=david8 password=david8 host=127.0.0.1 port=5432')
conn.autocommit = True

query = (
    get_default_qb(FormatParamStyle())
    .select(
        'relkind',
        count('*').as_('obj_counter'),
    )
    .from_table('pg_class', db_name='pg_catalog')
    .where(ne('relkind', 't'))
    .group_by('relkind')
    .order_by('relkind')
)

with conn.cursor() as cursor:
    cursor.execute(query.get_sql(), query.get_tuple_parameters())
    print(cursor.fetchall())
# SELECT relkind, count(*) AS obj_counter FROM pg_catalog.pg_class
# WHERE relkind != %s GROUP BY relkind ORDER BY relkind
# [('i', 164), ('r', 68), ('v', 141)]

asyncpg example(pip install asyncio==4.0.0 asyncpg==0.31.0):

from david8.param_styles import NumericParamStyle
from david8 import get_default_qb
from david8.predicates import ne
from david8.functions import count
import asyncio
import asyncpg

async def run():
    conn = await asyncpg.connect(user='david8', password='david8', database='david8', host='127.0.0.1')
    query = (
        get_default_qb(NumericParamStyle())
        .select(
            'relkind',
            count('*').as_('obj_counter'),
        )
        .from_table('pg_class', db_name='pg_catalog')
        .where(ne('relkind', "t".encode("utf-8")))
        .group_by('relkind')
        .order_by('relkind')
    )

    values = await conn.fetchrow(
        query.get_sql(),
        *query.get_tuple_parameters(),
    )
    print(values)

asyncio.run(run())
# [INFO]: SELECT relkind, count(*) AS obj_counter FROM pg_catalog.pg_class WHERE relkind != $1 GROUP BY relkind ORDER BY relkind
# {'1': b't'}
# <Record relkind=b'i' obj_counter=164>

DuckDB

💡 See david8_duckdb

An example of how to merge PostgreSQL data and local csv

$ pip install duckdb==1.4.2 david8-duckdb
# create a csv
$ echo "relkind,name" > names.csv && echo "r,ordinary table" >> names.csv && echo "i,index" >> names.csv && echo "v,view" >> names.csv
import duckdb
from david8.joins import left
from david8.predicates import ne
from david8.functions import count
from david8_duckdb import get_qb

con = duckdb.connect()
con.execute('INSTALL postgres')
con.execute('LOAD postgres')
con.execute("""
    ATTACH 'host=127.0.0.1 port=5432 user=david8 password=david8 dbname=david8'
    AS pg
    (TYPE postgres);
""")


qb = get_qb()
# CREATE TABLE names AS SELECT * FROM read_csv('names.csv')
con.execute(
    qb.create_table_as(
        qb.select('*').from_csv('names.csv'),
        'names'
    ).get_sql()
)

query = (
    qb.select(
        'relkind',
        'name',
        count('*').as_('obj_counter'),
    )
    .from_table('pg_class', db_name='pg_catalog')
    .join(left().table('names').using('relkind'))
    .where(ne('relkind', 't'))
    .group_by('relkind', 'name')
    .order_by('relkind')
)

print(con.execute(query.get_sql(), query.get_tuple_parameters()).fetchall())
# [INFO]: SELECT relkind, name, count(*) AS obj_counter FROM pg_catalog.pg_class
# LEFT JOIN names USING (relkind) WHERE relkind != ? GROUP BY relkind, name ORDER BY relkind
# {'1': 't'}
# [('i', 'index', 124), ('r', 'ordinary table', 210), ('v', 'view', 46)]

Apache Doris

docker-compose.yml 
version: "3.8"

networks:
  doris_net:
    driver: bridge
    ipam:
      config:
        - subnet: 172.28.90.0/24

services:
  doris-fe:
    image: apache/doris:fe-2.1.10
    hostname: doris-fe
    container_name: doris-fe
    environment:
      - FE_SERVERS=fe1:172.28.90.2:9010
      - FE_ID=1
    networks:
      doris_net:
        ipv4_address: 172.28.90.2
    ports:
      - "8030:8030"
      - "9030:9030"
      - "9010:9010"
      - "9020:9020"
    restart: unless-stopped

  doris-be:
    image: apache/doris:be-2.1.10
    hostname: doris-be
    container_name: doris-be
    environment:
      - FE_SERVERS=fe1:172.28.90.2:9010
      - BE_ADDR=172.28.90.3:9050
    networks:
      doris_net:
        ipv4_address: 172.28.90.3
    ports:
      - "8040:8040"
      - "9050:9050"
      - "9060:9060"
      - "8060:8060"
    depends_on:
      - doris-fe
    restart: unless-stopped

run containers and create db:

$ pip install pymysql
$ docker compose up -d  # wait for BE interface
$ mysql -h 127.0.0.1 -P 9030 -u root
$ CREATE DATABASE david8;

query example:

import pymysql

from david8 import get_default_qb
from david8.functions import count
from david8.joins import inner
from david8.predicates import eq_c, eq

conn = pymysql.connect(
    host="127.0.0.1",
    port=9030,
    user="root",
    password="",
    database="david8",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)


with conn.cursor() as cursor:
    cursor.execute("""CREATE TABLE IF NOT EXISTS sales (
            order_id    BIGINT,
            order_date  DATE,
            customer    VARCHAR(100),
            amount      DECIMAL(12,2)
        )
        DUPLICATE KEY(order_id, order_date)
        PARTITION BY RANGE(order_date) (
            PARTITION p202601 VALUES [('2026-01-01'), ('2026-02-01')),
            PARTITION p202602 VALUES [('2026-02-01'), ('2026-03-01')),
            PARTITION p202603 VALUES [('2026-03-01'), ('2026-04-01'))
        )
        DISTRIBUTED BY HASH(order_id) BUCKETS 4
        PROPERTIES (
            "replication_allocation" = "tag.location.default: 1"
        );
    """)

    qb = get_default_qb()
    query = (
        qb
        .insert()
        .into('sales')
        .record({
            'order_id': 1001,
            'order_date': '2026-01-01',
            'customer': 'Guido van Rossum',
            'amount': 4599.90,
        })
    )

    cursor.execute(query.get_sql(), query.get_parameters())

    query = (
        qb
        .select(
            't.table_schema',
            't.table_name',
            't.engine',
            count('p.partition_name').as_('partition_count')
        )
        .from_table('tables', 't', 'information_schema')
        .join(
            inner()
            .table('partitions', 'information_schema')
            .as_('p')
            .on(
                eq_c('t.table_schema', 'p.table_schema'),
                eq_c('t.table_name', 'p.table_name'),
            )
        )
        .where(
            eq('t.table_schema', 'david8'),
            eq('t.table_name', 'sales'),
        )
        .group_by(
            't.table_schema',
            't.table_name',
            't.engine'
        )
    )

    print(query.get_sql())
    cursor.execute(query.get_sql(), query.get_parameters())
    rows = cursor.fetchall()
    for row in rows:
        print(row)
# SELECT t.table_schema,
#        t.table_name,
#        t.engine,
#        count(p.partition_name) AS partition_count
#   FROM information_schema.tables AS t
#  INNER JOIN information_schema.partitions AS p ON (
#        t.table_schema = p.table_schema AND t.table_name = p.table_name
#  )
#  WHERE t.table_schema = %(p1)s AND t.table_name = %(p2)s
# GROUP BY t.table_schema, t.table_name, t.engine
#{'table_schema': 'david8', 'table_name': 'sales', 'engine': 'Doris', 'partition_count': 3}

MySQL

$ docker run --name david8_msql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=david8 -e MYSQL_DATABASE=david8 -e MYSQL_USER=david8 -e MYSQL_PASSWORD=david8 -d mysql:8.0
$ pip install mysql-connector-python==9.5.0
import mysql.connector
from david8 import get_default_qb
from david8.functions import count
from david8.expressions import desc
from david8.predicates import ne

cnx = mysql.connector.connect(host='127.0.0.1', port=3306, user='david8', password='david8', database='david8')
query = (
    get_default_qb()
    .select(
        'data_type',
        count('*').as_('data_types_without_json'),
    )
    .from_table('COLUMNS', db_name='information_schema')
    .where(ne('data_type', 'json'))
    .group_by('data_type')
    .order_by(desc('data_types_without_json'))
)

cursor = cnx.cursor()
cursor.execute(query.get_sql(), query.get_parameters())
print(cursor.fetchall())
# [INFO]: SELECT data_type, count(*) AS data_types_without_json FROM information_schema.COLUMNS
# WHERE data_type != %(p1)s GROUP BY data_type ORDER BY data_types_without_json DESC
# {'p1': 'json'}
# [('varchar', 339), ('bigint', 150), ('int', 104), ('binary', 26), ('enum', 19), ('char', 18), ('longtext', 13), ('datetime', 13), ('float', 9), ('text', 8), ('timestamp', 7), ('set', 6), ('varbinary', 4), ('decimal', 3), ('mediumtext', 2), ('tinyint', 2), ('blob', 1), ('double', 1)]

SQLite

import sqlite3
from david8 import get_default_qb
from david8.param_styles import QMarkParamStyle
from david8.predicates import ne
from david8.functions import count


cursor = sqlite3.connect('david8.db').cursor()
query = (
    get_default_qb(QMarkParamStyle())
    .select(
        'type',
        count('type').as_('types'),
    )
    .from_table('sqlite_master')
    .where(ne('type', 'view'))
    .group_by('type')
)

cursor.execute('DROP TABLE IF EXISTS david8')
cursor.execute('CREATE TABLE david8 (id INTEGER)')
cursor.execute(query.get_sql(), query.get_tuple_parameters())

print(cursor.fetchall())
# [INFO]: SELECT type, count(type) AS types FROM sqlite_master WHERE type != ? GROUP BY type {'1': 'view'}
# [('table', 1)]

Coverage Status PyPi Python versions Downloads

Docs
ClickHouse
DuckDb
PostgreSQL

Clone this wiki locally