0
# Native Interface
1

2
Modern streamlined interface optimized for direct query execution with automatic parameter handling and simplified result processing, providing an alternative to the legacy DB-API 2.0 interface.
3

4
## Core Imports
5

6
```python
7
import pg8000.native
8

9
# Available exports from pg8000.native
10
conn = pg8000.native.Connection(...)  # Native Connection class
11
pg8000.native.identifier("table_name")  # Identifier escaping function
12
pg8000.native.literal(value)  # Literal value formatting function
13

14
# Type constants are also available
15
from pg8000.native import INTEGER, TEXT, BOOLEAN, JSON, UUID_TYPE
16
```
17

18
Note: PreparedStatement objects are created through the Connection.prepare() method and are not directly importable.
19

20
## Capabilities
21

22
### Native Connection Class
23

24
Streamlined connection class with direct query execution and simplified parameter handling.
25

26
```python { .api }
27
class Connection:
28
    """
29
    Native pg8000 connection with modern API.
30
    
31
    Properties:
32
    - columns: list - Column information from last query
33
    - row_count: int - Number of rows affected by last query
34
    """
35
    
36
    def __init__(
37
        self,
38
        user: str,
39
        host: str = "localhost",
40
        database: str = None,
41
        port: int = 5432,
42
        password: str = None,
43
        source_address: tuple = None,
44
        unix_sock: str = None,
45
        ssl_context = None,
46
        timeout: float = None,
47
        tcp_keepalive: bool = True,
48
        application_name: str = None,
49
        replication: str = None,
50
        startup_params: dict = None,
51
        sock = None
52
    ):
53
        """
54
        Create native connection to PostgreSQL database.
55
        
56
        Parameters:
57
        - user: Username for database authentication
58
        - host: Database server hostname or IP address
59
        - database: Database name to connect to
60
        - port: Database server port number
61
        - password: Password for database authentication  
62
        - source_address: (host, port) tuple for local socket binding
63
        - unix_sock: Path to Unix domain socket for local connections
64
        - ssl_context: SSL context for encrypted connections
65
        - timeout: Socket timeout in seconds
66
        - tcp_keepalive: Enable TCP keepalive for connection health
67
        - application_name: Application name for connection identification
68
        - replication: Replication mode ('database' or 'physical')
69
        - startup_params: Additional connection parameters dict
70
        - sock: Existing socket connection for database communication
71
        
72
        Raises:
73
        InterfaceError: Connection parameter or interface errors
74
        OperationalError: Database connection errors
75
        """
76
    
77
    def run(self, sql: str, stream=None, types: dict = None, **params) -> list:
78
        """
79
        Execute SQL statement with named parameters and return results.
80
        
81
        Parameters:
82
        - sql: SQL statement with :param placeholders
83
        - stream: Optional stream for COPY operations
84
        - types: Dict mapping parameter names to PostgreSQL type OIDs
85
        - **params: Named parameters for SQL statement
86
        
87
        Returns:
88
        List of result rows as lists, or None for non-SELECT statements
89
        
90
        Raises:
91
        ProgrammingError: Invalid SQL syntax
92
        DataError: Invalid data values
93
        OperationalError: Database execution errors
94
        """
95
    
96
    def prepare(self, sql: str) -> PreparedStatement:
97
        """
98
        Create a prepared statement for efficient repeated execution.
99
        
100
        Parameters:
101
        - sql: SQL statement to prepare with :param placeholders
102
        
103
        Returns:
104
        Native PreparedStatement object
105
        
106
        Raises:
107
        ProgrammingError: Invalid SQL syntax
108
        """
109
    
110
    def close(self) -> None:
111
        """
112
        Close the database connection and free resources.
113
        """
114
```
115

116
### Native PreparedStatement Class
117

118
Prepared statement optimized for the native interface with simplified parameter handling.
119

120
```python { .api }
121
class PreparedStatement:
122
    """
123
    Native prepared statement for efficient repeated execution.
124
    
125
    Properties:
126
    - columns: list - Column information from prepared statement
127
    """
128
    
129
    def run(self, stream=None, **params) -> list:
130
        """
131
        Execute prepared statement with named parameters.
132
        
133
        Parameters:
134
        - stream: Optional stream for COPY operations  
135
        - **params: Named parameter values
136
        
137
        Returns:
138
        List of result rows as lists, or None for non-SELECT statements
139
        
140
        Raises:
141
        DataError: Invalid parameter values
142
        OperationalError: Execution errors
143
        """
144
    
145
    def close(self) -> None:
146
        """
147
        Close the prepared statement and free resources.
148
        """
149
```
150

151
### Query Parsing Functions
152

153
Utility functions for parsing SQL queries with named parameters.
154

155
```python { .api }
156
def to_statement(query: str) -> tuple:
157
    """
158
    Parse SQL query with named parameters into parameterized form.
159
    
160
    Parameters:
161
    - query: SQL query string with :param placeholders
162
    
163
    Returns:
164
    Tuple of (parsed_sql, param_function)
165
    - parsed_sql: SQL with placeholders converted to $1, $2, etc.
166
    - param_function: Function to extract parameter values
167
    
168
    Raises:
169
    ProgrammingError: Invalid parameter syntax
170
    """
171
```
172

173
### Parser State Enum
174

175
Internal enumeration for query parsing states.
176

177
```python { .api }
178
class State(Enum):
179
    """
180
    Parser states for SQL query parsing.
181
    
182
    Values:
183
    - OUT: Outside any special context
184
    - IN_SQ: Inside single-quoted string
185
    - IN_QI: Inside quoted identifier
186
    - IN_ES: Inside escape sequence
187
    - IN_PN: Inside parameter name
188
    - IN_CO: Inside comment
189
    - IN_DQ: Inside double-quoted string
190
    - IN_DP: Inside dollar-quoted string
191
    """
192
    OUT = "OUT"
193
    IN_SQ = "IN_SQ" 
194
    IN_QI = "IN_QI"
195
    IN_ES = "IN_ES"
196
    IN_PN = "IN_PN"
197
    IN_CO = "IN_CO"
198
    IN_DQ = "IN_DQ"
199
    IN_DP = "IN_DP"
200
```
201

202
### Usage Examples
203

204
#### Basic Query Execution
205

206
```python
207
import pg8000.native
208

209
# Connect using native interface
210
conn = pg8000.native.Connection(
211
    user="myuser",
212
    password="mypass", 
213
    database="mydb"
214
)
215

216
# Execute query with named parameters
217
results = conn.run(
218
    "SELECT id, name, email FROM users WHERE active = :active AND age > :min_age",
219
    active=True,
220
    min_age=18
221
)
222

223
# Process results  
224
for row in results:
225
    print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")
226

227
# Check column information
228
print("Columns:", conn.columns)
229
print("Row count:", conn.row_count)
230

231
conn.close()
232
```
233

234
#### Data Insertion and Updates
235

236
```python
237
import pg8000.native
238
import datetime
239

240
conn = pg8000.native.Connection(
241
    user="myuser",
242
    password="mypass",
243
    database="mydb"
244
)
245

246
# Insert new record
247
conn.run(
248
    """INSERT INTO users (name, email, created_at, active) 
249
       VALUES (:name, :email, :created, :active)""",
250
    name="Jane Smith",
251
    email="jane@example.com", 
252
    created=datetime.datetime.now(),
253
    active=True
254
)
255

256
print(f"Inserted {conn.row_count} row(s)")
257

258
# Update existing records
259
conn.run(
260
    "UPDATE users SET last_login = :login_time WHERE email = :email",
261
    login_time=datetime.datetime.now(),
262
    email="jane@example.com"
263
)
264

265
print(f"Updated {conn.row_count} row(s)")
266

267
conn.close()
268
```
269

270
#### Prepared Statements
271

272
```python
273
import pg8000.native
274

275
conn = pg8000.native.Connection(
276
    user="myuser",
277
    password="mypass",
278
    database="mydb"
279
)
280

281
# Prepare statement for repeated execution
282
stmt = conn.prepare(
283
    "INSERT INTO event_log (event_type, user_id, message, timestamp) VALUES (:type, :user, :msg, :ts)"
284
)
285

286
# Execute multiple times with different parameters
287
import datetime
288

289
events = [
290
    ("login", 123, "User logged in", datetime.datetime.now()),
291
    ("page_view", 123, "Viewed dashboard", datetime.datetime.now()),
292
    ("logout", 123, "User logged out", datetime.datetime.now())
293
]
294

295
for event_type, user_id, message, timestamp in events:
296
    result = stmt.run(
297
        type=event_type,
298
        user=user_id,
299
        msg=message,
300
        ts=timestamp
301
    )
302
    print(f"Logged event: {event_type}")
303

304
# Clean up
305
stmt.close()
306
conn.close()
307
```
308

309
#### Complex Queries with Type Specification
310

311
```python
312
import pg8000.native
313
from pg8000 import INTEGER, TEXT, BOOLEAN
314

315
conn = pg8000.native.Connection(
316
    user="myuser", 
317
    password="mypass",
318
    database="mydb"
319
)
320

321
# Execute query with explicit type specification
322
results = conn.run(
323
    """SELECT u.id, u.name, u.email, p.title 
324
       FROM users u 
325
       JOIN posts p ON u.id = p.user_id 
326
       WHERE u.active = :active AND p.published = :published
327
       ORDER BY p.created_at DESC 
328
       LIMIT :limit""",
329
    types={
330
        'active': BOOLEAN,
331
        'published': BOOLEAN, 
332
        'limit': INTEGER
333
    },
334
    active=True,
335
    published=True,
336
    limit=10
337
)
338

339
# Process results
340
for row in results:
341
    user_id, name, email, post_title = row
342
    print(f"User: {name} ({email}) - Post: {post_title}")
343

344
conn.close()
345
```