> ## Documentation Index
> Fetch the complete documentation index at: https://base.bangwu.me/llms.txt
> Use this file to discover all available pages before exploring further.

# SQLite

> SQLite is a lightweight embedded database reading and writing disk files, covering Python sqlite3 integration, basic SQL operations, and use cases.

# SQLite

SQLite is a lightweight embedded relational database. It does not require a standalone server process and reads from or writes to a disk file directly.

## Features

* **Zero configuration**: no installation or admin service required
* **Single file**: the entire database lives in one file
* **Cross-platform**: works on all major operating systems
* **Self-contained**: no external service dependency
* **Transactional**: supports full ACID behavior

## Using SQLite in Python

### Basic operations

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
import sqlite3

# 连接数据库（不存在则创建）
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
''')

# 插入数据
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", 
               ("张三", "zhangsan@example.com"))

# 查询数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# 提交事务
conn.commit()
conn.close()
```

### Context manager

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()
```

## Common commands

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
-- View所有表
.tables

-- View表结构
.schema users

-- 导出数据
.output data.sql
.dump

-- 导入数据
.read data.sql
```

## Performance tips

1. **Use transactions** for batch operations.
2. **Create indexes** to speed up queries.
3. **Enable WAL mode** to improve concurrent read/write behavior.

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
# 启用 WAL 模式
conn.execute('PRAGMA journal_mode=WAL')
```

## Good use cases

* Local storage for mobile apps
* Desktop app data management
* Small web applications
* Embedded devices
* Testing and prototyping

## Limitations

* Not ideal for high-concurrency writes
* Recommended database size is typically below 1 TB
* No built-in user permission management