0
# Database and Entity Management
1
2
Core classes for defining database schemas and entity relationships. The Database class serves as the central registry and connection manager, while Entity provides the base for all ORM-mapped classes with automatic query generation capabilities.
3
4
## Capabilities
5
6
### Database Class
7
8
The Database class is the central component that manages entity definitions, database connections, and schema generation. Each Database instance represents a complete database schema.
9
10
```python { .api }
11
class Database:
12
def __init__(self, *args, **kwargs):
13
"""Initialize database connection configuration.
14
15
Args:
16
*args: Positional arguments - first arg can be provider name or dict of connection params
17
**kwargs: Connection parameters including:
18
provider: Database provider ('sqlite', 'mysql', 'postgresql', 'oracle', 'cockroach')
19
filename: Database file path (SQLite only)
20
host: Database host (network databases)
21
port: Database port (network databases)
22
user: Database username (network databases)
23
password: Database password (network databases)
24
database: Database name (network databases)
25
"""
26
27
def bind(self, provider, **kwargs):
28
"""Bind database to specific provider and connection parameters.
29
30
Args:
31
provider: Database provider name
32
**kwargs: Connection parameters (host, port, user, password, etc.)
33
"""
34
35
def generate_mapping(self, check_tables=True, create_tables=False):
36
"""Generate database mapping from entity definitions.
37
38
Args:
39
check_tables: Validate existing table structures
40
create_tables: Create missing tables automatically
41
"""
42
43
def create_tables(self):
44
"""Create all tables for defined entities."""
45
46
def drop_all_tables(self, with_all_data=False):
47
"""Drop all tables from database.
48
49
Args:
50
with_all_data: If True, drop tables even if they contain data
51
"""
52
53
def drop_table(self, table_name, if_exists=False, with_all_data=False):
54
"""Drop specific table from database.
55
56
Args:
57
table_name: Name of table to drop
58
if_exists: Don't raise error if table doesn't exist
59
with_all_data: Drop table even if it contains data
60
"""
61
62
def check_tables(self):
63
"""Check that all entity tables exist and have correct structure."""
64
65
def get(self, sql, globals=None, locals=None):
66
"""Execute SQL query and get results.
67
68
Args:
69
sql: SQL query string or AST
70
globals: Global variables for SQL execution
71
locals: Local variables for SQL execution
72
73
Returns:
74
Query results
75
"""
76
77
def exists(self, sql, globals=None, locals=None):
78
"""Check if SQL query returns any results.
79
80
Args:
81
sql: SQL query string or AST
82
globals: Global variables for SQL execution
83
locals: Local variables for SQL execution
84
85
Returns:
86
bool: True if query returns results
87
"""
88
89
def insert(self, table_name, returning=None, **kwargs):
90
"""Insert row into table.
91
92
Args:
93
table_name: Name of table
94
returning: Column to return after insert
95
**kwargs: Column values to insert
96
97
Returns:
98
Inserted row ID or specified returning value
99
"""
100
101
def set_perms_for(self, *entities):
102
"""Set permissions for specified entities.
103
104
Args:
105
*entities: Entity classes to set permissions for
106
"""
107
108
# Properties
109
@property
110
def last_sql(self):
111
"""Get the last SQL query executed (str)."""
112
113
@property
114
def local_stats(self):
115
"""Get local query statistics (dict)."""
116
117
@property
118
def global_stats(self):
119
"""Get global query statistics (dict)."""
120
```
121
122
### Entity Base Class
123
124
The Entity metaclass provides the foundation for all database-mapped classes. Entities automatically receive query methods and database interaction capabilities.
125
126
```python { .api }
127
class Entity:
128
_database_: Database # Associated database instance
129
_table_: str # Database table name
130
_pk_columns_: List[str] # Primary key column names
131
_pk_val_: Any # Primary key value
132
133
def __init__(self, **kwargs):
134
"""Create new entity instance.
135
136
Args:
137
**kwargs: Attribute values for entity creation
138
"""
139
140
def delete(self):
141
"""Delete this entity from database."""
142
143
def flush(self):
144
"""Flush changes to database without committing transaction."""
145
146
def get_pk(self):
147
"""Get primary key value.
148
149
Returns:
150
Primary key value (single value or tuple for composite keys)
151
"""
152
153
def set(self, **kwargs):
154
"""Update entity attributes.
155
156
Args:
157
**kwargs: Attribute values to update
158
"""
159
160
def __lt__(self, other):
161
"""Less than comparison based on primary key."""
162
163
def __le__(self, other):
164
"""Less than or equal comparison based on primary key."""
165
166
def __gt__(self, other):
167
"""Greater than comparison based on primary key."""
168
169
def __ge__(self, other):
170
"""Greater than or equal comparison based on primary key."""
171
172
@classmethod
173
def get(cls, *args, **kwargs):
174
"""Get single entity by primary key or conditions.
175
176
Args:
177
*args: Primary key values
178
**kwargs: Attribute conditions
179
180
Returns:
181
Entity instance
182
183
Raises:
184
ObjectNotFound: No entity matches criteria
185
MultipleObjectsFoundError: Multiple entities match criteria
186
"""
187
188
@classmethod
189
def get_for_update(cls, *args, **kwargs):
190
"""Get entity with row lock for update.
191
192
Args:
193
*args: Primary key values
194
**kwargs: Attribute conditions
195
196
Returns:
197
Entity instance with row lock
198
"""
199
200
@classmethod
201
def exists(cls, *args, **kwargs):
202
"""Check if entity exists.
203
204
Args:
205
*args: Primary key values or lambda expression
206
**kwargs: Attribute conditions
207
208
Returns:
209
bool: True if entity exists
210
"""
211
212
@classmethod
213
def select(cls, lambda_expr=None):
214
"""Select entities matching criteria.
215
216
Args:
217
lambda_expr: Lambda expression for filtering
218
219
Returns:
220
Query object with matching entities
221
"""
222
223
@classmethod
224
def select_random(cls, limit):
225
"""Select random entities.
226
227
Args:
228
limit: Number of random entities to return
229
230
Returns:
231
List of random entity instances
232
"""
233
234
def to_dict(self, only=None, exclude=None, with_collections=True,
235
with_lazy=True, related_objects=False):
236
"""Convert entity to dictionary.
237
238
Args:
239
only: List of attributes to include
240
exclude: List of attributes to exclude
241
with_collections: Include collection attributes
242
with_lazy: Include lazy-loaded attributes
243
related_objects: Include related entity objects
244
245
Returns:
246
dict: Entity data as dictionary
247
"""
248
```
249
250
## Usage Examples
251
252
### Basic Database Setup
253
254
```python
255
from pony.orm import *
256
257
# Create database instance
258
db = Database()
259
260
# Define entities
261
class Person(db.Entity):
262
name = Required(str)
263
age = Optional(int)
264
email = Optional(str, unique=True)
265
266
class Order(db.Entity):
267
date = Required(datetime)
268
person = Required(Person)
269
total = Required(float)
270
271
# Bind to SQLite database
272
db.bind('sqlite', filename='example.db')
273
274
# Generate database schema
275
db.generate_mapping(create_tables=True)
276
```
277
278
### Working with Entities
279
280
```python
281
with db_session:
282
# Create new entities
283
person = Person(name="Alice", age=25, email="alice@example.com")
284
order = Order(date=datetime.now(), person=person, total=149.99)
285
286
# Get entity by primary key
287
alice = Person[1] # or Person.get(id=1)
288
289
# Get entity by unique attribute
290
alice = Person.get(email="alice@example.com")
291
292
# Update entity
293
alice.age = 26
294
alice.set(age=26) # alternative syntax
295
296
# Check if entity exists
297
if Person.exists(name="Bob"):
298
print("Bob exists")
299
300
# Convert to dictionary
301
person_data = alice.to_dict()
302
303
# Delete entity
304
alice.delete()
305
```
306
307
### Advanced Database Operations
308
309
```python
310
with db_session:
311
# Execute raw SQL
312
result = db.execute("SELECT COUNT(*) FROM Person")
313
314
# Get entities with conditions
315
young_people = Person.select(lambda p: p.age < 30)
316
317
# Random selection
318
random_people = Person.select_random(5)
319
320
# Bulk operations
321
Person.select(lambda p: p.age < 18).delete()
322
323
# Transaction control
324
try:
325
# ... database operations
326
commit()
327
except Exception:
328
rollback()
329
raise
330
```