or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

analytics.mdcore-queries.mddialects.mdfunctions.mdindex.mdparameters-types.mdtables-schema.mdterms-expressions.md
tile.json

tessl/pypi-pypika

A SQL query builder API for Python that provides programmatic interface for constructing SQL queries.

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/pypika@0.48.x

To install, run

npx @tessl/cli install tessl/pypi-pypika@0.48.0

index.mddocs/

PyPika

A comprehensive SQL query builder API for Python that provides a programmatic interface for constructing SQL queries without string concatenation or formatting. Built around the builder design pattern, PyPika offers an expressive and flexible API supporting multiple SQL database dialects including MySQL, PostgreSQL, Oracle, MSSQL, SQLite, Vertica, Redshift, and ClickHouse.

Package Information

  • Package Name: PyPika
  • Language: Python
  • Installation: pip install pypika

Core Imports

from pypika import Query, Table, Field, Schema, Database, Column
from pypika import Case, Criterion, EmptyCriterion, Tuple, Array
from pypika import Tables, Columns  # Utility functions for creating multiple tables/columns
from pypika import JoinType, Order, DatePart
from pypika import NULL, SYSTEM_TIME

Database-specific query builders:

from pypika import MySQLQuery, PostgreSQLQuery, OracleQuery, MSSQLQuery
from pypika import ClickHouseQuery, RedshiftQuery, SQLLiteQuery, VerticaQuery, SnowflakeQuery

Basic Usage

from pypika import Query, Table, Field

# Create table references
users = Table('users')
orders = Table('orders')

# Simple SELECT query
query = Query.from_(users).select('*')
print(query)  # SELECT * FROM "users"

# SELECT with specific fields
query = Query.from_(users).select(users.name, users.email)
print(query)  # SELECT "name","email" FROM "users"

# WHERE conditions
query = Query.from_(users).select('*').where(users.age > 18)
print(query)  # SELECT * FROM "users" WHERE "age">18

# JOINs
query = Query.from_(users).join(orders).on(users.id == orders.user_id).select(users.name, orders.total)
print(query)  # SELECT "name","total" FROM "users" JOIN "orders" ON "users"."id"="orders"."user_id"

# INSERT
query = Query.into(users).insert(1, 'John', 'john@email.com')
print(query)  # INSERT INTO "users" VALUES (1,'John','john@email.com')

# UPDATE
query = Query.update(users).set(users.email, 'new@email.com').where(users.id == 1)
print(query)  # UPDATE "users" SET "email"='new@email.com' WHERE "id"=1

Architecture

PyPika follows the Builder Pattern with immutable objects and fluent method chaining:

  • Query: Static factory class providing entry points for all query types
  • Table: Represents database tables with schema support and field access
  • Field: Represents table columns supporting all SQL operations and comparisons
  • Criterion: Boolean expressions for WHERE clauses with logical operations
  • Terms: Building blocks for SQL expressions (Case, Array, Tuple, etc.)
  • Dialects: Database-specific query builders with specialized features

All query builders use the @builder decorator pattern, returning new immutable instances for each operation, enabling safe method chaining and query reuse.

Capabilities

Core Query Building

Primary interface for constructing SQL queries including SELECT, INSERT, UPDATE, DELETE, and DDL operations. The Query class provides static factory methods for all query types with fluent method chaining.

class Query:
    @staticmethod
    def from_(table) -> QueryBuilder: ...
    @staticmethod
    def select(*terms) -> QueryBuilder: ...
    @staticmethod
    def into(table) -> QueryBuilder: ...
    @staticmethod
    def update(table) -> QueryBuilder: ...
    @staticmethod
    def create_table(table) -> CreateQueryBuilder: ...
    @staticmethod
    def drop_table(table) -> DropQueryBuilder: ...
    @staticmethod
    def with_(table, name) -> QueryBuilder: ...

Core Query Operations

Tables and Schema Management

Database schema representation with multi-level namespacing, table creation with column definitions, and field access patterns.

class Table:
    def __init__(self, name: str, schema: Optional[Union[Schema, str]] = None, 
                 alias: Optional[str] = None, query_cls: Optional[Type[Query]] = None): ...
    def select(self, *terms) -> QueryBuilder: ...
    def update(self) -> QueryBuilder: ...
    def insert(self, *terms) -> QueryBuilder: ...
    def field(self, name: str) -> Field: ...
    def as_(self, alias: str) -> Table: ...

class Schema:
    def __init__(self, name: str, parent: Optional[Schema] = None): ...

class Database(Schema): ...

class Column:
    def __init__(self, column_name: str, column_type: Optional[str] = None,
                 nullable: Optional[bool] = None, default: Optional[Any] = None): ...

Tables and Schema

Terms and Expressions

Building blocks for SQL expressions including fields, criteria, case statements, arrays, tuples, and custom functions. These components support all SQL operations and can be combined to create complex expressions.

class Field:
    def __init__(self, name: str, alias: Optional[str] = None, table: Optional[Table] = None): ...

class Criterion:
    @staticmethod
    def any(terms) -> EmptyCriterion: ...
    @staticmethod
    def all(terms) -> EmptyCriterion: ...

class Case:
    def when(self, criterion, term) -> Case: ...
    def else_(self, term) -> Case: ...

class Array:
    def __init__(self, *items): ...

class Tuple:
    def __init__(self, *items): ...

class CustomFunction:
    def __init__(self, name: str, params: Optional[list] = None): ...

Terms and Expressions

Database Dialects

Database-specific query builders providing specialized features and syntax for different SQL databases. Each dialect extends the base Query functionality with database-specific optimizations.

class MySQLQuery(Query): ...
class PostgreSQLQuery(Query): ...
class OracleQuery(Query): ...
class MSSQLQuery(Query): ...
class ClickHouseQuery(Query): ...
class RedshiftQuery(Query): ...
class SQLLiteQuery(Query): ...
class VerticaQuery(Query): ...

Database Dialects

SQL Functions

Comprehensive collection of SQL functions including aggregate functions (COUNT, SUM, AVG), string functions (CONCAT, SUBSTRING), date/time functions (NOW, DATE_ADD), and mathematical functions (ABS, SQRT).

# Aggregate functions
class Count(AggregateFunction): ...
class Sum(AggregateFunction): ...
class Avg(AggregateFunction): ...

# String functions  
class Concat(Function): ...
class Upper(Function): ...
class Lower(Function): ...

# Date/time functions
class Now(Function): ...
class DateAdd(Function): ...
class Extract(Function): ...

SQL Functions

Window Functions and Analytics

Advanced analytical functions including window functions (ROW_NUMBER, RANK), frame specifications, and statistical functions for complex data analysis queries.

# Window functions
class RowNumber(AnalyticFunction): ...
class Rank(AnalyticFunction): ...
class DenseRank(AnalyticFunction): ...
class NTile(AnalyticFunction): ...

# Frame boundaries
class Preceding: ...
class Following: ...
CURRENT_ROW: ...

Analytics and Windows

Parameters and Data Types

Parameter handling for prepared statements supporting multiple parameter styles (qmark, named, format), along with specialized data types for JSON operations, intervals, and database-specific types.

class Parameter: ...
class QmarkParameter(Parameter): ...
class NamedParameter(Parameter): ...
class FormatParameter(Parameter): ...
class PyformatParameter(Parameter): ...

class JSON:
    def get_json_value(self, key) -> BasicCriterion: ...
    def has_key(self, key) -> BasicCriterion: ...
    def contains(self, other) -> BasicCriterion: ...

class Interval:
    def __init__(self, years=0, months=0, days=0, hours=0, minutes=0, seconds=0): ...

Parameters and Types

Global Constants

NULL: NullValue  # Global NULL constant
SYSTEM_TIME: SystemTimeValue  # Global SYSTEM_TIME constant

Enumerations

class JoinType:
    inner: str
    left: str
    right: str
    outer: str
    cross: str

class Order:
    asc: str
    desc: str

class DatePart:
    year: str
    quarter: str
    month: str
    day: str
    hour: str
    minute: str
    second: str

Exception Types

class QueryException(Exception): ...
class GroupingException(QueryException): ...
class CaseException(QueryException): ...
class JoinException(QueryException): ...
class RollupException(QueryException): ...
class SetOperationException(QueryException): ...
class FunctionException(QueryException): ...