0
# Debugging and Utilities
1
2
Tools for debugging SQL queries, inspecting schemas, and configuring runtime behavior. Essential for development and troubleshooting database operations in Pony ORM applications.
3
4
## Capabilities
5
6
### SQL Debugging Functions
7
8
Functions for monitoring and debugging generated SQL queries and their execution.
9
10
```python { .api }
11
def set_sql_debug(debug=True, show_values=None):
12
"""Configure SQL debugging with fine-grained control.
13
14
Args:
15
debug: Enable/disable SQL query logging (default: True)
16
show_values: Show parameter values in debug output (default: None)
17
- True: Always show values
18
- False: Never show values
19
- None: Show values based on debug level
20
21
Usage:
22
set_sql_debug(True) # Enable SQL logging
23
set_sql_debug(True, show_values=True) # Show SQL with parameters
24
set_sql_debug(False) # Disable SQL logging
25
"""
26
27
def sql_debug(value):
28
"""Legacy function for enabling/disabling SQL debugging.
29
30
Args:
31
value: Boolean to enable/disable SQL debugging
32
33
Note: Deprecated - use set_sql_debug() instead for better control
34
"""
35
36
class sql_debugging:
37
"""Context manager for temporary SQL debugging activation.
38
39
Usage:
40
with sql_debugging:
41
# SQL queries will be printed during this block
42
users = select(u for u in User if u.active)
43
"""
44
45
def __enter__(self):
46
"""Enter debugging context."""
47
48
def __exit__(self, exc_type, exc_val, exc_tb):
49
"""Exit debugging context and restore previous setting."""
50
```
51
52
### Schema Inspection and Display
53
54
Functions for inspecting database schemas and pretty-printing query results.
55
56
```python { .api }
57
def show(entity_or_query):
58
"""Pretty-print entity schema or query results for debugging.
59
60
Args:
61
entity_or_query: Entity class, entity instance, or query object
62
63
Usage:
64
show(User) # Display User entity schema
65
show(user_instance) # Display entity instance data
66
show(select(u for u in User)) # Display query results
67
"""
68
```
69
70
## Usage Examples
71
72
### Basic SQL Debugging
73
74
```python
75
from pony.orm import *
76
77
db = Database()
78
79
class User(db.Entity):
80
name = Required(str)
81
email = Required(str, unique=True)
82
age = Optional(int)
83
84
db.bind('sqlite', filename='debug_example.db')
85
db.generate_mapping(create_tables=True)
86
87
# Enable SQL debugging globally
88
set_sql_debug(True)
89
90
with db_session:
91
# All SQL queries will now be printed
92
user = User(name="Alice", email="alice@example.com", age=25)
93
# Prints: INSERT INTO User (name, email, age) VALUES (?, ?, ?)
94
95
users = select(u for u in User if u.age > 18)
96
# Prints: SELECT u.id, u.name, u.email, u.age FROM User u WHERE u.age > ?
97
98
adult_count = count(u for u in User if u.age >= 18)
99
# Prints: SELECT COUNT(*) FROM User u WHERE u.age >= ?
100
101
# Disable SQL debugging
102
set_sql_debug(False)
103
104
with db_session:
105
# No SQL output
106
more_users = select(u for u in User)
107
```
108
109
### Advanced SQL Debugging with Parameters
110
111
```python
112
# Enable debugging with parameter values
113
set_sql_debug(True, show_values=True)
114
115
with db_session:
116
# Query with parameters - values will be shown
117
young_users = select(u for u in User if u.age < 30)
118
# Prints: SELECT u.id, u.name, u.email, u.age FROM User u WHERE u.age < ?
119
# Parameters: [30]
120
121
user = User.get(email="alice@example.com")
122
# Prints: SELECT u.id, u.name, u.email, u.age FROM User u WHERE u.email = ?
123
# Parameters: ['alice@example.com']
124
125
# Complex query with multiple parameters
126
filtered_users = select(u for u in User
127
if u.age >= 18 and u.name.startswith("A"))
128
# Shows full SQL with all parameter values
129
130
# Hide parameter values for security
131
set_sql_debug(True, show_values=False)
132
133
with db_session:
134
# SQL shown but parameters hidden
135
user = User.get(email="sensitive@example.com")
136
# Prints: SELECT u.id, u.name, u.email, u.age FROM User u WHERE u.email = ?
137
# (no parameter values shown)
138
```
139
140
### Temporary Debugging with Context Manager
141
142
```python
143
# Normal operations without debug output
144
with db_session:
145
user1 = User(name="Bob", email="bob@example.com")
146
147
# Temporary debugging for specific operations
148
with sql_debugging:
149
with db_session:
150
# Only these queries will show SQL
151
user2 = User(name="Charlie", email="charlie@example.com")
152
users = select(u for u in User if u.name.startswith("C"))
153
154
# Complex query to debug
155
result = select((u.name, u.age) for u in User
156
if u.age and u.age > 20).order_by(u.age)
157
158
# Back to normal (no debug output)
159
with db_session:
160
user3 = User(name="Dave", email="dave@example.com")
161
```
162
163
### Schema Inspection and Pretty Printing
164
165
```python
166
# Display entity schema
167
show(User)
168
# Output:
169
# class User(Entity):
170
# id = PrimaryKey(int, auto=True)
171
# name = Required(str)
172
# email = Required(str, unique=True)
173
# age = Optional(int)
174
175
with db_session:
176
# Create some test data
177
users = [
178
User(name="Alice", email="alice@example.com", age=25),
179
User(name="Bob", email="bob@example.com", age=30),
180
User(name="Charlie", email="charlie@example.com", age=22)
181
]
182
183
# Display entity instance
184
show(users[0])
185
# Output:
186
# User[1]
187
# id: 1
188
# name: 'Alice'
189
# email: 'alice@example.com'
190
# age: 25
191
192
# Display query results
193
query = select(u for u in User if u.age >= 25)
194
show(query)
195
# Output:
196
# User[1]
197
# User[2]
198
# (shows all matching users)
199
200
# Pretty print query with projections
201
name_age_query = select((u.name, u.age) for u in User).order_by(u.name)
202
show(name_age_query)
203
# Output:
204
# name | age
205
# ---------|----
206
# Alice | 25
207
# Bob | 30
208
# Charlie | 22
209
```
210
211
### Query-Level Debugging
212
213
```python
214
with db_session:
215
# Get query object without executing
216
query = select(u for u in User if u.age > 20)
217
218
# Show SQL for specific query
219
query.show()
220
# Prints the SQL for this specific query
221
222
# Get SQL string programmatically
223
sql_string = query.get_sql()
224
print(f"Generated SQL: {sql_string}")
225
226
# Execute query after inspection
227
results = list(query)
228
229
# Debug complex queries step by step
230
with db_session:
231
base_query = select(u for u in User)
232
print("Base query:")
233
base_query.show()
234
235
filtered_query = base_query.filter(lambda u: u.age > 18)
236
print("After age filter:")
237
filtered_query.show()
238
239
ordered_query = filtered_query.order_by(User.name)
240
print("After ordering:")
241
ordered_query.show()
242
243
final_query = ordered_query.limit(10)
244
print("After limit:")
245
final_query.show()
246
```
247
248
### Debugging in Different Environments
249
250
```python
251
import os
252
import logging
253
254
# Environment-based debugging configuration
255
def setup_pony_debugging():
256
debug_level = os.getenv('PONY_DEBUG', 'off').lower()
257
258
if debug_level == 'full':
259
# Full debugging with parameter values
260
set_sql_debug(True, show_values=True)
261
elif debug_level == 'sql':
262
# SQL only without parameters
263
set_sql_debug(True, show_values=False)
264
elif debug_level == 'off':
265
# No debugging
266
set_sql_debug(False)
267
268
return debug_level
269
270
# Setup debugging based on environment
271
debug_mode = setup_pony_debugging()
272
print(f"Pony debugging mode: {debug_mode}")
273
274
# Custom logging for SQL queries
275
class SQLLogger:
276
def __init__(self, logger_name='pony.sql'):
277
self.logger = logging.getLogger(logger_name)
278
279
def log_query(self, sql, params=None):
280
if params:
281
self.logger.info(f"SQL: {sql} | Params: {params}")
282
else:
283
self.logger.info(f"SQL: {sql}")
284
285
# Production debugging with custom logger
286
if os.getenv('ENVIRONMENT') == 'production':
287
# Use logging instead of print for production
288
logging.basicConfig(level=logging.INFO)
289
sql_logger = SQLLogger()
290
291
# Custom query execution with logging
292
def logged_query(query_func):
293
with sql_debugging:
294
# Capture SQL output and log it properly
295
result = query_func()
296
return result
297
298
with db_session:
299
users = logged_query(lambda: list(select(u for u in User)))
300
```
301
302
### Performance Debugging
303
304
```python
305
import time
306
from contextlib import contextmanager
307
308
@contextmanager
309
def query_timer(query_name="Query"):
310
"""Time query execution for performance debugging."""
311
start_time = time.time()
312
try:
313
yield
314
finally:
315
end_time = time.time()
316
duration = end_time - start_time
317
print(f"{query_name} took {duration:.4f} seconds")
318
319
# Combine SQL debugging with timing
320
with sql_debugging:
321
with db_session:
322
with query_timer("User count"):
323
user_count = count(u for u in User)
324
325
with query_timer("Complex aggregation"):
326
stats = select(
327
(count(), avg(u.age), min(u.age), max(u.age))
328
for u in User if u.age
329
).get()
330
331
with query_timer("Join query"):
332
user_orders = select(
333
(u.name, count(o.id))
334
for u in User for o in u.orders
335
).group_by(u.id)
336
337
# Profile multiple queries
338
def profile_queries():
339
queries = {
340
"all_users": lambda: list(select(u for u in User)),
341
"adult_users": lambda: list(select(u for u in User if u.age >= 18)),
342
"user_count": lambda: count(u for u in User),
343
"avg_age": lambda: avg(u.age for u in User if u.age)
344
}
345
346
with sql_debugging:
347
with db_session:
348
for name, query_func in queries.items():
349
with query_timer(name):
350
result = query_func()
351
print(f" Result: {result}")
352
353
# Run profiling
354
profile_queries()
355
```
356
357
### Debugging Best Practices
358
359
```python
360
# Good: Enable debugging for development
361
if __name__ == "__main__":
362
set_sql_debug(True, show_values=True)
363
364
# Good: Conditional debugging based on environment
365
import logging
366
if logging.getLogger().getEffectiveLevel() == logging.DEBUG:
367
set_sql_debug(True)
368
369
# Good: Temporary debugging for specific issues
370
def debug_user_creation():
371
with sql_debugging:
372
with db_session:
373
try:
374
user = User(name="Test", email="test@example.com")
375
commit()
376
except Exception as e:
377
print(f"Error creating user: {e}")
378
show(User) # Inspect schema
379
raise
380
381
# Good: Debug query performance issues
382
def debug_slow_query():
383
set_sql_debug(True, show_values=True)
384
385
with db_session:
386
print("=== Debugging slow query ===")
387
388
# Original slow query
389
slow_query = select(u for u in User
390
if exists(o for o in u.orders
391
if o.total > 1000))
392
print("Original query:")
393
slow_query.show()
394
395
# Optimized version
396
fast_query = select(u for u in User
397
if u.id in select(o.user_id for o in Order
398
if o.total > 1000))
399
print("Optimized query:")
400
fast_query.show()
401
402
set_sql_debug(False)
403
404
# Bad: Leave debugging enabled in production
405
# set_sql_debug(True) # Don't do this in production code
406
407
# Bad: Debug without context
408
# with db_session:
409
# users = select(u for u in User) # Hard to identify which query
410
# show(users)
411
```