0
# Database and Connections
1
2
Database connection management, transaction handling, and backend-specific functionality. Peewee supports multiple database backends with consistent APIs while providing backend-specific optimizations and features.
3
4
## Capabilities
5
6
### Database Base Class
7
8
Core database functionality providing connection management, transaction control, and SQL execution capabilities shared across all database backends.
9
10
```python { .api }
11
class Database:
12
"""
13
Base database class providing core functionality.
14
"""
15
def __init__(self, database, **kwargs):
16
"""
17
Initialize database connection.
18
19
Parameters:
20
- database (str): Database name/path
21
- **kwargs: Backend-specific connection parameters
22
"""
23
24
def connect(self, reuse_if_open=False):
25
"""
26
Connect to the database.
27
28
Parameters:
29
- reuse_if_open (bool): Reuse existing connection
30
31
Returns:
32
Connection: Database connection object
33
"""
34
35
def close(self):
36
"""
37
Close the database connection.
38
39
Returns:
40
bool: True if connection was closed
41
"""
42
43
def is_closed(self):
44
"""
45
Check if database connection is closed.
46
47
Returns:
48
bool: True if connection is closed
49
"""
50
51
def connection(self):
52
"""
53
Get the current database connection.
54
55
Returns:
56
Connection: Current connection object
57
"""
58
59
def execute_sql(self, sql, params=None, commit=None):
60
"""
61
Execute raw SQL query.
62
63
Parameters:
64
- sql (str): SQL query string
65
- params (list): Query parameters
66
- commit (bool): Auto-commit transaction
67
68
Returns:
69
Cursor: Database cursor with results
70
"""
71
72
def begin(self):
73
"""
74
Begin a database transaction.
75
"""
76
77
def commit(self):
78
"""
79
Commit the current transaction.
80
"""
81
82
def rollback(self):
83
"""
84
Rollback the current transaction.
85
"""
86
87
def atomic(self, *args, **kwargs):
88
"""
89
Create atomic transaction context manager.
90
91
Returns:
92
Context manager for atomic operations
93
"""
94
95
def transaction(self, *args, **kwargs):
96
"""
97
Create transaction context manager.
98
99
Returns:
100
Context manager for transactions
101
"""
102
103
def savepoint(self, sid=None):
104
"""
105
Create savepoint context manager.
106
107
Parameters:
108
- sid (str): Savepoint identifier
109
110
Returns:
111
Context manager for savepoint
112
"""
113
114
def create_tables(self, models, **options):
115
"""
116
Create database tables for models.
117
118
Parameters:
119
- models (list): Model classes to create tables for
120
- **options: Creation options (safe, checkfirst, etc.)
121
"""
122
123
def drop_tables(self, models, **options):
124
"""
125
Drop database tables for models.
126
127
Parameters:
128
- models (list): Model classes to drop tables for
129
- **options: Drop options (safe, cascade, etc.)
130
"""
131
132
def truncate_tables(self, models, **options):
133
"""
134
Truncate database tables for models.
135
136
Parameters:
137
- models (list): Model classes to truncate
138
- **options: Truncate options
139
"""
140
141
def create_index(self, model_class, fields, unique=False, **kwargs):
142
"""
143
Create database index.
144
145
Parameters:
146
- model_class: Model class
147
- fields (list): Fields to index
148
- unique (bool): Create unique index
149
- **kwargs: Index options
150
"""
151
152
def drop_index(self, model_class, fields, **kwargs):
153
"""
154
Drop database index.
155
156
Parameters:
157
- model_class: Model class
158
- fields (list): Fields indexed
159
- **kwargs: Drop options
160
"""
161
```
162
163
### SQLite Database
164
165
SQLite database implementation providing file-based storage with extensive SQL support and performance optimizations.
166
167
```python { .api }
168
class SqliteDatabase(Database):
169
"""
170
SQLite database implementation.
171
"""
172
def __init__(self, database, **kwargs):
173
"""
174
Initialize SQLite database.
175
176
Parameters:
177
- database (str): Database file path (':memory:' for in-memory)
178
- pragmas (dict): SQLite PRAGMA settings
179
- timeout (int): Connection timeout in seconds
180
- check_same_thread (bool): Check thread safety
181
- isolation_level (str): Transaction isolation level
182
- **kwargs: Additional sqlite3 connection parameters
183
"""
184
185
def pragma(self, key, value=None, permanent=False):
186
"""
187
Get or set SQLite PRAGMA value.
188
189
Parameters:
190
- key (str): PRAGMA name
191
- value: PRAGMA value (None to get current)
192
- permanent (bool): Apply to all connections
193
194
Returns:
195
Current value if getting, None if setting
196
"""
197
198
def attach(self, database, name):
199
"""
200
Attach another database.
201
202
Parameters:
203
- database (str): Database file path
204
- name (str): Alias name for attached database
205
"""
206
207
def detach(self, name):
208
"""
209
Detach database.
210
211
Parameters:
212
- name (str): Alias name of attached database
213
"""
214
```
215
216
Usage example:
217
218
```python
219
from peewee import *
220
221
# Basic SQLite database
222
db = SqliteDatabase('app.db')
223
224
# In-memory database
225
memory_db = SqliteDatabase(':memory:')
226
227
# SQLite with pragmas
228
db = SqliteDatabase('app.db', pragmas={
229
'journal_mode': 'wal',
230
'cache_size': -64000, # 64MB cache
231
'foreign_keys': 1,
232
'ignore_check_constraints': 0,
233
'synchronous': 0
234
})
235
236
# Connect and use
237
db.connect()
238
db.pragma('foreign_keys', 1) # Enable foreign keys
239
db.close()
240
```
241
242
### PostgreSQL Database
243
244
PostgreSQL database implementation with support for advanced SQL features, JSON, arrays, and PostgreSQL-specific functionality.
245
246
```python { .api }
247
class PostgresqlDatabase(Database):
248
"""
249
PostgreSQL database implementation.
250
"""
251
def __init__(self, database, **kwargs):
252
"""
253
Initialize PostgreSQL database.
254
255
Parameters:
256
- database (str): Database name
257
- user (str): Username
258
- password (str): Password
259
- host (str): Host address (default: localhost)
260
- port (int): Port number (default: 5432)
261
- sslmode (str): SSL mode ('disable', 'require', etc.)
262
- **kwargs: Additional psycopg2 connection parameters
263
"""
264
265
def set_time_zone(self, timezone):
266
"""
267
Set session timezone.
268
269
Parameters:
270
- timezone (str): Timezone name
271
"""
272
```
273
274
Usage example:
275
276
```python
277
from peewee import *
278
279
# PostgreSQL database
280
db = PostgresqlDatabase(
281
'mydb',
282
user='postgres',
283
password='secret',
284
host='localhost',
285
port=5432
286
)
287
288
# PostgreSQL with SSL
289
db = PostgresqlDatabase(
290
'mydb',
291
user='postgres',
292
password='secret',
293
host='db.example.com',
294
sslmode='require'
295
)
296
```
297
298
### MySQL Database
299
300
MySQL/MariaDB database implementation with support for MySQL-specific features and optimizations.
301
302
```python { .api }
303
class MySQLDatabase(Database):
304
"""
305
MySQL/MariaDB database implementation.
306
"""
307
def __init__(self, database, **kwargs):
308
"""
309
Initialize MySQL database.
310
311
Parameters:
312
- database (str): Database name
313
- user (str): Username
314
- password (str): Password
315
- host (str): Host address (default: localhost)
316
- port (int): Port number (default: 3306)
317
- charset (str): Character set (default: utf8mb4)
318
- sql_mode (str): SQL mode settings
319
- **kwargs: Additional connection parameters
320
"""
321
```
322
323
Usage example:
324
325
```python
326
from peewee import *
327
328
# MySQL database
329
db = MySQLDatabase(
330
'mydb',
331
user='root',
332
password='secret',
333
host='localhost',
334
port=3306,
335
charset='utf8mb4'
336
)
337
```
338
339
### Database Proxy
340
341
Database proxy for late binding, allowing models to be defined before the actual database instance is available.
342
343
```python { .api }
344
class DatabaseProxy:
345
"""
346
Proxy object for late database binding.
347
"""
348
def initialize(self, database):
349
"""
350
Initialize with actual database instance.
351
352
Parameters:
353
- database: Database instance to proxy to
354
"""
355
356
def __getattr__(self, attr):
357
"""
358
Proxy attribute access to underlying database.
359
"""
360
361
def __setattr__(self, attr, value):
362
"""
363
Proxy attribute setting to underlying database.
364
"""
365
```
366
367
Usage example:
368
369
```python
370
from peewee import *
371
372
# Define proxy
373
database = DatabaseProxy()
374
375
class User(Model):
376
username = CharField()
377
378
class Meta:
379
database = database
380
381
# Later, initialize with actual database
382
actual_db = SqliteDatabase('app.db')
383
database.initialize(actual_db)
384
```
385
386
### Base Proxy Class
387
388
Generic proxy implementation that DatabaseProxy extends.
389
390
```python { .api }
391
class Proxy:
392
"""
393
Base proxy class for creating placeholders for objects.
394
"""
395
def __init__(self):
396
"""Initialize empty proxy with callback support."""
397
398
def initialize(self, obj):
399
"""
400
Initialize proxy with target object.
401
402
Parameters:
403
- obj: Object to proxy to
404
"""
405
406
def attach_callback(self, callback):
407
"""
408
Attach callback to be called when proxy is initialized.
409
410
Parameters:
411
- callback: Function to call with target object
412
"""
413
```
414
415
### Transaction Management
416
417
Context managers and decorators for managing database transactions, atomicity, and savepoints.
418
419
```python { .api }
420
def atomic(db=None):
421
"""
422
Decorator/context manager for atomic operations.
423
424
Parameters:
425
- db: Database instance (uses default if None)
426
427
Usage as decorator:
428
@atomic()
429
def transfer_funds(from_account, to_account, amount):
430
...
431
432
Usage as context manager:
433
with atomic():
434
...
435
"""
436
437
def transaction(db=None):
438
"""
439
Decorator/context manager for transactions.
440
441
Parameters:
442
- db: Database instance (uses default if None)
443
"""
444
445
def savepoint(db=None, sid=None):
446
"""
447
Create savepoint context manager.
448
449
Parameters:
450
- db: Database instance
451
- sid (str): Savepoint identifier
452
"""
453
```
454
455
Usage examples:
456
457
```python
458
from peewee import *
459
460
db = SqliteDatabase('app.db')
461
462
# Atomic decorator
463
@atomic(db)
464
def create_user_with_profile(username, email, bio):
465
user = User.create(username=username, email=email)
466
UserProfile.create(user=user, bio=bio)
467
return user
468
469
# Atomic context manager
470
with atomic(db):
471
user = User.create(username='john', email='john@example.com')
472
user.is_active = True
473
user.save()
474
475
# Nested transactions with savepoints
476
with transaction(db):
477
user = User.create(username='jane', email='jane@example.com')
478
479
with savepoint(db):
480
# This might fail
481
try:
482
UserProfile.create(user=user, bio='A very long bio...')
483
except IntegrityError:
484
# Rollback to savepoint, user creation still valid
485
pass
486
487
user.is_active = True
488
user.save()
489
```
490
491
### Connection Management
492
493
Advanced connection handling, pooling configuration, and connection lifecycle management.
494
495
```python { .api }
496
class Database:
497
def init(self, database, **kwargs):
498
"""
499
Initialize database with new parameters.
500
501
Parameters:
502
- database (str): Database identifier
503
- **kwargs: Connection parameters
504
"""
505
506
def bind(self, models, bind_refs=True, bind_backrefs=True):
507
"""
508
Bind models to this database.
509
510
Parameters:
511
- models (list): Model classes to bind
512
- bind_refs (bool): Bind foreign key references
513
- bind_backrefs (bool): Bind back-references
514
"""
515
516
def bind_ctx(self, models, bind_refs=True, bind_backrefs=True):
517
"""
518
Context manager for temporary model binding.
519
520
Parameters:
521
- models (list): Model classes to bind
522
- bind_refs (bool): Bind foreign key references
523
- bind_backrefs (bool): Bind back-references
524
"""
525
```
526
527
## Schema Management
528
529
Database schema operations including table creation, modification, and introspection.
530
531
```python { .api }
532
class SchemaManager:
533
"""
534
Database schema management and DDL operations.
535
"""
536
def __init__(self, database):
537
"""
538
Parameters:
539
- database: Database instance to manage
540
"""
541
542
def create_table(self, model_class, safe=True, **options):
543
"""
544
Create table for model.
545
546
Parameters:
547
- model_class: Model to create table for
548
- safe (bool): Don't raise error if table exists
549
- **options: Creation options
550
"""
551
552
def drop_table(self, model_class, safe=True, cascade=False, **options):
553
"""
554
Drop table for model.
555
556
Parameters:
557
- model_class: Model to drop table for
558
- safe (bool): Don't raise error if table doesn't exist
559
- cascade (bool): Drop dependent objects
560
- **options: Drop options
561
"""
562
563
def truncate_table(self, model_class, restart_identity=False, cascade=False):
564
"""
565
Truncate table for model.
566
567
Parameters:
568
- model_class: Model to truncate
569
- restart_identity (bool): Restart sequence counters
570
- cascade (bool): Truncate dependent tables
571
"""
572
573
def create_index(self, model_class, fields, unique=False, safe=True, **kwargs):
574
"""
575
Create index on model fields.
576
577
Parameters:
578
- model_class: Model class
579
- fields (list): Fields to index
580
- unique (bool): Create unique index
581
- safe (bool): Don't raise error if index exists
582
- **kwargs: Index options
583
"""
584
585
def drop_index(self, model_class, fields, **kwargs):
586
"""
587
Drop index from model fields.
588
589
Parameters:
590
- model_class: Model class
591
- fields (list): Fields indexed
592
- **kwargs: Drop options
593
"""
594
595
def create_foreign_key(self, model_class, field, constraint=None):
596
"""
597
Create foreign key constraint.
598
599
Parameters:
600
- model_class: Model class
601
- field: Foreign key field
602
- constraint (str): Constraint name
603
"""
604
605
def create_sequence(self, sequence_name):
606
"""
607
Create database sequence.
608
609
Parameters:
610
- sequence_name (str): Name of sequence to create
611
"""
612
613
def drop_sequence(self, sequence_name):
614
"""
615
Drop database sequence.
616
617
Parameters:
618
- sequence_name (str): Name of sequence to drop
619
"""
620
621
class Index:
622
"""
623
Database index definition.
624
"""
625
def __init__(self, name, table, expressions, unique=False, safe=True, where=None, **kwargs):
626
"""
627
Parameters:
628
- name (str): Index name
629
- table (str): Table name
630
- expressions (list): Column expressions
631
- unique (bool): Unique constraint
632
- safe (bool): Create if not exists
633
- where: Partial index condition
634
- **kwargs: Database-specific options
635
"""
636
637
class ModelIndex:
638
"""
639
Model-specific index definition.
640
"""
641
def __init__(self, model, fields, unique=False, **kwargs):
642
"""
643
Parameters:
644
- model: Model class
645
- fields (list): Field names or expressions
646
- unique (bool): Unique constraint
647
- **kwargs: Index options
648
"""
649
```
650
651
## Error Handling
652
653
Database-specific error handling and exception management:
654
655
```python
656
from peewee import *
657
658
try:
659
db.connect()
660
# Database operations
661
except OperationalError as e:
662
print(f"Database connection failed: {e}")
663
except ProgrammingError as e:
664
print(f"SQL syntax error: {e}")
665
except IntegrityError as e:
666
print(f"Constraint violation: {e}")
667
finally:
668
if not db.is_closed():
669
db.close()
670
```