0
# SQL Expression Language
1
2
Programmatic SQL construction with Python expressions, including SELECT, INSERT, UPDATE, DELETE statements, joins, subqueries, functions, and complex query composition. The SQL Expression Language provides database-agnostic SQL generation.
3
4
## Capabilities
5
6
### Query Construction Functions
7
8
Core functions for building SQL statements programmatically.
9
10
```python { .api }
11
def select(*columns):
12
"""
13
Create SELECT statement.
14
15
Parameters:
16
- columns: Table, Column, or expression objects to select
17
18
Returns:
19
Select: SELECT statement object
20
"""
21
22
def insert(table):
23
"""
24
Create INSERT statement for table.
25
26
Parameters:
27
- table: Table object to insert into
28
29
Returns:
30
Insert: INSERT statement object
31
"""
32
33
def update(table):
34
"""
35
Create UPDATE statement for table.
36
37
Parameters:
38
- table: Table object to update
39
40
Returns:
41
Update: UPDATE statement object
42
"""
43
44
def delete(table):
45
"""
46
Create DELETE statement for table.
47
48
Parameters:
49
- table: Table object to delete from
50
51
Returns:
52
Delete: DELETE statement object
53
"""
54
55
def text(text, bind=None):
56
"""
57
Create literal SQL text with parameter binding.
58
59
Parameters:
60
- text: str, SQL text with optional parameter placeholders
61
- bind: Engine or Connection, bind for execution
62
63
Returns:
64
TextClause: Literal SQL text object
65
"""
66
```
67
68
### SELECT Statement Construction
69
70
Building and customizing SELECT queries with joins, filtering, grouping, and ordering.
71
72
```python { .api }
73
class Select:
74
"""SELECT statement with query building methods."""
75
76
def where(self, *criteria):
77
"""
78
Add WHERE criteria to SELECT.
79
80
Parameters:
81
- criteria: Column expressions for filtering
82
83
Returns:
84
Select: Modified SELECT with WHERE clause
85
"""
86
87
def join(self, target, onclause=None, isouter=False, full=False):
88
"""
89
Add JOIN to SELECT.
90
91
Parameters:
92
- target: Table or selectable to join
93
- onclause: join condition (auto-detected if None)
94
- isouter: bool, use LEFT OUTER JOIN
95
- full: bool, use FULL OUTER JOIN
96
97
Returns:
98
Select: Modified SELECT with JOIN
99
"""
100
101
def outerjoin(self, target, onclause=None, full=False):
102
"""
103
Add LEFT OUTER JOIN to SELECT.
104
105
Parameters:
106
- target: Table or selectable to join
107
- onclause: join condition
108
- full: bool, use FULL OUTER JOIN
109
110
Returns:
111
Select: Modified SELECT with OUTER JOIN
112
"""
113
114
def order_by(self, *clauses):
115
"""
116
Add ORDER BY to SELECT.
117
118
Parameters:
119
- clauses: Column expressions or ordering functions
120
121
Returns:
122
Select: Modified SELECT with ORDER BY
123
"""
124
125
def group_by(self, *clauses):
126
"""
127
Add GROUP BY to SELECT.
128
129
Parameters:
130
- clauses: Column expressions for grouping
131
132
Returns:
133
Select: Modified SELECT with GROUP BY
134
"""
135
136
def having(self, *criteria):
137
"""
138
Add HAVING criteria to SELECT.
139
140
Parameters:
141
- criteria: Column expressions for HAVING clause
142
143
Returns:
144
Select: Modified SELECT with HAVING
145
"""
146
147
def limit(self, limit):
148
"""
149
Add LIMIT to SELECT.
150
151
Parameters:
152
- limit: int, maximum number of rows
153
154
Returns:
155
Select: Modified SELECT with LIMIT
156
"""
157
158
def offset(self, offset):
159
"""
160
Add OFFSET to SELECT.
161
162
Parameters:
163
- offset: int, number of rows to skip
164
165
Returns:
166
Select: Modified SELECT with OFFSET
167
"""
168
169
def distinct(self, *expr):
170
"""
171
Make SELECT DISTINCT.
172
173
Parameters:
174
- expr: optional expressions for DISTINCT ON (PostgreSQL)
175
176
Returns:
177
Select: Modified SELECT with DISTINCT
178
"""
179
```
180
181
### INSERT, UPDATE, DELETE Statements
182
183
Data modification statement construction with value binding and conditional operations.
184
185
```python { .api }
186
class Insert:
187
"""INSERT statement with value specification."""
188
189
def values(self, *args, **kwargs):
190
"""
191
Specify values for INSERT.
192
193
Parameters:
194
- args: dictionaries of column-value pairs
195
- kwargs: column-value pairs as keyword arguments
196
197
Returns:
198
Insert: Modified INSERT with VALUES
199
"""
200
201
def returning(self, *cols):
202
"""
203
Add RETURNING clause (PostgreSQL, SQL Server, Oracle).
204
205
Parameters:
206
- cols: columns to return after insert
207
208
Returns:
209
Insert: Modified INSERT with RETURNING
210
"""
211
212
class Update:
213
"""UPDATE statement with WHERE and SET clauses."""
214
215
def where(self, *criteria):
216
"""
217
Add WHERE criteria to UPDATE.
218
219
Parameters:
220
- criteria: Column expressions for filtering
221
222
Returns:
223
Update: Modified UPDATE with WHERE
224
"""
225
226
def values(self, *args, **kwargs):
227
"""
228
Specify SET values for UPDATE.
229
230
Parameters:
231
- args: dictionaries of column-value pairs
232
- kwargs: column-value pairs as keyword arguments
233
234
Returns:
235
Update: Modified UPDATE with SET values
236
"""
237
238
def returning(self, *cols):
239
"""
240
Add RETURNING clause.
241
242
Parameters:
243
- cols: columns to return after update
244
245
Returns:
246
Update: Modified UPDATE with RETURNING
247
"""
248
249
class Delete:
250
"""DELETE statement with WHERE clause."""
251
252
def where(self, *criteria):
253
"""
254
Add WHERE criteria to DELETE.
255
256
Parameters:
257
- criteria: Column expressions for filtering
258
259
Returns:
260
Delete: Modified DELETE with WHERE
261
"""
262
263
def returning(self, *cols):
264
"""
265
Add RETURNING clause.
266
267
Parameters:
268
- cols: columns to return after delete
269
270
Returns:
271
Delete: Modified DELETE with RETURNING
272
"""
273
```
274
275
### Boolean and Logical Operators
276
277
Combining conditions with logical operators for complex WHERE clauses.
278
279
```python { .api }
280
def and_(*clauses):
281
"""
282
Combine expressions with AND.
283
284
Parameters:
285
- clauses: Boolean expressions to AND together
286
287
Returns:
288
BooleanClauseList: Combined AND expression
289
"""
290
291
def or_(*clauses):
292
"""
293
Combine expressions with OR.
294
295
Parameters:
296
- clauses: Boolean expressions to OR together
297
298
Returns:
299
BooleanClauseList: Combined OR expression
300
"""
301
302
def not_(clause):
303
"""
304
Negate expression with NOT.
305
306
Parameters:
307
- clause: Boolean expression to negate
308
309
Returns:
310
UnaryExpression: Negated expression
311
"""
312
```
313
314
### Functions and Expressions
315
316
SQL functions, type casting, and conditional expressions.
317
318
```python { .api }
319
def case(*whens, **kw):
320
"""
321
Create CASE expression.
322
323
Parameters:
324
- whens: sequence of (condition, value) tuples
325
- value: optional positional value for simple CASE
326
- else_: default value for ELSE clause
327
328
Returns:
329
Case: CASE expression
330
"""
331
332
def cast(expression, type_):
333
"""
334
Create CAST expression.
335
336
Parameters:
337
- expression: expression to cast
338
- type_: target data type
339
340
Returns:
341
Cast: CAST expression
342
"""
343
344
def extract(field, expr):
345
"""
346
Create EXTRACT expression for date/time components.
347
348
Parameters:
349
- field: str, component to extract (year, month, day, etc.)
350
- expr: date/time expression
351
352
Returns:
353
Extract: EXTRACT expression
354
"""
355
356
class func:
357
"""Namespace for SQL functions."""
358
359
@staticmethod
360
def count(expr=None):
361
"""
362
COUNT aggregate function.
363
364
Parameters:
365
- expr: expression to count (defaults to COUNT(*))
366
367
Returns:
368
Function: COUNT function call
369
"""
370
371
@staticmethod
372
def sum(expr):
373
"""
374
SUM aggregate function.
375
376
Parameters:
377
- expr: numeric expression to sum
378
379
Returns:
380
Function: SUM function call
381
"""
382
383
@staticmethod
384
def avg(expr):
385
"""
386
AVG aggregate function.
387
388
Parameters:
389
- expr: numeric expression to average
390
391
Returns:
392
Function: AVG function call
393
"""
394
395
@staticmethod
396
def max(expr):
397
"""
398
MAX aggregate function.
399
400
Parameters:
401
- expr: expression to find maximum
402
403
Returns:
404
Function: MAX function call
405
"""
406
407
@staticmethod
408
def min(expr):
409
"""
410
MIN aggregate function.
411
412
Parameters:
413
- expr: expression to find minimum
414
415
Returns:
416
Function: MIN function call
417
"""
418
419
@staticmethod
420
def now():
421
"""
422
Current timestamp function (database-specific).
423
424
Returns:
425
Function: Current timestamp function
426
"""
427
428
@staticmethod
429
def coalesce(*args):
430
"""
431
COALESCE function - return first non-null value.
432
433
Parameters:
434
- args: expressions to check for null
435
436
Returns:
437
Function: COALESCE function call
438
"""
439
```
440
441
### Subqueries and CTEs
442
443
Subquery construction and Common Table Expressions for complex queries.
444
445
```python { .api }
446
def exists(element):
447
"""
448
Create EXISTS expression.
449
450
Parameters:
451
- element: SELECT statement for EXISTS check
452
453
Returns:
454
Exists: EXISTS expression
455
"""
456
457
def cte(selectable, name=None, recursive=False):
458
"""
459
Create Common Table Expression.
460
461
Parameters:
462
- selectable: SELECT statement for CTE
463
- name: str, CTE name (auto-generated if None)
464
- recursive: bool, create recursive CTE
465
466
Returns:
467
CTE: Common Table Expression
468
"""
469
470
class Subquery:
471
"""Subquery that can be used in FROM clauses."""
472
473
def as_(self, name):
474
"""
475
Create alias for subquery.
476
477
Parameters:
478
- name: str, alias name
479
480
Returns:
481
Alias: Aliased subquery
482
"""
483
484
class CTE:
485
"""Common Table Expression."""
486
487
def union(self, other):
488
"""
489
UNION with another CTE (for recursive CTEs).
490
491
Parameters:
492
- other: CTE or SELECT to union with
493
494
Returns:
495
CompoundSelect: UNION of CTEs
496
"""
497
```
498
499
### Set Operations
500
501
UNION, INTERSECT, and EXCEPT operations for combining query results.
502
503
```python { .api }
504
def union(*selects):
505
"""
506
UNION multiple SELECT statements.
507
508
Parameters:
509
- selects: SELECT statements to union
510
511
Returns:
512
CompoundSelect: UNION query
513
"""
514
515
def union_all(*selects):
516
"""
517
UNION ALL multiple SELECT statements.
518
519
Parameters:
520
- selects: SELECT statements to union
521
522
Returns:
523
CompoundSelect: UNION ALL query
524
"""
525
526
def intersect(*selects):
527
"""
528
INTERSECT multiple SELECT statements.
529
530
Parameters:
531
- selects: SELECT statements to intersect
532
533
Returns:
534
CompoundSelect: INTERSECT query
535
"""
536
537
def except_(*selects):
538
"""
539
EXCEPT multiple SELECT statements.
540
541
Parameters:
542
- selects: SELECT statements for except operation
543
544
Returns:
545
CompoundSelect: EXCEPT query
546
"""
547
```
548
549
### Ordering and Grouping
550
551
Sort order specification and aggregation grouping.
552
553
```python { .api }
554
def asc(column):
555
"""
556
Create ascending sort order.
557
558
Parameters:
559
- column: column expression to sort
560
561
Returns:
562
UnaryExpression: Ascending sort expression
563
"""
564
565
def desc(column):
566
"""
567
Create descending sort order.
568
569
Parameters:
570
- column: column expression to sort
571
572
Returns:
573
UnaryExpression: Descending sort expression
574
"""
575
576
def nulls_first(column):
577
"""
578
Sort nulls first.
579
580
Parameters:
581
- column: column expression with sort order
582
583
Returns:
584
UnaryExpression: Modified sort with nulls first
585
"""
586
587
def nulls_last(column):
588
"""
589
Sort nulls last.
590
591
Parameters:
592
- column: column expression with sort order
593
594
Returns:
595
UnaryExpression: Modified sort with nulls last
596
"""
597
```
598
599
### Window Functions
600
601
Window function support with OVER clauses.
602
603
```python { .api }
604
def over(element, partition_by=None, order_by=None, rows=None, range_=None):
605
"""
606
Create OVER clause for window functions.
607
608
Parameters:
609
- element: function expression for windowing
610
- partition_by: expressions for PARTITION BY
611
- order_by: expressions for ORDER BY
612
- rows: tuple for ROWS frame specification
613
- range_: tuple for RANGE frame specification
614
615
Returns:
616
Over: Window function with OVER clause
617
"""
618
619
class Over:
620
"""Window function with OVER clause."""
621
622
def partition_by(self, *clauses):
623
"""
624
Set PARTITION BY for window.
625
626
Parameters:
627
- clauses: expressions for partitioning
628
629
Returns:
630
Over: Modified window with PARTITION BY
631
"""
632
633
def order_by(self, *clauses):
634
"""
635
Set ORDER BY for window.
636
637
Parameters:
638
- clauses: expressions for ordering
639
640
Returns:
641
Over: Modified window with ORDER BY
642
"""
643
```
644
645
### Literal Values and Parameters
646
647
Literal value binding and parameter specification.
648
649
```python { .api }
650
def literal(value, type_=None):
651
"""
652
Create literal value expression.
653
654
Parameters:
655
- value: literal value
656
- type_: optional type specification
657
658
Returns:
659
Literal: Literal value expression
660
"""
661
662
def bindparam(key, value=None, type_=None):
663
"""
664
Create bound parameter placeholder.
665
666
Parameters:
667
- key: str, parameter name
668
- value: default parameter value
669
- type_: parameter data type
670
671
Returns:
672
BindParameter: Parameter placeholder
673
"""
674
675
def null():
676
"""
677
Create NULL literal.
678
679
Returns:
680
Null: NULL literal expression
681
"""
682
683
def true():
684
"""
685
Create TRUE literal.
686
687
Returns:
688
True_: TRUE literal expression
689
"""
690
691
def false():
692
"""
693
Create FALSE literal.
694
695
Returns:
696
False_: FALSE literal expression
697
"""
698
```
699
700
## Usage Examples
701
702
### Basic SELECT Query
703
704
```python
705
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select
706
707
engine = create_engine("sqlite:///example.db")
708
metadata = MetaData()
709
710
users = Table('users', metadata,
711
Column('id', Integer, primary_key=True),
712
Column('name', String(50)),
713
Column('email', String(100))
714
)
715
716
# Simple select
717
stmt = select(users).where(users.c.name.like('%John%'))
718
719
with engine.connect() as conn:
720
result = conn.execute(stmt)
721
rows = result.fetchall()
722
```
723
724
### Complex Query with Joins
725
726
```python
727
from sqlalchemy import select, and_, or_, func
728
729
# Join with aggregation
730
stmt = select(
731
users.c.name,
732
func.count(orders.c.id).label('order_count')
733
).select_from(
734
users.join(orders, users.c.id == orders.c.user_id)
735
).where(
736
and_(
737
users.c.active == True,
738
orders.c.status.in_(['completed', 'shipped'])
739
)
740
).group_by(users.c.name).having(
741
func.count(orders.c.id) > 5
742
).order_by(desc(func.count(orders.c.id)))
743
```
744
745
### INSERT with RETURNING
746
747
```python
748
from sqlalchemy import insert
749
750
stmt = insert(users).values(
751
name='New User',
752
email='new@example.com'
753
).returning(users.c.id)
754
755
with engine.connect() as conn:
756
result = conn.execute(stmt)
757
new_id = result.scalar()
758
```
759
760
### Subquery Example
761
762
```python
763
# Subquery for filtering
764
subq = select(func.avg(users.c.age)).scalar_subquery()
765
766
stmt = select(users).where(users.c.age > subq)
767
768
# CTE example
769
users_cte = select(users.c.id, users.c.name).cte('users_cte')
770
771
stmt = select(users_cte.c.name).join(
772
orders, users_cte.c.id == orders.c.user_id
773
)
774
```