0
# Prepared Statements
1

2
High-performance reusable query execution with server-side statement caching and optimized parameter binding. Prepared statements provide significant performance benefits for queries that are executed repeatedly with different parameters.
3

4
## Capabilities
5

6
### Statement Preparation
7

8
Creates a prepared statement on the server that can be executed multiple times with different parameters, eliminating parse overhead and enabling query plan caching.
9

10
```python { .api }
11
async def prepare(
12
    self,
13
    query: str,
14
    *,
15
    name: str = None,
16
    timeout: float = None,
17
    record_class: type = None
18
) -> PreparedStatement
19
```
20

21
**Parameters:**
22
- `query`: SQL query string with optional parameter placeholders (`$1`, `$2`, etc.)
23
- `name`: Optional name for the prepared statement (auto-generated if not provided)
24
- `timeout`: Statement preparation timeout in seconds
25
- `record_class`: Custom record class for query results
26

27
**Usage Example:**
28
```python
29
import asyncpg
30

31
async def example():
32
    conn = await asyncpg.connect('postgresql://user:pass@localhost/db')
33
    
34
    # Prepare a statement
35
    stmt = await conn.prepare('SELECT * FROM users WHERE age > $1 AND city = $2')
36
    
37
    # Execute with different parameters
38
    young_users = await stmt.fetch(18, 'New York')
39
    older_users = await stmt.fetch(65, 'Seattle')
40
    
41
    await conn.close()
42
```
43

44
### Statement Execution
45

46
Execute prepared statements with various result formats, providing the same interface as direct connection methods but with improved performance.
47

48
```python { .api }
49
async def execute(self, *args, timeout: float = None) -> str
50
async def fetch(self, *args, timeout: float = None, record_class: type = None) -> typing.List[Record]
51
async def fetchval(self, *args, column: int = 0, timeout: float = None) -> typing.Any
52
async def fetchrow(self, *args, timeout: float = None, record_class: type = None) -> typing.Optional[Record]
53
async def executemany(self, args, *, timeout: float = None) -> None
54
```
55

56
**Usage Example:**
57
```python
58
# Single execution
59
result = await stmt.execute(25, 'Boston')
60
rows = await stmt.fetch(30, 'Chicago')
61
count = await stmt.fetchval(21, 'Portland')
62
single_row = await stmt.fetchrow(35, 'Denver')
63

64
# Batch execution
65
await stmt.executemany([
66
    (22, 'Austin'),
67
    (28, 'Miami'),
68
    (33, 'Atlanta')
69
])
70
```
71

72
### Cursor Creation
73

74
Create cursors from prepared statements for efficient traversal of large result sets.
75

76
```python { .api }
77
def cursor(
78
    self,
79
    *args,
80
    prefetch: int = None,
81
    timeout: float = None,
82
    record_class: type = None
83
) -> CursorFactory
84
```
85

86
**Usage Example:**
87
```python
88
stmt = await conn.prepare('SELECT * FROM large_table WHERE category = $1')
89

90
async with stmt.cursor('electronics', prefetch=100) as cursor:
91
    async for row in cursor:
92
        process_row(row)
93
```
94

95
### Statement Introspection
96

97
Access metadata about prepared statements including parameter types, result attributes, and statement details.
98

99
```python { .api }
100
def get_name(self) -> str
101
def get_query(self) -> str
102
def get_statusmsg(self) -> str
103
def get_parameters(self) -> typing.Tuple[Type, ...]
104
def get_attributes(self) -> typing.Tuple[Attribute, ...]
105
```
106

107
**Usage Example:**
108
```python
109
stmt = await conn.prepare('SELECT id, name, email FROM users WHERE age > $1')
110

111
print(f"Statement name: {stmt.get_name()}")
112
print(f"Query: {stmt.get_query()}")
113
print(f"Parameters: {[p.name for p in stmt.get_parameters()]}")
114
print(f"Result columns: {[a.name for a in stmt.get_attributes()]}")
115
```
116

117
## Types
118

119
```python { .api }
120
class PreparedStatement:
121
    """A prepared statement for efficient query execution."""
122
    
123
    def get_name(self) -> str
124
    def get_query(self) -> str
125
    def get_statusmsg(self) -> str
126
    def get_parameters(self) -> typing.Tuple[Type, ...]
127
    def get_attributes(self) -> typing.Tuple[Attribute, ...]
128
    
129
    async def execute(self, *args, timeout: float = None) -> str
130
    async def fetch(self, *args, timeout: float = None, record_class: type = None) -> typing.List[Record]
131
    async def fetchval(self, *args, column: int = 0, timeout: float = None) -> typing.Any
132
    async def fetchrow(self, *args, timeout: float = None, record_class: type = None) -> typing.Optional[Record]
133
    async def executemany(self, args, *, timeout: float = None) -> None
134
    
135
    def cursor(
136
        self,
137
        *args,
138
        prefetch: int = None,
139
        timeout: float = None,
140
        record_class: type = None
141
    ) -> CursorFactory
142
```
143

144
## Performance Benefits
145

146
Prepared statements provide several performance advantages:
147

148
- **Parse Overhead Elimination**: Query parsing is done once during preparation
149
- **Query Plan Caching**: PostgreSQL can cache and reuse execution plans
150
- **Parameter Type Optimization**: Parameter types are determined during preparation
151
- **Network Efficiency**: Only parameter values are sent for subsequent executions
152
- **Memory Efficiency**: Statement metadata is cached and reused
153

154
## Best Practices
155

156
1. **Use for Repeated Queries**: Greatest benefit for queries executed multiple times
157
2. **Parameter Validation**: Prepared statements provide automatic type checking
158
3. **Statement Caching**: Connection maintains a cache of prepared statements
159
4. **Resource Management**: Prepared statements are automatically cleaned up when connection closes
160
5. **Batch Operations**: Use `executemany()` for bulk parameter variations