0
# Query Operations
1
2
Functions for querying the database using Pony's signature generator expression syntax. These provide the main interface for data retrieval and manipulation using intuitive Python expressions that are automatically translated to optimized SQL.
3
4
## Capabilities
5
6
### Primary Query Functions
7
8
Core query functions that form the foundation of Pony ORM's querying capabilities using generator expressions.
9
10
```python { .api }
11
def select(gen):
12
"""Execute SELECT query using generator expression.
13
14
Args:
15
gen: Generator expression defining query criteria
16
17
Returns:
18
Query object with results (iterable)
19
20
Usage:
21
select(p for p in Person if p.age > 18)
22
select(p.name for p in Person if p.age > 18)
23
select((p.name, p.age) for p in Person if p.age > 18)
24
"""
25
26
def get(gen):
27
"""Get single entity using generator expression.
28
29
Args:
30
gen: Generator expression that should match exactly one entity
31
32
Returns:
33
Single entity instance
34
35
Raises:
36
ObjectNotFound: No entities match criteria
37
MultipleObjectsFoundError: Multiple entities match criteria
38
39
Usage:
40
get(p for p in Person if p.email == "alice@example.com")
41
"""
42
43
def exists(gen):
44
"""Check if any entities match generator expression criteria.
45
46
Args:
47
gen: Generator expression defining criteria
48
49
Returns:
50
bool: True if any entities match
51
52
Usage:
53
exists(p for p in Person if p.age > 100)
54
"""
55
56
def delete(gen):
57
"""Delete entities matching generator expression criteria.
58
59
Args:
60
gen: Generator expression defining entities to delete
61
62
Returns:
63
int: Number of deleted entities
64
65
Usage:
66
delete(p for p in Person if p.age < 0)
67
"""
68
69
def left_join(gen):
70
"""Execute LEFT JOIN query using generator expression.
71
72
Args:
73
gen: Generator expression with left join criteria
74
75
Returns:
76
Query object with left join results
77
78
Usage:
79
left_join((p, o) for p in Person for o in p.orders if o.total > 100)
80
"""
81
```
82
83
### Query Result Operations
84
85
Methods available on query results for further processing and manipulation.
86
87
```python { .api }
88
class Query:
89
def count(self):
90
"""Get count of query results without fetching all data."""
91
92
def limit(self, n, offset=0):
93
"""Limit query results.
94
95
Args:
96
n: Maximum number of results
97
offset: Number of results to skip
98
"""
99
100
def order_by(self, *args):
101
"""Order query results.
102
103
Args:
104
*args: Attributes or expressions to order by
105
"""
106
107
def distinct(self):
108
"""Remove duplicate results."""
109
110
def filter(self, lambda_expr):
111
"""Add additional filter to query.
112
113
Args:
114
lambda_expr: Lambda expression for filtering
115
"""
116
117
def first(self):
118
"""Get first result or None if no results."""
119
120
def random(self, n=1):
121
"""Get random results.
122
123
Args:
124
n: Number of random results
125
"""
126
127
def page(self, page_num, page_size=10):
128
"""Get specific page of results.
129
130
Args:
131
page_num: Page number (1-based)
132
page_size: Results per page
133
"""
134
135
def prefetch(self, *attrs):
136
"""Prefetch related attributes to avoid N+1 queries.
137
138
Args:
139
*attrs: Attribute names to prefetch
140
"""
141
142
def show(self):
143
"""Print SQL query for debugging."""
144
145
def get_sql(self):
146
"""Get SQL query string."""
147
```
148
149
## Usage Examples
150
151
### Basic Queries
152
153
```python
154
from pony.orm import *
155
156
with db_session:
157
# Simple selection
158
adults = select(p for p in Person if p.age >= 18)
159
160
# Select specific attributes
161
names = select(p.name for p in Person if p.age >= 18)
162
163
# Select multiple attributes as tuples
164
name_age_pairs = select((p.name, p.age) for p in Person if p.age >= 18)
165
166
# Iterate over results
167
for person in adults:
168
print(f"{person.name} is {person.age} years old")
169
170
# Convert to list
171
adult_list = list(adults)
172
173
# Get single entity
174
alice = get(p for p in Person if p.email == "alice@example.com")
175
176
# Check existence
177
has_minors = exists(p for p in Person if p.age < 18)
178
179
# Delete entities
180
deleted_count = delete(p for p in Person if p.last_login < date(2020, 1, 1))
181
```
182
183
### Complex Queries with Joins
184
185
```python
186
with db_session:
187
# Join with related entities
188
big_orders = select(o for o in Order
189
if o.customer.country == "USA" and o.total > 1000)
190
191
# Multiple joins
192
tech_employees = select(e for e in Employee
193
if e.company.industry == "Technology"
194
and e.department.name == "Engineering")
195
196
# Left join for optional relationships
197
all_customers_with_orders = left_join(
198
(c, o) for c in Customer for o in c.orders
199
)
200
201
# Select from join results
202
customer_order_data = select(
203
(c.name, o.total if o else 0)
204
for c in Customer
205
for o in c.orders
206
)
207
```
208
209
### Query Filtering and Conditions
210
211
```python
212
with db_session:
213
# String operations
214
gmail_users = select(p for p in Person if p.email.endswith("@gmail.com"))
215
john_variants = select(p for p in Person if p.name.startswith("John"))
216
217
# Numeric comparisons
218
millennials = select(p for p in Person if 1981 <= p.birth_year <= 1996)
219
220
# Date operations
221
recent_orders = select(o for o in Order
222
if o.date >= datetime.now() - timedelta(days=30))
223
224
# Collection operations
225
prolific_authors = select(a for a in Author if len(a.books) > 5)
226
227
# Complex boolean logic
228
target_customers = select(c for c in Customer
229
if (c.age > 25 and c.income > 50000)
230
or (c.loyalty_years > 5))
231
232
# IN operations using collections
233
tech_companies = ["Google", "Microsoft", "Apple"]
234
tech_employees = select(e for e in Employee
235
if e.company.name in tech_companies)
236
```
237
238
### Query Result Processing
239
240
```python
241
with db_session:
242
# Get query object (not executed yet)
243
query = select(p for p in Person if p.age > 18)
244
245
# Count results without fetching
246
adult_count = query.count()
247
248
# Ordering
249
ordered_adults = query.order_by(Person.name)
250
reverse_ordered = query.order_by(desc(Person.age))
251
252
# Limiting results
253
first_10_adults = query.limit(10)
254
page_2_adults = query.limit(10, offset=10)
255
256
# Pagination
257
page_2 = query.page(2, page_size=20)
258
259
# Get first result
260
oldest_adult = query.order_by(desc(Person.age)).first()
261
262
# Random selection
263
random_adults = query.random(5)
264
265
# Remove duplicates
266
unique_ages = select(p.age for p in Person).distinct()
267
268
# Additional filtering
269
senior_adults = query.filter(lambda p: p.age > 65)
270
271
# Prefetch related data to avoid N+1 queries
272
people_with_orders = select(p for p in Person).prefetch('orders')
273
```
274
275
### Advanced Query Patterns
276
277
```python
278
with db_session:
279
# Subqueries
280
avg_age = select(avg(p.age) for p in Person).get()
281
above_average = select(p for p in Person if p.age > avg_age)
282
283
# Correlated subqueries
284
customers_with_big_orders = select(c for c in Customer
285
if exists(o for o in c.orders
286
if o.total > 1000))
287
288
# Query with calculations
289
order_summaries = select(
290
(o.customer.name, o.total, o.total * 0.1)
291
for o in Order
292
if o.total > 100
293
)
294
295
# Using raw SQL in queries
296
complex_calculation = select(
297
p for p in Person
298
if raw_sql("EXTRACT(YEAR FROM $p.birth_date)") > 1990
299
)
300
301
# Multiple entity selection
302
customer_product_pairs = select(
303
(c, p) for c in Customer for p in Product
304
if p in c.favorite_products
305
)
306
```
307
308
### Query Debugging
309
310
```python
311
with db_session:
312
# Show generated SQL
313
query = select(p for p in Person if p.age > 18)
314
query.show() # Prints the SQL query
315
316
# Get SQL string
317
sql_string = query.get_sql()
318
print(f"Generated SQL: {sql_string}")
319
320
# Enable SQL debugging globally
321
set_sql_debug(True)
322
323
# All queries will now print SQL
324
adults = select(p for p in Person if p.age > 18)
325
326
# Disable SQL debugging
327
set_sql_debug(False)
328
```