0
# Database Operations
1
2
Database connection management, table access, transaction control, and raw SQL query execution. The Database class provides the foundation for all dataset operations with automatic schema management and connection pooling.
3
4
## Capabilities
5
6
### Connection Management
7
8
Create and manage database connections with support for multiple database backends and connection pooling.
9
10
```python { .api }
11
def connect(url=None, schema=None, engine_kwargs=None, ensure_schema=True,
12
row_type=OrderedDict, sqlite_wal_mode=True, on_connect_statements=None):
13
"""
14
Opens a new connection to a database.
15
16
Parameters:
17
- url: str, SQLAlchemy engine URL (defaults to DATABASE_URL env var or sqlite://)
18
- schema: str, database schema name
19
- engine_kwargs: dict, additional SQLAlchemy engine parameters
20
- ensure_schema: bool, automatically create tables and columns (default True)
21
- row_type: type, dict-like class for row containers (default OrderedDict)
22
- sqlite_wal_mode: bool, enable WAL mode for SQLite (default True)
23
- on_connect_statements: list, SQL statements to run on connect
24
25
Returns:
26
Database instance
27
"""
28
29
class Database:
30
def __init__(self, url, schema=None, engine_kwargs=None, ensure_schema=True,
31
row_type=OrderedDict, sqlite_wal_mode=True, on_connect_statements=None):
32
"""Configure and connect to the database."""
33
34
def close(self):
35
"""Close database connections. Makes this object unusable."""
36
```
37
38
### Table Access
39
40
Access and manage database tables with automatic creation and schema reflection.
41
42
```python { .api }
43
class Database:
44
def has_table(self, name):
45
"""Check if a table exists in the database."""
46
47
def create_table(self, table_name, primary_id=None, primary_type=None, primary_increment=None):
48
"""
49
Create a new table.
50
51
Parameters:
52
- table_name: str, name of the table
53
- primary_id: str, name of primary key column (default "id")
54
- primary_type: SQLAlchemy type, type of primary key (default Integer)
55
- primary_increment: bool, auto-increment primary key (default True for numeric types)
56
57
Returns:
58
Table instance
59
"""
60
61
def load_table(self, table_name):
62
"""
63
Load an existing table.
64
65
Parameters:
66
- table_name: str, name of existing table
67
68
Returns:
69
Table instance
70
"""
71
72
def get_table(self, table_name, primary_id=None, primary_type=None, primary_increment=None):
73
"""
74
Load or create a table (same as create_table).
75
76
Returns:
77
Table instance
78
"""
79
80
def __getitem__(self, table_name):
81
"""Get a table using bracket notation: db['table_name']"""
82
83
def __contains__(self, table_name):
84
"""Check if table exists using 'in' operator: 'table' in db"""
85
```
86
87
### Database Properties
88
89
Access database metadata and connection information.
90
91
```python { .api }
92
class Database:
93
@property
94
def tables(self):
95
"""Get a list of all table names in the database."""
96
97
@property
98
def views(self):
99
"""Get a list of all view names in the database."""
100
101
@property
102
def executable(self):
103
"""Connection against which statements will be executed."""
104
105
@property
106
def metadata(self):
107
"""Return a SQLAlchemy schema cache object."""
108
109
@property
110
def in_transaction(self):
111
"""Check if this database is in a transactional context."""
112
113
@property
114
def op(self):
115
"""Get an alembic operations context."""
116
117
@property
118
def inspect(self):
119
"""Get a SQLAlchemy inspector."""
120
121
@property
122
def types(self):
123
"""Get the Types instance for accessing SQLAlchemy types."""
124
```
125
126
### Transaction Management
127
128
Handle database transactions with explicit control or context manager support.
129
130
```python { .api }
131
class Database:
132
def begin(self):
133
"""
134
Enter a transaction explicitly.
135
No data will be written until the transaction has been committed.
136
"""
137
138
def commit(self):
139
"""
140
Commit the current transaction.
141
Make all statements executed since the transaction was begun permanent.
142
"""
143
144
def rollback(self):
145
"""
146
Roll back the current transaction.
147
Discard all statements executed since the transaction was begun.
148
"""
149
150
def __enter__(self):
151
"""Start a transaction."""
152
153
def __exit__(self, error_type, error_value, traceback):
154
"""End a transaction by committing or rolling back."""
155
```
156
157
### Raw SQL Queries
158
159
Execute arbitrary SQL statements with parameter binding and result iteration.
160
161
```python { .api }
162
class Database:
163
def query(self, query, *args, **kwargs):
164
"""
165
Run a statement on the database directly.
166
167
Parameters:
168
- query: str or SQLAlchemy expression, SQL query to execute
169
- *args: positional parameters for query binding
170
- **kwargs: keyword parameters for query binding
171
- _step: int, result fetching step size (default 1000)
172
173
Returns:
174
ResultIter: Iterator yielding each result row as dict-like objects
175
"""
176
```
177
178
## Usage Examples
179
180
### Database Connection
181
182
```python
183
import dataset
184
185
# SQLite (default)
186
db = dataset.connect() # In-memory
187
db = dataset.connect('sqlite:///data.db') # File-based
188
189
# PostgreSQL
190
db = dataset.connect('postgresql://user:pass@localhost/dbname')
191
192
# MySQL
193
db = dataset.connect('mysql://user:pass@localhost/dbname')
194
195
# With custom options
196
db = dataset.connect(
197
'sqlite:///data.db',
198
engine_kwargs={'pool_recycle': 3600},
199
row_type=dict, # Use regular dict instead of OrderedDict
200
on_connect_statements=['PRAGMA foreign_keys=ON']
201
)
202
```
203
204
### Table Management
205
206
```python
207
# Check if table exists
208
if 'users' in db:
209
print("Users table exists")
210
211
# Create table with custom primary key
212
users = db.create_table('users', 'user_id', db.types.string(50))
213
214
# Load existing table
215
existing_table = db.load_table('products')
216
217
# Get or create table (most common)
218
orders = db['orders'] # Shorthand syntax
219
orders = db.get_table('orders') # Explicit syntax
220
```
221
222
### Transactions
223
224
```python
225
# Explicit transaction control
226
db.begin()
227
try:
228
table.insert({'name': 'John'})
229
table.insert({'name': 'Jane'})
230
db.commit()
231
except Exception:
232
db.rollback()
233
raise
234
235
# Context manager (recommended)
236
with db:
237
table.insert({'name': 'John'})
238
table.insert({'name': 'Jane'})
239
# Automatically commits on success, rolls back on exception
240
```
241
242
### Raw SQL Queries
243
244
```python
245
# Simple query
246
for row in db.query('SELECT * FROM users WHERE age > 18'):
247
print(row['name'])
248
249
# Parameterized queries
250
for row in db.query('SELECT * FROM users WHERE city = ?', 'New York'):
251
print(row)
252
253
# Named parameters
254
for row in db.query('SELECT * FROM users WHERE age > :min_age', min_age=21):
255
print(row)
256
257
# Complex query with SQLAlchemy expression
258
from sqlalchemy import text
259
query = text('SELECT COUNT(*) as total FROM users WHERE created > :date')
260
result = list(db.query(query, date='2023-01-01'))
261
print(result[0]['total'])
262
```