0
# Stateful Sessions
1

2
Persistent database sessions that maintain state across queries, enabling DDL operations, temporary tables, views, and complex multi-query workflows. Sessions provide database-like persistence either in memory or on disk.
3

4
## Capabilities
5

6
### Session Management
7

8
Create and manage stateful database sessions with automatic cleanup.
9

10
```python { .api }
11
class Session:
12
    def __init__(self, path: str = None):
13
        """
14
        Initialize a new database session.
15
        
16
        Parameters:
17
        - path: Optional directory path for persistent storage. If None, creates temporary directory that is auto-cleaned.
18
        
19
        Notes:
20
        - Default database is "_local" with Memory engine
21
        - Data stored in memory unless different database/engine specified
22
        - Temporary sessions are cleaned up automatically on deletion
23
        - Persistent sessions (with path) are not auto-cleaned
24
        """
25
    
26
    def query(self, sql: str, fmt: str = "CSV", udf_path: str = ""):
27
        """
28
        Execute SQL query within session context.
29
        
30
        Parameters:
31
        - sql: SQL query string to execute
32
        - fmt: Output format ("CSV", "JSON", "DataFrame", "Pretty", etc.)
33
        - udf_path: Optional path to user-defined function configurations
34
        
35
        Returns:
36
        Query result in specified format
37
        
38
        Raises:
39
        ChdbError: If query execution fails
40
        """
41
    
42
    def sql(self, sql: str, fmt: str = "CSV", udf_path: str = ""):
43
        """Alias for query() method with identical functionality."""
44
    
45
    def cleanup(self):
46
        """Manually clean up session resources and temporary files."""
47
```
48

49
### Context Manager Support
50

51
Sessions support Python context manager protocol for automatic resource cleanup.
52

53
```python { .api }
54
class Session:
55
    def __enter__(self):
56
        """Enter context manager, returns self."""
57
    
58
    def __exit__(self, exc_type, exc_value, traceback):
59
        """Exit context manager, calls cleanup()."""
60
    
61
    def __del__(self):
62
        """Destructor that calls cleanup() for temporary sessions."""
63
```
64

65
## Usage Examples
66

67
### Basic Session Usage
68

69
```python
70
from chdb import session
71

72
# Create temporary session (auto-cleanup)
73
sess = session.Session()
74

75
# Execute queries with persistent state
76
sess.query("CREATE DATABASE test_db ENGINE = Atomic")
77
sess.query("CREATE TABLE test_db.users (id Int32, name String) ENGINE = Memory")
78
sess.query("INSERT INTO test_db.users VALUES (1, 'Alice'), (2, 'Bob')")
79

80
# Query the data
81
result = sess.query("SELECT * FROM test_db.users", "Pretty")
82
print(result)
83

84
# Session is automatically cleaned up when sess goes out of scope
85
```
86

87
### Context Manager Usage
88

89
```python
90
from chdb import session
91

92
# Using session as context manager for automatic cleanup
93
with session.Session() as sess:
94
    # Create database objects
95
    sess.query("CREATE DATABASE IF NOT EXISTS analytics ENGINE = Atomic")
96
    
97
    # Create table with specific engine
98
    sess.query('''
99
        CREATE TABLE IF NOT EXISTS analytics.sales (
100
            date Date,
101
            product_id Int32,
102
            quantity Int32,
103
            price Float64
104
        ) ENGINE = MergeTree()
105
        ORDER BY (date, product_id)
106
    ''')
107
    
108
    # Insert sample data
109
    sess.query('''
110
        INSERT INTO analytics.sales VALUES
111
        ('2024-01-01', 1, 100, 9.99),
112
        ('2024-01-01', 2, 50, 19.99),
113
        ('2024-01-02', 1, 75, 9.99),
114
        ('2024-01-02', 2, 80, 19.99)
115
    ''')
116
    
117
    # Complex analytical query
118
    report = sess.query('''
119
        SELECT 
120
            date,
121
            SUM(quantity) as total_quantity,
122
            SUM(quantity * price) as total_revenue,
123
            AVG(price) as avg_price
124
        FROM analytics.sales
125
        GROUP BY date
126
        ORDER BY date
127
    ''', "DataFrame")
128
    
129
    print(report)
130
# Session automatically cleaned up here
131
```
132

133
### Persistent Session Storage
134

135
```python
136
from chdb import session
137
import os
138

139
# Create session with persistent storage
140
db_path = "/tmp/my_persistent_db"
141
sess = session.Session(path=db_path)
142

143
# Create persistent data structures
144
sess.query("CREATE DATABASE IF NOT EXISTS warehouse ENGINE = Atomic")
145
sess.query('''
146
    CREATE TABLE IF NOT EXISTS warehouse.inventory (
147
        item_id Int32,
148
        item_name String,
149
        quantity Int32,
150
        last_updated DateTime
151
    ) ENGINE = MergeTree()
152
    ORDER BY item_id
153
''')
154

155
# Insert data
156
sess.query('''
157
    INSERT INTO warehouse.inventory VALUES
158
    (1, 'Widget A', 100, now()),
159
    (2, 'Widget B', 250, now()),
160
    (3, 'Widget C', 75, now())
161
''')
162

163
print("Data inserted into persistent storage")
164

165
# Close session but keep data
166
sess.cleanup()
167

168
# Later: Reconnect to same persistent storage
169
sess2 = session.Session(path=db_path)
170
inventory = sess2.query("SELECT * FROM warehouse.inventory", "DataFrame")
171
print("Retrieved from persistent storage:")
172
print(inventory)
173

174
# Manual cleanup when done with persistent data
175
sess2.cleanup()
176
# Note: Database files remain at db_path
177
```
178

179
### Working with Views and Complex Queries
180

181
```python
182
from chdb import session
183

184
with session.Session() as sess:
185
    # Create database and base tables
186
    sess.query("CREATE DATABASE reporting ENGINE = Atomic")
187
    
188
    sess.query('''
189
        CREATE TABLE reporting.orders (
190
            order_id Int32,
191
            customer_id Int32,
192
            order_date Date,
193
            amount Float64
194
        ) ENGINE = Memory
195
    ''')
196
    
197
    sess.query('''
198
        CREATE TABLE reporting.customers (
199
            customer_id Int32,
200
            customer_name String,
201
            region String
202
        ) ENGINE = Memory
203
    ''')
204
    
205
    # Insert sample data
206
    sess.query('''
207
        INSERT INTO reporting.orders VALUES
208
        (1, 101, '2024-01-15', 250.00),
209
        (2, 102, '2024-01-16', 125.50),
210
        (3, 101, '2024-01-17', 89.99),
211
        (4, 103, '2024-01-18', 450.00)
212
    ''')
213
    
214
    sess.query('''
215
        INSERT INTO reporting.customers VALUES
216
        (101, 'Acme Corp', 'North'),
217
        (102, 'Tech Solutions', 'South'),
218
        (103, 'Global Industries', 'East')
219
    ''')
220
    
221
    # Create view for easy reporting
222
    sess.query('''
223
        CREATE VIEW reporting.customer_summary AS
224
        SELECT 
225
            c.customer_name,
226
            c.region,
227
            COUNT(o.order_id) as order_count,
228
            SUM(o.amount) as total_spent,
229
            AVG(o.amount) as avg_order_value
230
        FROM reporting.customers c
231
        LEFT JOIN reporting.orders o ON c.customer_id = o.customer_id
232
        GROUP BY c.customer_id, c.customer_name, c.region
233
        ORDER BY total_spent DESC
234
    ''')
235
    
236
    # Query the view
237
    summary = sess.query("SELECT * FROM reporting.customer_summary", "Pretty")
238
    print("Customer Summary Report:")
239
    print(summary)
240
    
241
    # Use view in more complex queries
242
    top_customers = sess.query('''
243
        SELECT customer_name, total_spent
244
        FROM reporting.customer_summary
245
        WHERE total_spent > 200
246
    ''', "JSON")
247
    
248
    print("Top customers (>$200):")
249
    print(top_customers)
250
```
251

252
### DataFrame Output with Sessions
253

254
```python
255
from chdb import session
256
import pandas as pd
257

258
with session.Session() as sess:
259
    # Create and populate table
260
    sess.query("CREATE TABLE sales (date Date, amount Float64) ENGINE = Memory")
261
    sess.query('''
262
        INSERT INTO sales VALUES
263
        ('2024-01-01', 100.0),
264
        ('2024-01-02', 150.0),
265
        ('2024-01-03', 200.0)
266
    ''')
267
    
268
    # Get results as DataFrame
269
    df = sess.query("SELECT * FROM sales ORDER BY date", "DataFrame")
270
    
271
    # Work with pandas DataFrame
272
    df['cumulative'] = df['amount'].cumsum()
273
    print("Sales with cumulative total:")
274
    print(df)
275
    
276
    # Use DataFrame results in subsequent queries
277
    total = sess.query("SELECT SUM(amount) as total FROM sales", "JSON")
278
    print(f"Total sales: {total}")
279
```
280

281
### Error Handling with Sessions
282

283
```python
284
from chdb import session, ChdbError
285

286
try:
287
    with session.Session() as sess:
288
        # This will work
289
        sess.query("CREATE TABLE test (id Int32) ENGINE = Memory")
290
        
291
        # This might fail if table already exists without IF NOT EXISTS
292
        sess.query("CREATE TABLE test (id Int32) ENGINE = Memory")
293
        
294
except ChdbError as e:
295
    print(f"Session query failed: {e}")
296
```