0
# Queries and Operations
1
2
Query building, filtering, joins, aggregation, and bulk operations. Peewee provides a fluent interface for constructing complex SQL queries using Python syntax with type safety and database portability.
3
4
## Capabilities
5
6
### Select Queries
7
8
SELECT query building with filtering, joins, grouping, ordering, and result iteration. The core interface for retrieving data from the database.
9
10
```python { .api }
11
class Select:
12
"""
13
SELECT query builder.
14
"""
15
def __init__(self, from_list=None, columns=None, group_by=None,
16
having=None, distinct=None, windows=None, for_update=None,
17
for_update_of=None, nowait=None, lateral=None, **kwargs):
18
"""
19
Initialize SELECT query.
20
21
Parameters:
22
- from_list: Tables to select from
23
- columns: Columns to select
24
- group_by: GROUP BY expressions
25
- having: HAVING conditions
26
- distinct: DISTINCT clause
27
- windows: Window functions
28
- for_update: FOR UPDATE locking
29
- for_update_of: FOR UPDATE OF specific tables
30
- nowait: NOWAIT option for locking
31
- lateral: LATERAL join support
32
- **kwargs: Additional query options
33
"""
34
35
def where(self, *expressions):
36
"""
37
Add WHERE conditions to the query.
38
39
Parameters:
40
- *expressions: Query expressions to filter by
41
42
Returns:
43
Select: Query with added conditions
44
"""
45
46
def join(self, dest, join_type='INNER', on=None):
47
"""
48
Add JOIN clause to the query.
49
50
Parameters:
51
- dest: Model or table to join
52
- join_type (str): JOIN type ('INNER', 'LEFT', 'RIGHT', 'FULL')
53
- on: Join condition (auto-detected if None)
54
55
Returns:
56
Select: Query with added join
57
"""
58
59
def switch(self, dest=None):
60
"""
61
Switch query context for chained joins.
62
63
Parameters:
64
- dest: Model to switch to (None for original)
65
66
Returns:
67
Select: Query with switched context
68
"""
69
70
def group_by(self, *columns):
71
"""
72
Add GROUP BY clause to the query.
73
74
Parameters:
75
- *columns: Columns to group by
76
77
Returns:
78
Select: Query with grouping
79
"""
80
81
def having(self, *expressions):
82
"""
83
Add HAVING conditions to grouped query.
84
85
Parameters:
86
- *expressions: Having conditions
87
88
Returns:
89
Select: Query with having conditions
90
"""
91
92
def order_by(self, *columns):
93
"""
94
Add ORDER BY clause to the query.
95
96
Parameters:
97
- *columns: Columns to order by (use .desc() for descending)
98
99
Returns:
100
Select: Query with ordering
101
"""
102
103
def limit(self, limit, offset=0):
104
"""
105
Add LIMIT and OFFSET to the query.
106
107
Parameters:
108
- limit (int): Maximum number of results
109
- offset (int): Number of results to skip
110
111
Returns:
112
Select: Query with limit/offset
113
"""
114
115
def offset(self, offset):
116
"""
117
Add OFFSET to the query.
118
119
Parameters:
120
- offset (int): Number of results to skip
121
122
Returns:
123
Select: Query with offset
124
"""
125
126
def paginate(self, page, paginate_by=20):
127
"""
128
Paginate query results.
129
130
Parameters:
131
- page (int): Page number (1-based)
132
- paginate_by (int): Results per page
133
134
Returns:
135
Select: Query with pagination
136
"""
137
138
def distinct(self, distinct=True):
139
"""
140
Make query return distinct results.
141
142
Parameters:
143
- distinct (bool): Enable/disable distinct
144
145
Returns:
146
Select: Query with distinct
147
"""
148
149
def aggregate(self, aggregation):
150
"""
151
Perform aggregation on query results.
152
153
Parameters:
154
- aggregation: Aggregation expression (e.g., fn.COUNT())
155
156
Returns:
157
Scalar result of aggregation
158
"""
159
160
def count(self, clear_limit=False):
161
"""
162
Count query results.
163
164
Parameters:
165
- clear_limit (bool): Remove LIMIT for accurate count
166
167
Returns:
168
int: Number of matching results
169
"""
170
171
def exists(self):
172
"""
173
Check if query has any results.
174
175
Returns:
176
bool: True if results exist
177
"""
178
179
def get(self):
180
"""
181
Get single result from query.
182
183
Returns:
184
Model instance
185
186
Raises:
187
DoesNotExist: If no results found
188
MultipleObjectsReturned: If multiple results found
189
"""
190
191
def first(self, n=1):
192
"""
193
Get first n results.
194
195
Parameters:
196
- n (int): Number of results to return
197
198
Returns:
199
Model instance (n=1) or list of instances (n>1)
200
"""
201
202
def scalar(self, as_tuple=False):
203
"""
204
Get scalar value from query.
205
206
Parameters:
207
- as_tuple (bool): Return as tuple if multiple columns
208
209
Returns:
210
Scalar value or tuple
211
"""
212
```
213
214
Usage examples:
215
216
```python
217
from peewee import *
218
219
# Basic select
220
users = User.select().where(User.age > 18)
221
222
# Select specific fields
223
users = User.select(User.username, User.email).where(User.is_active == True)
224
225
# Joins
226
query = (User
227
.select(User.username, fn.COUNT(Post.id).alias('post_count'))
228
.join(Post, JOIN.LEFT_OUTER)
229
.group_by(User.username)
230
.order_by(fn.COUNT(Post.id).desc()))
231
232
# Complex filtering
233
users = (User
234
.select()
235
.where(
236
(User.age.between(18, 65)) &
237
(User.email.contains('@gmail.com')) |
238
(User.username.in_(['admin', 'moderator']))
239
)
240
.order_by(User.created_at.desc())
241
.limit(10))
242
243
# Pagination
244
page_1 = User.select().paginate(1, 20) # First 20 users
245
page_2 = User.select().paginate(2, 20) # Next 20 users
246
247
# Aggregation
248
total_users = User.select().count()
249
avg_age = User.select(fn.AVG(User.age)).scalar()
250
```
251
252
### Query Expressions and Operators
253
254
Query building components for constructing WHERE conditions, field operations, and SQL expressions.
255
256
```python { .api }
257
class Field:
258
"""
259
Field query operations.
260
"""
261
def __eq__(self, other):
262
"""Equality comparison (=)."""
263
264
def __ne__(self, other):
265
"""Not equal comparison (!=)."""
266
267
def __lt__(self, other):
268
"""Less than comparison (<)."""
269
270
def __le__(self, other):
271
"""Less than or equal (<=)."""
272
273
def __gt__(self, other):
274
"""Greater than comparison (>)."""
275
276
def __ge__(self, other):
277
"""Greater than or equal (>=)."""
278
279
def __lshift__(self, other):
280
"""Left shift for special operations."""
281
282
def __rshift__(self, other):
283
"""Right shift for special operations."""
284
285
def in_(self, values):
286
"""IN clause with list of values."""
287
288
def not_in(self, values):
289
"""NOT IN clause with list of values."""
290
291
def is_null(self, null=True):
292
"""IS NULL / IS NOT NULL check."""
293
294
def contains(self, value):
295
"""String contains (LIKE %value%)."""
296
297
def startswith(self, value):
298
"""String starts with (LIKE value%)."""
299
300
def endswith(self, value):
301
"""String ends with (LIKE %value)."""
302
303
def between(self, low, high):
304
"""BETWEEN low AND high clause."""
305
306
def regexp(self, pattern):
307
"""Regular expression match."""
308
309
def concat(self, *args):
310
"""String concatenation."""
311
312
def desc(self):
313
"""Descending order modifier."""
314
315
def asc(self):
316
"""Ascending order modifier."""
317
318
# Logical operators
319
def __and__(self, other):
320
"""AND logical operator (&)."""
321
322
def __or__(self, other):
323
"""OR logical operator (|)."""
324
325
def __invert__(self):
326
"""NOT logical operator (~)."""
327
```
328
329
Usage examples:
330
331
```python
332
# Comparison operators
333
User.select().where(User.age > 18)
334
User.select().where(User.age >= 21)
335
User.select().where(User.username == 'john')
336
User.select().where(User.email != 'test@example.com')
337
338
# IN and NOT IN
339
User.select().where(User.username.in_(['john', 'jane', 'bob']))
340
User.select().where(User.status.not_in(['banned', 'suspended']))
341
342
# NULL checks
343
User.select().where(User.email.is_null(False)) # IS NOT NULL
344
User.select().where(User.deleted_at.is_null()) # IS NULL
345
346
# String operations
347
User.select().where(User.email.contains('@gmail.com'))
348
User.select().where(User.username.startswith('admin'))
349
User.select().where(User.phone.endswith('1234'))
350
351
# Range operations
352
User.select().where(User.age.between(18, 65))
353
Post.select().where(Post.created_at.between(start_date, end_date))
354
355
# Logical combinations
356
query = User.select().where(
357
(User.age > 18) &
358
(User.is_active == True) |
359
(User.role == 'admin')
360
)
361
362
# Complex expressions
363
query = User.select().where(
364
(User.age.between(18, 65)) &
365
((User.email.contains('@gmail.com')) | (User.email.contains('@yahoo.com'))) &
366
~(User.username.in_(['spam', 'test']))
367
)
368
```
369
370
### Function Calls and Expressions
371
372
SQL function calls, mathematical operations, and custom expressions for advanced query capabilities.
373
374
```python { .api }
375
class fn:
376
"""
377
SQL function call builder.
378
"""
379
# Aggregate functions
380
COUNT = lambda *args: ... # COUNT(*)
381
SUM = lambda field: ... # SUM(field)
382
AVG = lambda field: ... # AVG(field)
383
MIN = lambda field: ... # MIN(field)
384
MAX = lambda field: ... # MAX(field)
385
386
# String functions
387
LOWER = lambda field: ... # LOWER(field)
388
UPPER = lambda field: ... # UPPER(field)
389
LENGTH = lambda field: ... # LENGTH(field)
390
SUBSTR = lambda field, start, length=None: ... # SUBSTR(field, start, length)
391
392
# Date functions
393
NOW = lambda: ... # NOW()
394
DATE = lambda field: ... # DATE(field)
395
YEAR = lambda field: ... # YEAR(field)
396
MONTH = lambda field: ... # MONTH(field)
397
DAY = lambda field: ... # DAY(field)
398
399
# Math functions
400
ABS = lambda field: ... # ABS(field)
401
ROUND = lambda field, precision=None: ... # ROUND(field, precision)
402
403
# Conditional functions
404
COALESCE = lambda *args: ... # COALESCE(arg1, arg2, ...)
405
406
# Custom functions
407
def __getattr__(self, name):
408
"""Call any SQL function by name."""
409
410
class Case:
411
"""
412
SQL CASE expression builder.
413
"""
414
def __init__(self, predicate=None, expression_tuples=None, default=None):
415
"""
416
Parameters:
417
- predicate: Field to switch on (for simple CASE)
418
- expression_tuples: List of (condition, result) tuples
419
- default: Default value for ELSE clause
420
"""
421
422
def when(self, expression, value):
423
"""
424
Add WHEN clause.
425
426
Parameters:
427
- expression: Condition to test
428
- value: Value to return if condition is true
429
430
Returns:
431
Case: Updated case expression
432
"""
433
434
def else_(self, value):
435
"""
436
Add ELSE clause.
437
438
Parameters:
439
- value: Default value
440
441
Returns:
442
Case: Case expression with default
443
"""
444
445
class Cast:
446
"""
447
SQL CAST expression for type conversion.
448
"""
449
def __init__(self, field, cast):
450
"""
451
Parameters:
452
- field: Field to cast
453
- cast (str): Target data type
454
"""
455
456
class SQL:
457
"""
458
Raw SQL expression wrapper.
459
"""
460
def __init__(self, sql, *params):
461
"""
462
Parameters:
463
- sql (str): Raw SQL string
464
- *params: Parameter values for placeholders
465
"""
466
```
467
468
Usage examples:
469
470
```python
471
from peewee import *
472
473
# Aggregate functions
474
user_count = User.select(fn.COUNT(User.id)).scalar()
475
total_age = User.select(fn.SUM(User.age)).scalar()
476
avg_age = User.select(fn.AVG(User.age)).scalar()
477
478
# String functions
479
users = User.select().where(fn.LOWER(User.username) == 'john')
480
users_with_lengths = User.select(User.username, fn.LENGTH(User.username))
481
482
# Date functions
483
recent_posts = Post.select().where(Post.created_at >= fn.NOW() - timedelta(days=7))
484
posts_by_year = (Post
485
.select(fn.YEAR(Post.created_at).alias('year'), fn.COUNT(Post.id))
486
.group_by(fn.YEAR(Post.created_at)))
487
488
# CASE expressions
489
priority_case = Case(None, [
490
(Post.is_urgent == True, 'High'),
491
(Post.created_at > datetime.now() - timedelta(hours=1), 'Medium'),
492
], 'Low')
493
494
posts_with_priority = Post.select(Post.title, priority_case.alias('priority'))
495
496
# CAST expressions
497
user_ages_as_text = User.select(Cast(User.age, 'TEXT'))
498
499
# Raw SQL
500
custom_function = SQL('MY_CUSTOM_FUNCTION(?)', User.id)
501
results = User.select(User.username, custom_function.alias('custom_value'))
502
```
503
504
### Subqueries and CTEs
505
506
Subquery construction and Common Table Expressions (CTEs) for complex hierarchical and analytical queries.
507
508
```python { .api }
509
class Select:
510
def alias(self, alias):
511
"""
512
Create aliased subquery.
513
514
Parameters:
515
- alias (str): Alias name for subquery
516
517
Returns:
518
Select: Aliased query usable in other queries
519
"""
520
521
def cte(self, name, recursive=False, columns=None):
522
"""
523
Create Common Table Expression.
524
525
Parameters:
526
- name (str): CTE name
527
- recursive (bool): Enable recursive CTE
528
- columns (list): Column names for CTE
529
530
Returns:
531
CTE: Common Table Expression
532
"""
533
534
def WITH(*ctes):
535
"""
536
Create query with CTEs.
537
538
Parameters:
539
- *ctes: Common Table Expressions
540
541
Returns:
542
Query with attached CTEs
543
"""
544
```
545
546
Usage examples:
547
548
```python
549
# Subqueries
550
subquery = User.select(User.id).where(User.age > 18)
551
posts = Post.select().where(Post.author.in_(subquery))
552
553
# Correlated subqueries
554
subquery = (Post
555
.select(fn.COUNT(Post.id))
556
.where(Post.author == User.id))
557
users_with_post_count = User.select(User.username, subquery.alias('post_count'))
558
559
# Common Table Expressions (CTEs)
560
base_cte = User.select(User.id, User.username, User.manager_id).cte('employees')
561
562
recursive_cte = (base_cte
563
.select_from(base_cte.c.id, base_cte.c.username, base_cte.c.manager_id)
564
.union_all(
565
User.select(User.id, User.username, User.manager_id)
566
.join(base_cte, on=(User.manager_id == base_cte.c.id))
567
))
568
569
with_cte_query = (WITH(recursive_cte)
570
.select_from(recursive_cte.c.username)
571
.order_by(recursive_cte.c.username))
572
```
573
574
### Bulk Operations
575
576
Efficient bulk insert, update, and delete operations for handling large datasets with performance optimizations.
577
578
```python { .api }
579
class Model:
580
@classmethod
581
def bulk_create(cls, model_list, batch_size=None):
582
"""
583
Bulk create multiple instances.
584
585
Parameters:
586
- model_list (list): List of model instances
587
- batch_size (int): Number of records per batch
588
589
Returns:
590
int: Number of created records
591
"""
592
593
@classmethod
594
def bulk_update(cls, model_list, fields, batch_size=None):
595
"""
596
Bulk update multiple instances.
597
598
Parameters:
599
- model_list (list): List of model instances
600
- fields (list): Fields to update
601
- batch_size (int): Number of records per batch
602
603
Returns:
604
int: Number of updated records
605
"""
606
607
@classmethod
608
def insert_many(cls, rows, fields=None):
609
"""
610
Insert multiple rows from data.
611
612
Parameters:
613
- rows (list): List of dictionaries or tuples
614
- fields (list): Field names (for tuple data)
615
616
Returns:
617
Insert query
618
"""
619
620
@classmethod
621
def insert_from(cls, query, fields):
622
"""
623
Insert from SELECT query results.
624
625
Parameters:
626
- query: SELECT query to insert from
627
- fields (list): Target fields
628
629
Returns:
630
Insert query
631
"""
632
633
@classmethod
634
def replace_many(cls, rows, fields=None):
635
"""
636
Replace multiple rows (INSERT OR REPLACE).
637
638
Parameters:
639
- rows (list): List of dictionaries or tuples
640
- fields (list): Field names (for tuple data)
641
642
Returns:
643
Insert query with REPLACE
644
"""
645
646
def chunked(iterable, n):
647
"""
648
Split iterable into chunks of size n.
649
650
Parameters:
651
- iterable: Data to chunk
652
- n (int): Chunk size
653
654
Yields:
655
Chunks of the iterable
656
"""
657
```
658
659
Usage examples:
660
661
```python
662
from peewee import *
663
664
# Bulk create from instances
665
users = [
666
User(username=f'user{i}', email=f'user{i}@example.com')
667
for i in range(1000)
668
]
669
User.bulk_create(users, batch_size=100)
670
671
# Insert many from data
672
user_data = [
673
{'username': 'john', 'email': 'john@example.com'},
674
{'username': 'jane', 'email': 'jane@example.com'},
675
# ... more data
676
]
677
User.insert_many(user_data).execute()
678
679
# Insert from tuples
680
user_tuples = [
681
('john', 'john@example.com'),
682
('jane', 'jane@example.com'),
683
]
684
User.insert_many(user_tuples, fields=[User.username, User.email]).execute()
685
686
# Insert from query
687
active_users = User.select().where(User.is_active == True)
688
ArchivedUser.insert_from(active_users, [ArchivedUser.username, ArchivedUser.email]).execute()
689
690
# Bulk update
691
users = User.select().where(User.last_login.is_null())
692
for user in users:
693
user.is_active = False
694
695
User.bulk_update(users, fields=[User.is_active], batch_size=100)
696
697
# Chunked processing
698
all_users = User.select()
699
for user_batch in chunked(all_users, 100):
700
process_user_batch(user_batch)
701
```
702
703
### Prefetching and N+1 Prevention
704
705
Optimization techniques for efficiently loading related objects and preventing N+1 query problems.
706
707
```python { .api }
708
def prefetch(query, *subqueries, **kwargs):
709
"""
710
Prefetch related objects to avoid N+1 queries.
711
712
Parameters:
713
- query: Base query to prefetch for
714
- *subqueries: Related object queries to prefetch
715
- prefetch_type: Prefetch strategy (WHERE, JOIN)
716
717
Returns:
718
Generator of objects with prefetched relations
719
"""
720
721
class Model:
722
@classmethod
723
def select(cls, *fields):
724
def join(self, dest, join_type='INNER', on=None):
725
"""Join related tables in single query."""
726
727
def switch(self, dest=None):
728
"""Switch context for additional joins."""
729
```
730
731
Usage examples:
732
733
```python
734
# N+1 problem (BAD - multiple queries)
735
users = User.select()
736
for user in users:
737
print(f"{user.username} has {len(user.posts)} posts") # One query per user
738
739
# Solution 1: Prefetch
740
users_with_posts = prefetch(User.select(), Post.select())
741
for user in users_with_posts:
742
print(f"{user.username} has {len(user.posts)} posts") # Only 2 queries total
743
744
# Solution 2: Join with aggregation
745
users = (User
746
.select(User.username, fn.COUNT(Post.id).alias('post_count'))
747
.join(Post, JOIN.LEFT_OUTER)
748
.group_by(User.username))
749
750
for user in users:
751
print(f"{user.username} has {user.post_count} posts") # Single query
752
753
# Complex prefetching
754
query = (User
755
.select()
756
.where(User.is_active == True))
757
758
posts_query = (Post
759
.select()
760
.where(Post.is_published == True))
761
762
comments_query = (Comment
763
.select()
764
.where(Comment.is_approved == True))
765
766
# Prefetch users with their posts and comments
767
users_with_data = prefetch(query, posts_query, comments_query)
768
for user in users_with_data:
769
print(f"User: {user.username}")
770
for post in user.posts:
771
print(f" Post: {post.title}")
772
for comment in post.comments:
773
print(f" Comment: {comment.content}")
774
```
775
776
### Query Utility Classes and Functions
777
778
Core utility classes and functions for query construction, value handling, and SQL expression building.
779
780
```python { .api }
781
class AsIs:
782
"""
783
Pass-through value wrapper without escaping.
784
785
Parameters:
786
- value: Value to pass through without modification
787
"""
788
def __init__(self, value): ...
789
790
class Value:
791
"""
792
Literal value wrapper for SQL expressions.
793
794
Parameters:
795
- value: Literal value to wrap
796
- converter: Optional value converter function
797
"""
798
def __init__(self, value, converter=None): ...
799
800
class ValuesList:
801
"""
802
VALUES list for bulk insert operations.
803
804
Parameters:
805
- values (list): List of value tuples
806
"""
807
def __init__(self, values): ...
808
809
class Column:
810
"""
811
Column reference for queries and expressions.
812
813
Parameters:
814
- source: Source table or alias
815
- name (str): Column name
816
"""
817
def __init__(self, source, name): ...
818
819
class Table:
820
"""
821
Table reference for queries and joins.
822
823
Parameters:
824
- name (str): Table name
825
- alias (str): Table alias
826
"""
827
def __init__(self, name, alias=None): ...
828
829
class Window:
830
"""
831
SQL window function definitions.
832
833
Parameters:
834
- partition_by: Fields to partition by
835
- order_by: Fields to order by
836
- start: Window frame start
837
- end: Window frame end
838
"""
839
def __init__(self, partition_by=None, order_by=None, start=None, end=None): ...
840
841
class Context:
842
"""
843
Query execution context management.
844
"""
845
def __init__(self, **kwargs): ...
846
847
class QualifiedNames:
848
"""
849
Qualified column name wrapper for complex queries.
850
851
Parameters:
852
- source: Source model or table
853
"""
854
def __init__(self, source): ...
855
856
class DQ:
857
"""
858
Dynamic query helper for programmatic query construction.
859
860
Parameters:
861
- **kwargs: Dynamic query conditions
862
"""
863
def __init__(self, **kwargs): ...
864
865
def Check(constraint):
866
"""
867
Check constraint helper function.
868
869
Parameters:
870
- constraint (str): SQL constraint expression
871
872
Returns:
873
Check constraint object
874
"""
875
876
def Asc(node, collation=None, nulls=None):
877
"""
878
Create ascending order expression.
879
880
Parameters:
881
- node: Field or expression to order by
882
- collation (str): Collation name
883
- nulls (str): NULL handling ('FIRST' or 'LAST')
884
885
Returns:
886
Ordering: Ascending order expression
887
"""
888
889
def Desc(node, collation=None, nulls=None):
890
"""
891
Create descending order expression.
892
893
Parameters:
894
- node: Field or expression to order by
895
- collation (str): Collation name
896
- nulls (str): NULL handling ('FIRST' or 'LAST')
897
898
Returns:
899
Ordering: Descending order expression
900
"""
901
902
def chunked(iterable, n):
903
"""
904
Utility to chunk iterables into smaller batches.
905
906
Parameters:
907
- iterable: Data to chunk
908
- n (int): Chunk size
909
910
Yields:
911
Chunks of the iterable
912
"""
913
914
def Tuple(*args):
915
"""
916
Create tuple expression for multi-column operations.
917
918
Parameters:
919
- *args: Values or expressions to include in tuple
920
921
Returns:
922
EnclosedNodeList: Tuple expression
923
"""
924
925
class Tuple:
926
"""Alias for tuple expressions using EnclosedNodeList."""
927
928
class Value:
929
"""
930
Literal value wrapper for SQL expressions.
931
932
Parameters:
933
- value: Literal value to wrap
934
- converter: Optional value converter function
935
- unpack (bool): Unpack multi-value types like lists
936
"""
937
def __init__(self, value, converter=None, unpack=True): ...
938
939
class ValuesList:
940
"""
941
VALUES clause for table-valued expressions.
942
943
Parameters:
944
- values (list): List of value tuples
945
- columns (list): Column names for values
946
- alias (str): Table alias
947
"""
948
def __init__(self, values, columns=None, alias=None): ...
949
950
def columns(self, *names):
951
"""Set column names for VALUES table."""
952
953
class Window:
954
"""
955
SQL window function specification.
956
957
Parameters:
958
- partition_by: Fields to partition by
959
- order_by: Fields to order by
960
- start: Frame start boundary
961
- end: Frame end boundary
962
- frame_type (str): Frame type ('ROWS', 'RANGE', 'GROUPS')
963
- extends: Base window to extend
964
- exclude: Frame exclusion type
965
- alias (str): Window alias
966
"""
967
# Frame boundaries
968
CURRENT_ROW = SQL('CURRENT ROW')
969
GROUP = SQL('GROUP')
970
TIES = SQL('TIES')
971
NO_OTHERS = SQL('NO OTHERS')
972
973
# Frame types
974
GROUPS = 'GROUPS'
975
RANGE = 'RANGE'
976
ROWS = 'ROWS'
977
978
def __init__(self, partition_by=None, order_by=None, start=None, end=None,
979
frame_type=None, extends=None, exclude=None, alias=None): ...
980
981
class ModelIndex:
982
"""
983
Database index definition for models.
984
985
Parameters:
986
- model: Model class to create index for
987
- fields (list): Fields to include in index
988
- unique (bool): Create unique index
989
- safe (bool): Don't raise error if index exists
990
- where: Partial index condition
991
- using (str): Index method
992
- name (str): Custom index name
993
"""
994
def __init__(self, model, fields, unique=False, safe=True, where=None,
995
using=None, name=None): ...
996
```
997
998
### Constants and Operators
999
1000
Important constants and operator definitions for query building and special operations.
1001
1002
```python { .api }
1003
class OP:
1004
"""
1005
Operator constants for query expressions.
1006
"""
1007
EQ = 'EQ' # Equals (=)
1008
LT = 'LT' # Less than (<)
1009
LTE = 'LTE' # Less than or equal (<=)
1010
GT = 'GT' # Greater than (>)
1011
GTE = 'GTE' # Greater than or equal (>=)
1012
NE = 'NE' # Not equal (!=)
1013
IN = 'IN' # IN clause
1014
IS = 'IS' # IS clause
1015
LIKE = 'LIKE' # LIKE pattern matching
1016
ILIKE = 'ILIKE' # Case-insensitive LIKE
1017
REGEXP = 'REGEXP' # Regular expression matching
1018
IREGEXP = 'IREGEXP' # Case-insensitive regex
1019
BETWEEN = 'BETWEEN' # BETWEEN clause
1020
ADD = 'ADD' # Addition (+)
1021
SUB = 'SUB' # Subtraction (-)
1022
MUL = 'MUL' # Multiplication (*)
1023
DIV = 'DIV' # Division (/)
1024
BIN_AND = 'BIN_AND' # Binary AND (&)
1025
BIN_OR = 'BIN_OR' # Binary OR (|)
1026
XOR = 'XOR' # Exclusive OR
1027
MOD = 'MOD' # Modulo (%)
1028
LSHIFT = 'LSHIFT' # Left shift (<<)
1029
RSHIFT = 'RSHIFT' # Right shift (>>)
1030
AND = 'AND' # Logical AND
1031
OR = 'OR' # Logical OR
1032
NOT = 'NOT' # Logical NOT
1033
CONTAINS = 'CONTAINS' # String contains
1034
STARTSWITH = 'STARTSWITH' # String starts with
1035
ENDSWITH = 'ENDSWITH' # String ends with
1036
CONCAT = 'CONCAT' # String concatenation
1037
NULL = 'NULL' # NULL check
1038
1039
class JOIN:
1040
"""
1041
Join type constants.
1042
"""
1043
INNER = 'INNER' # INNER JOIN
1044
LEFT = 'LEFT' # LEFT JOIN
1045
LEFT_OUTER = 'LEFT OUTER' # LEFT OUTER JOIN
1046
RIGHT = 'RIGHT' # RIGHT JOIN
1047
RIGHT_OUTER = 'RIGHT OUTER' # RIGHT OUTER JOIN
1048
FULL = 'FULL' # FULL JOIN
1049
FULL_OUTER = 'FULL OUTER' # FULL OUTER JOIN
1050
CROSS = 'CROSS' # CROSS JOIN
1051
1052
EXCLUDED = 'EXCLUDED'
1053
"""Reference to excluded values in UPSERT operations (PostgreSQL)."""
1054
1055
DJANGO_MAP = {
1056
'AutoField': 'AutoField',
1057
'BigAutoField': 'BigAutoField',
1058
'BigIntegerField': 'BigIntegerField',
1059
'BinaryField': 'BlobField',
1060
'BooleanField': 'BooleanField',
1061
'CharField': 'CharField',
1062
'DateField': 'DateField',
1063
'DateTimeField': 'DateTimeField',
1064
'DecimalField': 'DecimalField',
1065
'DurationField': 'IntegerField',
1066
'EmailField': 'CharField',
1067
'FileField': 'CharField',
1068
'FilePathField': 'CharField',
1069
'FloatField': 'FloatField',
1070
'ImageField': 'CharField',
1071
'IntegerField': 'IntegerField',
1072
'GenericIPAddressField': 'CharField',
1073
'NullBooleanField': 'BooleanField',
1074
'PositiveIntegerField': 'IntegerField',
1075
'PositiveSmallIntegerField': 'SmallIntegerField',
1076
'SlugField': 'CharField',
1077
'SmallIntegerField': 'SmallIntegerField',
1078
'TextField': 'TextField',
1079
'TimeField': 'TimeField',
1080
'URLField': 'CharField',
1081
'UUIDField': 'UUIDField',
1082
}
1083
"""Mapping of Django ORM field types to Peewee field types."""
1084
1085
PREFETCH_TYPE = 'PREFETCH_TYPE'
1086
"""Prefetch strategy constants for optimization."""
1087
```
1088
1089
Usage examples:
1090
1091
```python
1092
from peewee import *
1093
1094
# Using operators explicitly
1095
query = User.select().where(User.age.op(OP.BETWEEN)(18, 65))
1096
1097
# Join types
1098
query = (User
1099
.select()
1100
.join(Post, JOIN.LEFT_OUTER)
1101
.where(User.is_active == True))
1102
1103
# Value wrappers
1104
raw_value = AsIs("NOW()") # Pass SQL function without escaping
1105
literal_value = Value(42) # Literal value
1106
1107
# Column references for complex queries
1108
user_table = Table('user', 'u')
1109
username_col = Column(user_table, 'username')
1110
1111
# Check constraints
1112
class User(Model):
1113
age = IntegerField(constraints=[Check('age >= 0')])
1114
1115
# Dynamic queries
1116
conditions = {'is_active': True, 'age__gt': 18}
1117
dq = DQ(**conditions)
1118
users = User.select().where(dq)
1119
1120
# EXCLUDED for UPSERT (PostgreSQL)
1121
query = (User
1122
.insert(username='john', email='john@example.com')
1123
.on_conflict(
1124
conflict_target=[User.username],
1125
update={User.email: EXCLUDED.email}))
1126
```