0
# Parameters and Types
1

2
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. These components enable safe parameter binding and advanced data type operations.
3

4
## Capabilities
5

6
### Parameter Types
7

8
Different parameter styles for prepared statements and parameterized queries.
9

10
```python { .api }
11
class Parameter:
12
    """Base class for SQL parameters."""
13
    
14
class QmarkParameter(Parameter):
15
    def __init__(self, key):
16
        """
17
        Question mark style parameter: ?
18
        
19
        Parameters:
20
        - key: Parameter identifier
21
        """
22

23
class NamedParameter(Parameter):
24
    def __init__(self, key):
25
        """
26
        Named style parameter: :name
27
        
28
        Parameters:
29
        - key: Parameter name
30
        """
31

32
class NumericParameter(Parameter):
33
    def __init__(self, key):
34
        """
35
        Numeric style parameter: :1, :2, etc.
36
        
37
        Parameters:
38
        - key: Parameter number
39
        """
40

41
class FormatParameter(Parameter):
42
    def __init__(self, key):
43
        """
44
        ANSI C printf style parameter: %s, %d, etc.
45
        
46
        Parameters:
47
        - key: Parameter format specifier
48
        """
49

50
class PyformatParameter(Parameter):
51
    def __init__(self, key):
52
        """
53
        Python format style parameter: %(name)s
54
        
55
        Parameters:
56
        - key: Parameter name
57
        """
58
```
59

60
**Usage Examples:**
61

62
```python
63
from pypika import Query, Table
64
from pypika.terms import QmarkParameter, NamedParameter, PyformatParameter
65

66
users = Table('users')
67
orders = Table('orders')
68

69
# Question mark parameters (SQLite, MySQL)
70
qmark_query = (Query.from_(users)
71
               .select('*')
72
               .where(users.age > QmarkParameter('age'))
73
               .where(users.status == QmarkParameter('status')))
74

75
print(qmark_query.get_sql())
76
# SELECT * FROM "users" WHERE "age">? AND "status"=?
77

78
# Named parameters (PostgreSQL, Oracle)
79
named_query = (Query.from_(users)
80
               .select('*')
81
               .where(users.age > NamedParameter('min_age'))
82
               .where(users.status == NamedParameter('user_status')))
83

84
print(named_query.get_sql())
85
# SELECT * FROM "users" WHERE "age">:min_age AND "status"=:user_status
86

87
# Python format parameters
88
pyformat_query = (Query.from_(users)
89
                  .select('*')
90
                  .where(users.age > PyformatParameter('min_age'))
91
                  .where(users.status == PyformatParameter('status')))
92

93
print(pyformat_query.get_sql())
94
# SELECT * FROM "users" WHERE "age">%(min_age)s AND "status"=%(status)s
95

96
# INSERT with parameters
97
insert_query = (Query.into(users)
98
                .columns(users.name, users.email, users.age)
99
                .insert(
100
                    NamedParameter('name'),
101
                    NamedParameter('email'), 
102
                    NamedParameter('age')
103
                ))
104

105
# UPDATE with parameters
106
update_query = (Query.update(users)
107
                .set(users.email, NamedParameter('new_email'))
108
                .set(users.updated_at, NamedParameter('timestamp'))
109
                .where(users.id == NamedParameter('user_id')))
110
```
111

112
### JSON Data Type
113

114
JSON field operations and path-based access for advanced JSON manipulation.
115

116
```python { .api }
117
class JSON:
118
    def get_json_value(self, key_or_index) -> BasicCriterion:
119
        """
120
        JSON -> operator: extract JSON value.
121
        
122
        Parameters:
123
        - key_or_index: Object key or array index
124
        """
125
        
126
    def get_text_value(self, key_or_index) -> BasicCriterion:
127
        """
128
        JSON ->> operator: extract JSON value as text.
129
        
130
        Parameters:
131
        - key_or_index: Object key or array index
132
        """
133
        
134
    def get_path_json_value(self, path_json) -> BasicCriterion:
135
        """
136
        JSON #> operator: extract JSON value at path.
137
        
138
        Parameters:
139
        - path_json: JSON path array
140
        """
141
        
142
    def get_path_text_value(self, path_json) -> BasicCriterion:
143
        """
144
        JSON #>> operator: extract JSON value at path as text.
145
        
146
        Parameters:
147
        - path_json: JSON path array
148
        """
149
        
150
    def has_key(self, key) -> BasicCriterion:
151
        """
152
        JSON ? operator: check if key exists.
153
        
154
        Parameters:
155
        - key: Key to check for existence
156
        """
157
        
158
    def contains(self, other) -> BasicCriterion:
159
        """
160
        JSON @> operator: check if contains value.
161
        
162
        Parameters:
163
        - other: JSON value to check containment
164
        """
165
        
166
    def contained_by(self, other) -> BasicCriterion:
167
        """
168
        JSON <@ operator: check if contained by value.
169
        
170
        Parameters:
171
        - other: JSON value to check containment by
172
        """
173
        
174
    def has_keys(self, keys) -> BasicCriterion:
175
        """
176
        JSON ?& operator: check if has all keys.
177
        
178
        Parameters:
179
        - keys: Array of keys to check
180
        """
181
        
182
    def has_any_keys(self, keys) -> BasicCriterion:
183
        """
184
        JSON ?| operator: check if has any keys.
185
        
186
        Parameters:
187
        - keys: Array of keys to check
188
        """
189
```
190

191
**Usage Examples:**
192

193
```python
194
from pypika import PostgreSQLQuery, Table, JSON
195

196
# PostgreSQL table with JSON columns
197
users = Table('users')
198
events = Table('events')
199

200
# JSON field operations
201
profile = users.profile  # Assuming JSON/JSONB column
202
metadata = events.metadata
203

204
# Extract JSON values
205
query = (PostgreSQLQuery.from_(users)
206
         .select(
207
             users.id,
208
             profile.get_text_value('name').as_('profile_name'),
209
             profile.get_json_value('preferences').as_('user_prefs'),
210
             profile.get_text_value('email').as_('profile_email')
211
         ))
212

213
# Path-based JSON extraction
214
query = (PostgreSQLQuery.from_(users)
215
         .select(
216
             users.id,
217
             profile.get_path_text_value(['address', 'street']).as_('street'),
218
             profile.get_path_text_value(['address', 'city']).as_('city'),
219
             profile.get_path_json_value(['contact', 'phones']).as_('phone_numbers')
220
         ))
221

222
# JSON key existence checks
223
query = (PostgreSQLQuery.from_(users)
224
         .select(users.id, users.name)
225
         .where(profile.has_key('email'))
226
         .where(profile.has_key('phone')))
227

228
# Multiple key checks
229
query = (PostgreSQLQuery.from_(users)
230
         .select('*')
231
         .where(profile.has_keys(['name', 'email', 'address']))
232
         .where(profile.has_any_keys(['twitter', 'facebook', 'linkedin'])))
233

234
# JSON containment operations
235
query = (PostgreSQLQuery.from_(users)
236
         .select('*')
237
         .where(profile.contains({'subscription': 'premium'}))
238
         .where(profile.contains({'preferences': {'newsletter': True}})))
239

240
# Complex JSON queries
241
query = (PostgreSQLQuery.from_(events)
242
         .select(
243
             events.id,
244
             events.event_type,
245
             metadata.get_text_value('source').as_('event_source'),
246
             metadata.get_path_text_value(['device', 'type']).as_('device_type'),
247
             metadata.get_path_json_value(['location', 'coordinates']).as_('coordinates')
248
         )
249
         .where(metadata.has_key('user_id'))
250
         .where(metadata.get_text_value('status') == 'completed'))
251

252
# JSON aggregations
253
from pypika.functions import Count
254
query = (PostgreSQLQuery.from_(users)
255
         .select(
256
             profile.get_text_value('country').as_('country'),
257
             Count('*').as_('user_count')
258
         )
259
         .where(profile.has_key('country'))
260
         .groupby(profile.get_text_value('country')))
261
```
262

263
### Interval Data Type
264

265
Time and date interval expressions for temporal calculations and date arithmetic.
266

267
```python { .api }
268
class Interval:
269
    def __init__(self, years=0, months=0, days=0, hours=0, minutes=0, 
270
                 seconds=0, microseconds=0, quarters=0, weeks=0, dialect=None):
271
        """
272
        Create time interval for date arithmetic.
273
        
274
        Parameters:
275
        - years: Number of years
276
        - months: Number of months  
277
        - days: Number of days
278
        - hours: Number of hours
279
        - minutes: Number of minutes
280
        - seconds: Number of seconds
281
        - microseconds: Number of microseconds
282
        - quarters: Number of quarters
283
        - weeks: Number of weeks
284
        - dialect: Database dialect for formatting
285
        """
286
```
287

288
**Usage Examples:**
289

290
```python
291
from pypika import Query, Table, Interval
292
from pypika.functions import Now, CurDate
293

294
users = Table('users')
295
subscriptions = Table('subscriptions')
296
events = Table('events')
297

298
# Basic interval operations
299
one_day = Interval(days=1)
300
one_week = Interval(weeks=1)
301
one_month = Interval(months=1)
302
one_year = Interval(years=1)
303
thirty_minutes = Interval(minutes=30)
304

305
# Date arithmetic with intervals
306
query = (Query.from_(users)
307
         .select(
308
             users.name,
309
             users.created_at,
310
             (users.created_at + one_year).as_('anniversary_date'),
311
             (Now() - users.created_at).as_('account_age'),
312
             (users.created_at + Interval(days=30)).as_('trial_end_date')
313
         ))
314

315
# Filter by time ranges
316
recent_users = (Query.from_(users)
317
                .select('*')
318
                .where(users.created_at > (Now() - Interval(days=30))))
319

320
old_users = (Query.from_(users)
321
             .select('*')
322
             .where(users.created_at < (Now() - Interval(years=2))))
323

324
# Subscription management
325
query = (Query.from_(subscriptions)
326
         .select(
327
             subscriptions.user_id,
328
             subscriptions.start_date,
329
             (subscriptions.start_date + Interval(months=12)).as_('renewal_date'),
330
             subscriptions.trial_start,
331
             (subscriptions.trial_start + Interval(days=14)).as_('trial_end')
332
         ))
333

334
# Complex interval calculations
335
quarterly_intervals = Interval(quarters=1)
336
semi_annual = Interval(months=6)
337
bi_weekly = Interval(weeks=2)
338

339
query = (Query.from_(events)
340
         .select(
341
             events.event_date,
342
             (events.event_date + quarterly_intervals).as_('next_quarter'),
343
             (events.event_date - semi_annual).as_('six_months_ago'),
344
             (events.event_date + bi_weekly).as_('two_weeks_later')
345
         ))
346

347
# Time-based grouping and analysis
348
from pypika.functions import Extract, DatePart
349
query = (Query.from_(users)
350
         .select(
351
             Extract(DatePart.year, users.created_at).as_('signup_year'),
352
             Extract(DatePart.month, users.created_at).as_('signup_month'),
353
             Count('*').as_('signups')
354
         )
355
         .where(users.created_at > (Now() - Interval(years=2)))
356
         .groupby(
357
             Extract(DatePart.year, users.created_at),
358
             Extract(DatePart.month, users.created_at)
359
         ))
360

361
# Interval comparisons
362
active_sessions = (Query.from_(events)
363
                   .select('*')
364
                   .where(events.session_end - events.session_start > Interval(minutes=5)))
365

366
# Database-specific interval usage
367
from pypika import PostgreSQLQuery
368
pg_query = (PostgreSQLQuery.from_(users)
369
            .select(
370
                users.name,
371
                (users.created_at + Interval(months=1, dialect='postgresql')).as_('plus_month')
372
            ))
373
```
374

375
### Array Data Types
376

377
SQL array operations for databases that support array types.
378

379
```python { .api }
380
class Array:
381
    def __init__(self, *items):
382
        """
383
        Create SQL array literal.
384
        
385
        Parameters:
386
        - items: Array elements
387
        """
388
        
389
    def contains(self, item) -> BasicCriterion:
390
        """Check if array contains item."""
391
        
392
    def overlap(self, other_array) -> BasicCriterion:
393
        """Check if arrays overlap."""
394
        
395
    def length(self) -> Function:
396
        """Get array length."""
397
```
398

399
**Usage Examples:**
400

401
```python
402
from pypika import PostgreSQLQuery, Table, Array
403

404
# PostgreSQL arrays
405
users = Table('users')
406
posts = Table('posts')
407

408
# Array operations
409
tags_array = Array('python', 'sql', 'database')
410
colors_array = Array('red', 'blue', 'green')
411

412
# Array containment
413
query = (PostgreSQLQuery.from_(posts)
414
         .select('*')
415
         .where(posts.tags.contains('python')))
416

417
# Array overlap
418
query = (PostgreSQLQuery.from_(posts)
419
         .select('*')
420
         .where(posts.tags.overlap(tags_array)))
421

422
# Array functions
423
from pypika.functions import ArrayLength, ArrayAppend
424
query = (PostgreSQLQuery.from_(posts)
425
         .select(
426
             posts.title,
427
             posts.tags,
428
             ArrayLength(posts.tags).as_('tag_count')
429
         ))
430

431
# Complex array queries
432
query = (PostgreSQLQuery.from_(users)
433
         .select(
434
             users.name,
435
             users.skills,
436
             ArrayLength(users.skills).as_('skill_count')
437
         )
438
         .where(users.skills.overlap(Array('python', 'java', 'javascript')))
439
         .where(ArrayLength(users.skills) >= 3))
440
```
441

442
### Tuple Data Type
443

444
SQL tuple operations for multi-value comparisons and row constructors.
445

446
```python { .api }
447
class Tuple:
448
    def __init__(self, *items):
449
        """
450
        Create SQL tuple literal.
451
        
452
        Parameters:
453
        - items: Tuple elements
454
        """
455
        
456
    def isin(self, *values) -> BasicCriterion:
457
        """Check if tuple is in list of values."""
458
```
459

460
**Usage Examples:**
461

462
```python
463
from pypika import Query, Table, Tuple
464

465
users = Table('users')
466
coordinates = Table('coordinates')
467

468
# Multi-column comparisons
469
name_tuple = Tuple(users.first_name, users.last_name)
470
valid_names = Tuple(
471
    Tuple('John', 'Doe'),
472
    Tuple('Jane', 'Smith'),
473
    Tuple('Bob', 'Johnson')
474
)
475

476
query = (Query.from_(users)
477
         .select('*')
478
         .where(name_tuple.isin(valid_names)))
479

480
# Coordinate comparisons
481
location_tuple = Tuple(coordinates.latitude, coordinates.longitude)
482
valid_locations = Tuple(
483
    Tuple(40.7128, -74.0060),  # New York
484
    Tuple(34.0522, -118.2437), # Los Angeles
485
    Tuple(41.8781, -87.6298)   # Chicago
486
)
487

488
query = (Query.from_(coordinates)
489
         .select('*')
490
         .where(location_tuple.isin(valid_locations)))
491

492
# Complex tuple operations
493
user_status_tuple = Tuple(users.status, users.role, users.department)
494
admin_combinations = Tuple(
495
    Tuple('active', 'admin', 'IT'),
496
    Tuple('active', 'admin', 'Security'),
497
    Tuple('pending', 'admin', 'IT')
498
)
499

500
query = (Query.from_(users)
501
         .select('*')
502
         .where(user_status_tuple.isin(admin_combinations)))
503
```
504

505
### Special Value Types
506

507
Special SQL values and constants for various database operations.
508

509
```python { .api }
510
class NullValue:
511
    """SQL NULL value representation."""
512

513
class SystemTimeValue:
514
    """SQL SYSTEM_TIME value for temporal tables."""
515

516
class Not:
517
    def __init__(self, term):
518
        """
519
        NOT operator wrapper.
520
        
521
        Parameters:
522
        - term: Expression to negate
523
        """
524

525
class Bracket:
526
    def __init__(self, term):
527
        """
528
        Parentheses wrapper for expressions.
529
        
530
        Parameters:
531
        - term: Expression to wrap in parentheses
532
        """
533

534
class Index:
535
    def __init__(self, name: str):
536
        """
537
        Database index reference.
538
        
539
        Parameters:
540
        - name: Index name
541
        """
542
```
543

544
**Usage Examples:**
545

546
```python
547
from pypika import Query, Table, NULL, SYSTEM_TIME, Not, Bracket, Index
548

549
users = Table('users')
550
orders = Table('orders')
551

552
# NULL operations
553
query = (Query.from_(users)
554
         .select('*')
555
         .where(users.phone != NULL)
556
         .where(users.deleted_at == NULL))
557

558
# NOT operations
559
not_admin = Not(users.role == 'admin')
560
query = Query.from_(users).select('*').where(not_admin)
561

562
# Complex conditions with parentheses
563
complex_condition = Bracket(
564
    (users.age >= 18) & (users.status == 'active')
565
) | Bracket(
566
    (users.role == 'admin') & (users.verified == True)
567
)
568

569
query = Query.from_(users).select('*').where(complex_condition)
570

571
# Index hints (MySQL-specific)
572
user_email_idx = Index('idx_user_email')
573
# Usage would depend on dialect-specific implementation
574

575
# Temporal table operations
576
historical_users = users.for_(SYSTEM_TIME.between('2023-01-01', '2023-12-31'))
577
query = Query.from_(historical_users).select('*')
578

579
# Combining special values
580
query = (Query.from_(users)
581
         .select('*')
582
         .where(
583
             Not(users.email == NULL) &
584
             Bracket(
585
                 (users.created_at > '2023-01-01') |
586
                 (users.status == 'premium')
587
             )
588
         ))
589
```
590

591
### Type Conversion Utilities
592

593
Utilities for handling type conversions and value wrapping.
594

595
```python { .api }
596
class ValueWrapper:
597
    def __init__(self, value):
598
        """
599
        Wrap Python values for SQL usage.
600
        
601
        Parameters:
602
        - value: Python value to wrap
603
        """
604
        
605
class LiteralValue:
606
    def __init__(self, value):
607
        """
608
        Create literal SQL value.
609
        
610
        Parameters:
611
        - value: Literal value
612
        """
613
```
614

615
**Usage Examples:**
616

617
```python
618
from pypika import Query, Table, ValueWrapper, LiteralValue
619

620
users = Table('users')
621

622
# Value wrapping for safe SQL generation
623
python_list = [1, 2, 3, 4, 5]
624
wrapped_list = ValueWrapper(python_list)
625

626
query = Query.from_(users).select('*').where(users.id.isin(wrapped_list))
627

628
# Literal values
629
current_timestamp = LiteralValue('CURRENT_TIMESTAMP')
630
query = (Query.into(users)
631
         .columns(users.name, users.email, users.created_at)
632
         .insert('John Doe', 'john@example.com', current_timestamp))
633

634
# Safe parameter handling
635
def build_user_query(min_age=None, status=None, roles=None):
636
    query = Query.from_(users).select('*')
637
    
638
    if min_age is not None:
639
        query = query.where(users.age >= ValueWrapper(min_age))
640
    
641
    if status is not None:
642
        query = query.where(users.status == ValueWrapper(status))
643
    
644
    if roles is not None:
645
        query = query.where(users.role.isin(ValueWrapper(roles)))
646
    
647
    return query
648

649
# Usage
650
user_query = build_user_query(min_age=18, status='active', roles=['admin', 'user'])
651
```