0
# SQL Composition and Query Building
1

2
Safe SQL query construction using composable objects for identifiers, literals, and SQL fragments. The sql module prevents SQL injection while enabling dynamic query building through a flexible composition system that properly handles identifiers, literals, and SQL fragments.
3

4
## Capabilities
5

6
### Composable Base Classes
7

8
Foundation classes for building SQL queries through composition with proper escaping and formatting.
9

10
```python { .api }
11
class Composable:
12
    """Abstract base class for SQL composable objects."""
13
    
14
    def __init__(self, wrapped):
15
        """Initialize composable with wrapped content."""
16
    
17
    def as_string(self, context):
18
        """
19
        Return string representation for SQL context.
20
        
21
        Parameters:
22
        - context (connection/cursor): SQL context for rendering
23
        
24
        Returns:
25
        str: SQL string representation
26
        """
27
    
28
    def __add__(self, other):
29
        """
30
        Concatenate with another composable.
31
        
32
        Parameters:
33
        - other (Composable): Object to concatenate
34
        
35
        Returns:
36
        Composed: Combined composable object
37
        """
38
    
39
    def __mul__(self, n):
40
        """
41
        Repeat composable n times.
42
        
43
        Parameters:
44
        - n (int): Number of repetitions
45
        
46
        Returns:
47
        Composed: Repeated composable object
48
        """
49

50
class Composed(Composable):
51
    """Sequence of composable objects."""
52
    
53
    def __init__(self, seq):
54
        """
55
        Initialize from sequence of composables.
56
        
57
        Parameters:
58
        - seq (sequence): Sequence of Composable objects
59
        """
60
    
61
    @property
62
    def seq(self):
63
        """
64
        Access to composable sequence.
65
        
66
        Returns:
67
        list: List of composable objects
68
        """
69
    
70
    def join(self, joiner):
71
        """
72
        Join composables with separator.
73
        
74
        Parameters:
75
        - joiner (str/SQL): Separator string or SQL object
76
        
77
        Returns:
78
        Composed: Joined composable object
79
        """
80
```
81

82
### SQL Fragments
83

84
Raw SQL string fragments that are not escaped, for static SQL content.
85

86
```python { .api }
87
class SQL(Composable):
88
    """SQL string fragment (not escaped)."""
89
    
90
    def __init__(self, string):
91
        """
92
        Initialize SQL fragment.
93
        
94
        Parameters:
95
        - string (str): SQL string content
96
        
97
        Raises:
98
        TypeError: If string is not str type
99
        """
100
    
101
    @property
102
    def string(self):
103
        """
104
        Access to wrapped string.
105
        
106
        Returns:
107
        str: SQL string content
108
        """
109
    
110
    def format(self, *args, **kwargs):
111
        """
112
        Format SQL with composable arguments.
113
        
114
        Parameters:
115
        - *args: Positional composable arguments
116
        - **kwargs: Named composable arguments
117
        
118
        Returns:
119
        Composed: Formatted SQL object
120
        """
121
    
122
    def join(self, seq):
123
        """
124
        Join sequence with this SQL as separator.
125
        
126
        Parameters:
127
        - seq (iterable): Sequence of composables
128
        
129
        Returns:
130
        Composed: Joined composable object
131
        """
132
```
133

134
Usage examples:
135

136
```python
137
from psycopg2 import sql
138

139
# Basic SQL fragments
140
select_sql = sql.SQL("SELECT * FROM users")
141
where_sql = sql.SQL("WHERE active = %s")
142

143
# Combining SQL fragments
144
query = select_sql + sql.SQL(" ") + where_sql
145
print(query.as_string(conn))  # "SELECT * FROM users WHERE active = %s"
146

147
# SQL formatting with placeholders
148
template = sql.SQL("SELECT {fields} FROM {table}")
149
query = template.format(
150
    fields=sql.SQL("id, name, email"),
151
    table=sql.SQL("users")
152
)
153

154
# Joining SQL fragments
155
fields = [sql.SQL("id"), sql.SQL("name"), sql.SQL("email")]
156
field_list = sql.SQL(", ").join(fields)
157
```
158

159
### Identifiers
160

161
SQL identifiers (table names, column names) with proper quoting and escaping.
162

163
```python { .api }
164
class Identifier(Composable):
165
    """SQL identifier with proper quoting."""
166
    
167
    def __init__(self, *strings):
168
        """
169
        Initialize identifier.
170
        
171
        Parameters:
172
        - *strings: Identifier components (e.g., schema, table, column)
173
        """
174
    
175
    @property
176
    def strings(self):
177
        """
178
        Access to identifier components.
179
        
180
        Returns:
181
        tuple: Identifier string components
182
        """
183
```
184

185
Usage examples:
186

187
```python
188
from psycopg2 import sql
189

190
# Simple identifiers
191
table = sql.Identifier('users')
192
column = sql.Identifier('user_name')
193

194
# Qualified identifiers (schema.table, table.column)
195
qualified_table = sql.Identifier('public', 'users')
196
qualified_column = sql.Identifier('users', 'id')
197

198
# Safe dynamic table/column selection
199
def get_user_data(table_name, column_name):
200
    query = sql.SQL("SELECT {column} FROM {table}").format(
201
        column=sql.Identifier(column_name),
202
        table=sql.Identifier(table_name)
203
    )
204
    return query
205

206
# Usage - properly quoted regardless of input
207
query = get_user_data('user_profiles', 'first_name')
208
# Result: SELECT "first_name" FROM "user_profiles"
209

210
query = get_user_data('special-table', 'column with spaces')
211
# Result: SELECT "column with spaces" FROM "special-table"
212
```
213

214
### Literals
215

216
SQL literal values with proper escaping and type conversion.
217

218
```python { .api }
219
class Literal(Composable):
220
    """SQL literal value with proper escaping."""
221
    
222
    def __init__(self, wrapped):
223
        """
224
        Initialize literal value.
225
        
226
        Parameters:
227
        - wrapped: Python value to convert to SQL literal
228
        """
229
    
230
    @property
231
    def wrapped(self):
232
        """
233
        Access to wrapped value.
234
        
235
        Returns:
236
        Wrapped Python value
237
        """
238
```
239

240
Usage examples:
241

242
```python
243
from psycopg2 import sql
244

245
# Basic literals
246
name_literal = sql.Literal('Alice')
247
age_literal = sql.Literal(30)
248
active_literal = sql.Literal(True)
249

250
# Dynamic WHERE clauses
251
def build_where_clause(conditions):
252
    clauses = []
253
    for column, value in conditions.items():
254
        clause = sql.SQL("{} = {}").format(
255
            sql.Identifier(column),
256
            sql.Literal(value)
257
        )
258
        clauses.append(clause)
259
    return sql.SQL(" AND ").join(clauses)
260

261
# Usage
262
conditions = {'name': 'Alice', 'age': 30, 'active': True}
263
where_clause = build_where_clause(conditions)
264
# Result: "name" = 'Alice' AND "age" = 30 AND "active" = true
265

266
query = sql.SQL("SELECT * FROM users WHERE {}").format(where_clause)
267
```
268

269
### Placeholders
270

271
SQL parameter placeholders for prepared statement usage.
272

273
```python { .api }
274
class Placeholder(Composable):
275
    """SQL parameter placeholder."""
276
    
277
    def __init__(self, name=None):
278
        """
279
        Initialize placeholder.
280
        
281
        Parameters:
282
        - name (str, optional): Named parameter name
283
        """
284
    
285
    @property
286
    def name(self):
287
        """
288
        Access to placeholder name.
289
        
290
        Returns:
291
        str/None: Parameter name or None for positional
292
        """
293
```
294

295
Usage examples:
296

297
```python
298
from psycopg2 import sql
299

300
# Named placeholders
301
name_placeholder = sql.Placeholder('name')
302
age_placeholder = sql.Placeholder('age')
303

304
query = sql.SQL("SELECT * FROM users WHERE name = {} AND age > {}").format(
305
    name_placeholder, age_placeholder
306
)
307
# Result: "SELECT * FROM users WHERE name = %(name)s AND age > %(age)s"
308

309
# Execute with named parameters
310
cur.execute(query, {'name': 'Alice', 'age': 25})
311

312
# Positional placeholders
313
pos_placeholder = sql.Placeholder()
314
query = sql.SQL("INSERT INTO users (name, email) VALUES ({}, {})").format(
315
    pos_placeholder, pos_placeholder
316
)
317
# Result: "INSERT INTO users (name, email) VALUES (%s, %s)"
318

319
cur.execute(query, ('Alice', 'alice@example.com'))
320
```
321

322
### Advanced Query Building
323

324
Complex query construction patterns using composition methods.
325

326
```python
327
# Dynamic SELECT with optional fields
328
def build_select_query(table, fields=None, conditions=None, order_by=None):
329
    # Base query
330
    if fields:
331
        field_list = sql.SQL(", ").join(sql.Identifier(f) for f in fields)
332
    else:
333
        field_list = sql.SQL("*")
334
    
335
    query = sql.SQL("SELECT {} FROM {}").format(
336
        field_list, 
337
        sql.Identifier(table)
338
    )
339
    
340
    # Add WHERE clause
341
    if conditions:
342
        where_parts = []
343
        for column, value in conditions.items():
344
            where_parts.append(
345
                sql.SQL("{} = {}").format(
346
                    sql.Identifier(column),
347
                    sql.Placeholder(column)
348
                )
349
            )
350
        where_clause = sql.SQL(" AND ").join(where_parts)
351
        query = query + sql.SQL(" WHERE ") + where_clause
352
    
353
    # Add ORDER BY
354
    if order_by:
355
        order_fields = sql.SQL(", ").join(
356
            sql.Identifier(field) for field in order_by
357
        )
358
        query = query + sql.SQL(" ORDER BY ") + order_fields
359
    
360
    return query
361

362
# Usage
363
query = build_select_query(
364
    table='users',
365
    fields=['id', 'name', 'email'],
366
    conditions={'active': True, 'role': 'admin'},
367
    order_by=['name', 'created_at']
368
)
369

370
cur.execute(query, {'active': True, 'role': 'admin'})
371
```
372

373
### Bulk Operations with SQL Composition
374

375
Using SQL composition with bulk operations for dynamic queries.
376

377
```python
378
from psycopg2.extras import execute_values
379

380
# Dynamic bulk insert
381
def bulk_insert(table, columns, data):
382
    # Build column list
383
    column_list = sql.SQL(", ").join(sql.Identifier(col) for col in columns)
384
    
385
    # Build VALUES template  
386
    value_template = sql.SQL("({})").format(
387
        sql.SQL(", ").join(sql.Placeholder() for _ in columns)
388
    )
389
    
390
    # Complete query
391
    query = sql.SQL("INSERT INTO {} ({}) VALUES {}").format(
392
        sql.Identifier(table),
393
        column_list,
394
        value_template
395
    )
396
    
397
    # Execute bulk insert
398
    execute_values(cur, query, data, template=None, page_size=100)
399

400
# Usage
401
columns = ['name', 'email', 'age']
402
data = [
403
    ('Alice', 'alice@example.com', 30),
404
    ('Bob', 'bob@example.com', 25),
405
    ('Charlie', 'charlie@example.com', 35)
406
]
407

408
bulk_insert('users', columns, data)
409
```
410

411
### SQL Injection Prevention
412

413
SQL composition provides strong protection against SQL injection attacks.
414

415
```python
416
# UNSAFE - vulnerable to SQL injection  
417
user_input = "'; DROP TABLE users; --"
418
unsafe_query = f"SELECT * FROM users WHERE name = '{user_input}'"
419
# DON'T DO THIS!
420

421
# SAFE - using SQL composition
422
safe_query = sql.SQL("SELECT * FROM users WHERE name = {}").format(
423
    sql.Literal(user_input)
424
)
425
# Properly escaped: SELECT * FROM users WHERE name = '''; DROP TABLE users; --'
426

427
# SAFE - using placeholders (preferred for values)
428
param_query = sql.SQL("SELECT * FROM users WHERE name = {}").format(
429
    sql.Placeholder('name')
430
)
431
cur.execute(param_query, {'name': user_input})
432

433
# SAFE - identifiers properly quoted
434
table_name = "user-profiles"  # Contains special characters
435
safe_table_query = sql.SQL("SELECT * FROM {}").format(
436
    sql.Identifier(table_name)
437
)
438
# Result: SELECT * FROM "user-profiles"
439
```
440

441
## Types
442

443
### Composition Operators
444

445
```python { .api }
446
# Concatenation
447
composable1 + composable2  # Returns Composed object
448

449
# Repetition  
450
composable * n  # Returns Composed with n repetitions
451

452
# Equality
453
composable1 == composable2  # True if same type and content
454
```
455

456
### Context Objects
457

458
```python { .api }
459
SqlContext = connection | cursor  # Valid contexts for as_string()
460
```
461

462
### String Formatting
463

464
```python { .api }
465
# Format placeholders
466
"{}"           # Positional placeholder
467
"{0}", "{1}"   # Numbered placeholders  
468
"{name}"       # Named placeholder
469

470
# Format arguments must be Composable objects
471
FormatArgs = Composable | tuple[Composable, ...] | dict[str, Composable]
472
```
473

474
### Common Patterns
475

476
```python
477
# Table and column name building
478
schema_table = sql.Identifier('schema_name', 'table_name')
479
table_column = sql.Identifier('table_name', 'column_name')
480

481
# IN clause construction
482
values = [sql.Literal(v) for v in [1, 2, 3, 4]]
483
in_clause = sql.SQL("IN ({})").format(sql.SQL(", ").join(values))
484

485
# Dynamic ORDER BY
486
order_fields = ['name', 'created_at']
487
order_clause = sql.SQL("ORDER BY {}").format(
488
    sql.SQL(", ").join(sql.Identifier(f) for f in order_fields)
489
)
490

491
# CASE expressions
492
case_expr = sql.SQL("""
493
    CASE 
494
        WHEN {} > {} THEN {}
495
        ELSE {}
496
    END
497
""").format(
498
    sql.Identifier('age'),
499
    sql.Literal(18),
500
    sql.Literal('adult'),
501
    sql.Literal('minor')
502
)
503
```