0
# Database Dialects
1

2
Database-specific implementations for PostgreSQL, MySQL, SQLite, Oracle, SQL Server with specialized types, functions, and features for each database. Dialects provide database-specific SQL generation and type handling.
3

4
## Capabilities
5

6
### PostgreSQL Dialect
7

8
Advanced PostgreSQL-specific types, operators, and features.
9

10
```python { .api }
11
# Import PostgreSQL-specific types
12
from sqlalchemy.dialects.postgresql import ARRAY, HSTORE, JSON, JSONB, UUID
13
from sqlalchemy.dialects.postgresql import INET, CIDR, MACADDR
14
from sqlalchemy.dialects.postgresql import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE
15
from sqlalchemy.dialects.postgresql import TSVECTOR, TSQUERY
16
from sqlalchemy.dialects.postgresql import ExcludeConstraint
17

18
class ARRAY:
19
    """PostgreSQL array type with indexing and operators."""
20
    
21
    def __init__(self, item_type, as_tuple=False, dimensions=None, zero_indexes=False):
22
        """
23
        Create PostgreSQL array type.
24
        
25
        Parameters:
26
        - item_type: element type for array
27
        - as_tuple: bool, return tuples instead of lists
28
        - dimensions: int, number of array dimensions
29
        - zero_indexes: bool, use zero-based indexing
30
        """
31

32
class HSTORE:
33
    """PostgreSQL key-value store type."""
34
    
35
    def __init__(self, text_type=None):
36
        """
37
        Create HSTORE type.
38
        
39
        Parameters:
40
        - text_type: underlying text type for keys/values
41
        """
42

43
class JSON:
44
    """PostgreSQL JSON type with operators."""
45
    
46
    def __init__(self, none_as_null=False, astext_type=None):
47
        """
48
        Create JSON type.
49
        
50
        Parameters:
51
        - none_as_null: bool, store None as SQL NULL
52
        - astext_type: type for ->> text extraction
53
        """
54

55
class JSONB:
56
    """PostgreSQL binary JSON type with indexing."""
57
    
58
    def __init__(self, none_as_null=False, astext_type=None):
59
        """
60
        Create JSONB type.
61
        
62
        Parameters:
63
        - none_as_null: bool, store None as SQL NULL  
64
        - astext_type: type for ->> text extraction
65
        """
66

67
class UUID:
68
    """PostgreSQL UUID type."""
69
    
70
    def __init__(self, as_uuid=True):
71
        """
72
        Create UUID type.
73
        
74
        Parameters:
75
        - as_uuid: bool, return UUID objects vs strings
76
        """
77

78
# Network address types
79
class INET:
80
    """PostgreSQL IP address type."""
81

82
class CIDR:
83
    """PostgreSQL network address type."""
84

85
class MACADDR:
86
    """PostgreSQL MAC address type."""
87

88
# Range types
89
class INT4RANGE:
90
    """PostgreSQL integer range type."""
91
    
92
    def __init__(self, bounds="[)"):
93
        """
94
        Create integer range type.
95
        
96
        Parameters:
97
        - bounds: str, default bounds ("[)", "(]", "[]", "()")
98
        """
99

100
class INT8RANGE:
101
    """PostgreSQL big integer range type."""
102

103
class NUMRANGE:
104
    """PostgreSQL numeric range type."""
105

106
class DATERANGE:
107
    """PostgreSQL date range type."""
108

109
class TSRANGE:
110
    """PostgreSQL timestamp range type."""
111

112
class TSTZRANGE:
113
    """PostgreSQL timestamp with timezone range type."""
114

115
# Full-text search types
116
class TSVECTOR:
117
    """PostgreSQL text search vector type."""
118

119
class TSQUERY:
120
    """PostgreSQL text search query type."""
121

122
# PostgreSQL functions
123
def to_tsvector(config, text):
124
    """Convert text to search vector."""
125

126
def to_tsquery(config, query):
127
    """Convert text to search query."""
128

129
def plainto_tsquery(config, query):
130
    """Convert plain text to search query."""
131

132
# PostgreSQL constraints
133
class ExcludeConstraint:
134
    """PostgreSQL EXCLUDE constraint."""
135
    
136
    def __init__(self, *elements, **kwargs):
137
        """
138
        Create exclude constraint.
139
        
140
        Parameters:
141
        - elements: (column, operator) tuples
142
        - name: str, constraint name
143
        - using: str, index method (default 'gist')
144
        """
145
```
146

147
### MySQL Dialect
148

149
MySQL-specific types, functions, and SQL features.
150

151
```python { .api }
152
# Import MySQL-specific types
153
from sqlalchemy.dialects.mysql import MEDIUMINT, LONGTEXT, MEDIUMTEXT, TINYTEXT
154
from sqlalchemy.dialects.mysql import SET, ENUM, YEAR, BIT
155
from sqlalchemy.dialects.mysql import Insert
156

157
class MEDIUMINT:
158
    """MySQL medium integer type (3 bytes)."""
159
    
160
    def __init__(self, display_width=None, unsigned=False, zerofill=False):
161
        """
162
        Create MEDIUMINT type.
163
        
164
        Parameters:
165
        - display_width: int, display width
166
        - unsigned: bool, unsigned integer
167
        - zerofill: bool, zero-fill display
168
        """
169

170
class LONGTEXT:
171
    """MySQL long text type (4GB)."""
172

173
class MEDIUMTEXT:
174
    """MySQL medium text type (16MB)."""
175

176
class TINYTEXT:
177
    """MySQL tiny text type (255 bytes)."""
178

179
class SET:
180
    """MySQL SET type for multiple choice values."""
181
    
182
    def __init__(self, *values, **kwargs):
183
        """
184
        Create SET type.
185
        
186
        Parameters:
187
        - values: allowed set values
188
        - charset: str, character set
189
        - collation: str, collation
190
        """
191

192
class ENUM:
193
    """MySQL ENUM type."""
194
    
195
    def __init__(self, *enums, **kwargs):
196
        """
197
        Create ENUM type.
198
        
199
        Parameters:
200
        - enums: enumeration values
201
        - charset: str, character set
202
        - collation: str, collation
203
        """
204

205
class YEAR:
206
    """MySQL YEAR type."""
207
    
208
    def __init__(self, display_width=None):
209
        """
210
        Create YEAR type.
211
        
212
        Parameters:
213
        - display_width: int, display width (2 or 4)
214
        """
215

216
class BIT:
217
    """MySQL BIT type."""
218
    
219
    def __init__(self, length=None):
220
        """
221
        Create BIT type.
222
        
223
        Parameters:
224
        - length: int, bit length (1-64)
225
        """
226

227
class Insert:
228
    """MySQL-specific INSERT with ON DUPLICATE KEY UPDATE."""
229
    
230
    def on_duplicate_key_update(self, **kwargs):
231
        """
232
        Add ON DUPLICATE KEY UPDATE clause.
233
        
234
        Parameters:
235
        - kwargs: column=value pairs for updates
236
        
237
        Returns:
238
        Insert: Modified INSERT with ON DUPLICATE KEY UPDATE
239
        """
240

241
# MySQL functions
242
def match(*columns, against=None, in_boolean_mode=False, in_natural_language_mode=False):
243
    """
244
    MySQL MATCH() AGAINST() full-text search.
245
    
246
    Parameters:
247
    - columns: columns to search
248
    - against: search expression
249
    - in_boolean_mode: bool, use boolean mode
250
    - in_natural_language_mode: bool, use natural language mode
251
    
252
    Returns:
253
    Function: MATCH function call
254
    """
255
```
256

257
### SQLite Dialect
258

259
SQLite-specific features and limitations handling.
260

261
```python { .api }
262
# Import SQLite-specific types
263
from sqlalchemy.dialects.sqlite import JSON, Insert
264

265
class JSON:
266
    """SQLite JSON type (SQLite 3.38+)."""
267
    
268
    def __init__(self, none_as_null=False):
269
        """
270
        Create SQLite JSON type.
271
        
272
        Parameters:
273
        - none_as_null: bool, store None as SQL NULL
274
        """
275

276
class Insert:
277
    """SQLite-specific INSERT with ON CONFLICT handling."""
278
    
279
    def on_conflict_do_update(self, index_elements=None, set_=None, **kwargs):
280
        """
281
        Add ON CONFLICT DO UPDATE clause.
282
        
283
        Parameters:
284
        - index_elements: conflicting columns/expressions
285
        - set_: dictionary of column updates
286
        
287
        Returns:
288
        Insert: Modified INSERT with ON CONFLICT DO UPDATE
289
        """
290
    
291
    def on_conflict_do_nothing(self, index_elements=None):
292
        """
293
        Add ON CONFLICT DO NOTHING clause.
294
        
295
        Parameters:
296
        - index_elements: conflicting columns/expressions
297
        
298
        Returns:
299
        Insert: Modified INSERT with ON CONFLICT DO NOTHING
300
        """
301
```
302

303
### Oracle Dialect
304

305
Oracle-specific types, functions, and SQL constructs.
306

307
```python { .api }
308
# Import Oracle-specific types
309
from sqlalchemy.dialects.oracle import NUMBER, LONG, RAW, NCLOB, BLOB, BFILE
310
from sqlalchemy.dialects.oracle import ROWNUM
311

312
class NUMBER:
313
    """Oracle NUMBER type with precision and scale."""
314
    
315
    def __init__(self, precision=None, scale=None, asdecimal=None):
316
        """
317
        Create Oracle NUMBER type.
318
        
319
        Parameters:
320
        - precision: int, total digits
321
        - scale: int, digits after decimal
322
        - asdecimal: bool, return Decimal objects
323
        """
324

325
class LONG:
326
    """Oracle LONG type for large text."""
327

328
class RAW:
329
    """Oracle RAW type for binary data."""
330
    
331
    def __init__(self, length=None):
332
        """
333
        Create RAW type.
334
        
335
        Parameters:
336
        - length: int, maximum byte length
337
        """
338

339
class NCLOB:
340
    """Oracle NCLOB type for large Unicode text."""
341

342
class BLOB:
343
    """Oracle BLOB type for binary large objects."""
344

345
class BFILE:
346
    """Oracle BFILE type for external file references."""
347

348
class ROWNUM:
349
    """Oracle ROWNUM pseudocolumn."""
350
```
351

352
### SQL Server Dialect
353

354
Microsoft SQL Server specific types and features.
355

356
```python { .api }
357
# Import SQL Server-specific types
358
from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER, IMAGE, MONEY, SMALLMONEY
359
from sqlalchemy.dialects.mssql import SQL_VARIANT, ROWVERSION, try_cast
360

361
class UNIQUEIDENTIFIER:
362
    """SQL Server GUID/UUID type."""
363

364
class IMAGE:
365
    """SQL Server IMAGE type (deprecated, use VARBINARY(MAX))."""
366

367
class MONEY:
368
    """SQL Server MONEY type for currency."""
369

370
class SMALLMONEY:
371
    """SQL Server SMALLMONEY type for smaller currency values."""
372

373
class SQL_VARIANT:
374
    """SQL Server sql_variant type for mixed data types."""
375

376
class ROWVERSION:
377
    """SQL Server rowversion/timestamp type for concurrency."""
378

379
def try_cast(expression, type_):
380
    """
381
    SQL Server TRY_CAST function.
382
    
383
    Parameters:
384
    - expression: expression to cast
385
    - type_: target type
386
    
387
    Returns:
388
    Function: TRY_CAST function call
389
    """
390
```
391

392
### Dialect-Specific Features
393

394
Database-specific SQL generation and optimization features.
395

396
```python { .api }
397
# PostgreSQL-specific query features
398
from sqlalchemy.dialects.postgresql import aggregate_order_by, array_agg
399

400
def aggregate_order_by(expr, *order_by):
401
    """
402
    PostgreSQL aggregate ORDER BY clause.
403
    
404
    Parameters:
405
    - expr: aggregate expression
406
    - order_by: ordering expressions
407
    
408
    Returns:
409
    AggregateOrderBy: Ordered aggregate expression
410
    """
411

412
def array_agg(*args, **kwargs):
413
    """
414
    PostgreSQL ARRAY_AGG function.
415
    
416
    Parameters:
417
    - args: expressions to aggregate
418
    - order_by: ordering for aggregation
419
    
420
    Returns:
421
    Function: ARRAY_AGG function call
422
    """
423

424
# MySQL-specific features
425
from sqlalchemy.dialects.mysql import match
426

427
# SQL Server-specific features  
428
from sqlalchemy.dialects.mssql import try_cast
429
```
430

431
## Usage Examples
432

433
### PostgreSQL Arrays and JSON
434

435
```python
436
from sqlalchemy.dialects.postgresql import ARRAY, JSONB, UUID
437
from sqlalchemy import Table, Column, Integer, String
438

439
# Array usage
440
tags_table = Table('articles', metadata,
441
    Column('id', UUID, primary_key=True),
442
    Column('title', String(200)),
443
    Column('tags', ARRAY(String(50))),
444
    Column('ratings', ARRAY(Integer, dimensions=2)),  # 2D array
445
    Column('metadata', JSONB)
446
)
447

448
# Query arrays
449
from sqlalchemy import select, func
450

451
stmt = select(tags_table).where(
452
    tags_table.c.tags.contains(['python', 'sqlalchemy'])
453
)
454

455
# JSON operations
456
stmt = select(tags_table).where(
457
    tags_table.c.metadata['author']['name'].astext == 'John Doe'
458
)
459
```
460

461
### MySQL ON DUPLICATE KEY UPDATE
462

463
```python
464
from sqlalchemy.dialects.mysql import Insert
465

466
stmt = Insert(users).values(
467
    id=1,
468
    name='John Doe',
469
    email='john@example.com',
470
    login_count=1
471
)
472

473
stmt = stmt.on_duplicate_key_update(
474
    name=stmt.inserted.name,
475
    email=stmt.inserted.email,
476
    login_count=users.c.login_count + 1
477
)
478

479
with engine.connect() as conn:
480
    conn.execute(stmt)
481
```
482

483
### SQLite ON CONFLICT Handling
484

485
```python
486
from sqlalchemy.dialects.sqlite import Insert
487

488
stmt = Insert(users).values(
489
    username='johndoe',
490
    email='john@example.com'
491
)
492

493
# Ignore conflicts
494
stmt = stmt.on_conflict_do_nothing()
495

496
# Update on conflict
497
stmt = stmt.on_conflict_do_update(
498
    index_elements=['username'],
499
    set_={'email': stmt.excluded.email}
500
)
501
```
502

503
### PostgreSQL Full-Text Search
504

505
```python
506
from sqlalchemy.dialects.postgresql import TSVECTOR, TSQUERY
507
from sqlalchemy.dialects.postgresql import to_tsvector, to_tsquery
508

509
documents = Table('documents', metadata,
510
    Column('id', Integer, primary_key=True),
511
    Column('title', String(200)),
512
    Column('content', Text),
513
    Column('search_vector', TSVECTOR)
514
)
515

516
# Create GIN index for full-text search
517
from sqlalchemy import Index
518
search_idx = Index('idx_search_vector', documents.c.search_vector, 
519
                   postgresql_using='gin')
520

521
# Full-text search query
522
stmt = select(documents).where(
523
    documents.c.search_vector.match('python & sqlalchemy')
524
)
525

526
# Update search vector
527
stmt = documents.update().values(
528
    search_vector=to_tsvector('english', 
529
                              func.coalesce(documents.c.title, '') + ' ' + 
530
                              func.coalesce(documents.c.content, ''))
531
)
532
```
533

534
### Oracle-Specific Features
535

536
```python
537
from sqlalchemy.dialects.oracle import NUMBER, ROWNUM
538

539
accounts = Table('accounts', metadata,
540
    Column('id', NUMBER(10), primary_key=True),
541
    Column('balance', NUMBER(15, 2)),
542
    Column('account_number', String(20))
543
)
544

545
# Use ROWNUM for pagination (Oracle < 12c)
546
stmt = select(accounts).where(ROWNUM <= 10)
547

548
# Oracle 12c+ pagination
549
stmt = select(accounts).order_by(accounts.c.id).offset(10).limit(10)
550
```
551

552
### SQL Server Features
553

554
```python
555
from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER, try_cast
556

557
orders = Table('orders', metadata,
558
    Column('id', UNIQUEIDENTIFIER, primary_key=True),
559
    Column('amount_text', String(50)),
560
    Column('customer_id', Integer)
561
)
562

563
# Use TRY_CAST for safe type conversion
564
stmt = select(
565
    orders.c.id,
566
    try_cast(orders.c.amount_text, Numeric(10, 2)).label('amount')
567
).where(
568
    try_cast(orders.c.amount_text, Numeric(10, 2)) > 100
569
)
570
```