0
# PyPika
1

2
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.
3

4
## Package Information
5

6
- **Package Name**: PyPika
7
- **Language**: Python
8
- **Installation**: `pip install pypika`
9

10
## Core Imports
11

12
```python
13
from pypika import Query, Table, Field, Schema, Database, Column
14
from pypika import Case, Criterion, EmptyCriterion, Tuple, Array
15
from pypika import Tables, Columns  # Utility functions for creating multiple tables/columns
16
from pypika import JoinType, Order, DatePart
17
from pypika import NULL, SYSTEM_TIME
18
```
19

20
Database-specific query builders:
21

22
```python
23
from pypika import MySQLQuery, PostgreSQLQuery, OracleQuery, MSSQLQuery
24
from pypika import ClickHouseQuery, RedshiftQuery, SQLLiteQuery, VerticaQuery, SnowflakeQuery
25
```
26

27
## Basic Usage
28

29
```python
30
from pypika import Query, Table, Field
31

32
# Create table references
33
users = Table('users')
34
orders = Table('orders')
35

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

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

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

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

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

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

61
## Architecture
62

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

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

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

74
## Capabilities
75

76
### Core Query Building
77

78
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.
79

80
```python { .api }
81
class Query:
82
    @staticmethod
83
    def from_(table) -> QueryBuilder: ...
84
    @staticmethod
85
    def select(*terms) -> QueryBuilder: ...
86
    @staticmethod
87
    def into(table) -> QueryBuilder: ...
88
    @staticmethod
89
    def update(table) -> QueryBuilder: ...
90
    @staticmethod
91
    def create_table(table) -> CreateQueryBuilder: ...
92
    @staticmethod
93
    def drop_table(table) -> DropQueryBuilder: ...
94
    @staticmethod
95
    def with_(table, name) -> QueryBuilder: ...
96
```
97

98
[Core Query Operations](./core-queries.md)
99

100
### Tables and Schema Management
101

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

104
```python { .api }
105
class Table:
106
    def __init__(self, name: str, schema: Optional[Union[Schema, str]] = None, 
107
                 alias: Optional[str] = None, query_cls: Optional[Type[Query]] = None): ...
108
    def select(self, *terms) -> QueryBuilder: ...
109
    def update(self) -> QueryBuilder: ...
110
    def insert(self, *terms) -> QueryBuilder: ...
111
    def field(self, name: str) -> Field: ...
112
    def as_(self, alias: str) -> Table: ...
113

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

117
class Database(Schema): ...
118

119
class Column:
120
    def __init__(self, column_name: str, column_type: Optional[str] = None,
121
                 nullable: Optional[bool] = None, default: Optional[Any] = None): ...
122
```
123

124
[Tables and Schema](./tables-schema.md)
125

126
### Terms and Expressions
127

128
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.
129

130
```python { .api }
131
class Field:
132
    def __init__(self, name: str, alias: Optional[str] = None, table: Optional[Table] = None): ...
133

134
class Criterion:
135
    @staticmethod
136
    def any(terms) -> EmptyCriterion: ...
137
    @staticmethod
138
    def all(terms) -> EmptyCriterion: ...
139

140
class Case:
141
    def when(self, criterion, term) -> Case: ...
142
    def else_(self, term) -> Case: ...
143

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

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

150
class CustomFunction:
151
    def __init__(self, name: str, params: Optional[list] = None): ...
152
```
153

154
[Terms and Expressions](./terms-expressions.md)
155

156
### Database Dialects
157

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

160
```python { .api }
161
class MySQLQuery(Query): ...
162
class PostgreSQLQuery(Query): ...
163
class OracleQuery(Query): ...
164
class MSSQLQuery(Query): ...
165
class ClickHouseQuery(Query): ...
166
class RedshiftQuery(Query): ...
167
class SQLLiteQuery(Query): ...
168
class VerticaQuery(Query): ...
169
```
170

171
[Database Dialects](./dialects.md)
172

173
### SQL Functions
174

175
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).
176

177
```python { .api }
178
# Aggregate functions
179
class Count(AggregateFunction): ...
180
class Sum(AggregateFunction): ...
181
class Avg(AggregateFunction): ...
182

183
# String functions  
184
class Concat(Function): ...
185
class Upper(Function): ...
186
class Lower(Function): ...
187

188
# Date/time functions
189
class Now(Function): ...
190
class DateAdd(Function): ...
191
class Extract(Function): ...
192
```
193

194
[SQL Functions](./functions.md)
195

196
### Window Functions and Analytics
197

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

200
```python { .api }
201
# Window functions
202
class RowNumber(AnalyticFunction): ...
203
class Rank(AnalyticFunction): ...
204
class DenseRank(AnalyticFunction): ...
205
class NTile(AnalyticFunction): ...
206

207
# Frame boundaries
208
class Preceding: ...
209
class Following: ...
210
CURRENT_ROW: ...
211
```
212

213
[Analytics and Windows](./analytics.md)
214

215
### Parameters and Data Types
216

217
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.
218

219
```python { .api }
220
class Parameter: ...
221
class QmarkParameter(Parameter): ...
222
class NamedParameter(Parameter): ...
223
class FormatParameter(Parameter): ...
224
class PyformatParameter(Parameter): ...
225

226
class JSON:
227
    def get_json_value(self, key) -> BasicCriterion: ...
228
    def has_key(self, key) -> BasicCriterion: ...
229
    def contains(self, other) -> BasicCriterion: ...
230

231
class Interval:
232
    def __init__(self, years=0, months=0, days=0, hours=0, minutes=0, seconds=0): ...
233
```
234

235
[Parameters and Types](./parameters-types.md)
236

237
## Global Constants
238

239
```python { .api }
240
NULL: NullValue  # Global NULL constant
241
SYSTEM_TIME: SystemTimeValue  # Global SYSTEM_TIME constant
242
```
243

244
## Enumerations
245

246
```python { .api }
247
class JoinType:
248
    inner: str
249
    left: str
250
    right: str
251
    outer: str
252
    cross: str
253

254
class Order:
255
    asc: str
256
    desc: str
257

258
class DatePart:
259
    year: str
260
    quarter: str
261
    month: str
262
    day: str
263
    hour: str
264
    minute: str
265
    second: str
266
```
267

268
## Exception Types
269

270
```python { .api }
271
class QueryException(Exception): ...
272
class GroupingException(QueryException): ...
273
class CaseException(QueryException): ...
274
class JoinException(QueryException): ...
275
class RollupException(QueryException): ...
276
class SetOperationException(QueryException): ...
277
class FunctionException(QueryException): ...
278
```