# How to Store Crawled Data in SQLite with MediaCrawler

> Learn to store crawled data in SQLite using MediaCrawler. This guide shows you how to leverage the built-in SQLite backend for efficient local data persistence without a database server.

- Repository: [程序员阿江-Relakkes/MediaCrawler](https://github.com/NanmiCoder/MediaCrawler)
- Tags: how-to-guide
- Published: 2026-06-26

---

**MediaCrawler provides a built-in SQLite backend that persists crawled content, comments, and creator data to a local file without requiring a separate database server.**

The open-source [NanmiCoder/MediaCrawler](https://github.com/NanmiCoder/MediaCrawler) repository supports multiple storage backends, including a zero-configuration SQLite option. By setting a single configuration flag and initializing the database schema, you can store all scraped items from platforms like Zhihu, Tieba, XHS, and Douyin directly into a local `.db` file using SQLAlchemy ORM models.

## Configure the SQLite Database Path

MediaCrawler defines the default SQLite file location in [`config/db_config.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/config/db_config.py). The `SQLITE_DB_PATH` variable constructs an absolute path to `database/sqlite_tables.db` relative to the project root.

```python

# config/db_config.py

import os

SQLITE_DB_PATH = os.path.join(
    os.path.dirname(os.path.dirname(__file__)),
    "database",
    "sqlite_tables.db"
)
sqlite_db_config = {"db_path": SQLITE_DB_PATH}

```

You can override this path by modifying the `sqlite_db_config` dictionary or setting a custom absolute path before initialization.

## Initialize the Database Schema

Before writing data, you must create the SQLite file and generate tables from the ORM models defined in [`database/models.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/database/models.py). MediaCrawler provides two methods to initialize the schema.

**CLI Method:**
Run the crawler with the `--init_db sqlite` flag to create the database file and all tables:

```bash
uv run main.py --init_db sqlite

```

**Programmatic Method:**
Call `create_tables()` directly from [`database/db_session.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/database/db_session.py) inside an async context:

```python

# database/db_session.py

async def create_tables(db_type: str = None):
    if db_type is None:
        db_type = config.SAVE_DATA_OPTION
    await create_database_if_not_exists(db_type)
    engine = get_async_engine(db_type)
    if engine:
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)

```

This function creates the async engine, ensures the database file exists, and executes SQLAlchemy's `create_all()` to build tables for all platforms (e.g., `zhihu_content`, `zhihu_comment`, `tieba_note`).

## Set SQLite as the Storage Engine

MediaCrawler uses a factory pattern to route data to specific storage implementations. The global `SAVE_DATA_OPTION` configuration determines which store the crawler instantiates.

In [`config/base_config.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/config/base_config.py), set the default storage option:

```python

# config/base_config.py

SAVE_DATA_OPTION = "sqlite"  # Options: csv, json, jsonl, sqlite, postgres, mongodb, excel

```

Alternatively, pass the `--save_data_option sqlite` CLI argument to override the config for a single run.

Each platform module contains a `StoreFactory` that maps the `"sqlite"` key to a SQLite-specific implementation. For example, in [`store/zhihu/__init__.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/store/zhihu/__init__.py):

```python

# store/zhihu/__init__.py

class ZhihuStoreFactory:
    STORES = {
        "csv": ZhihuCsvStoreImplement,
        "db": ZhihuDbStoreImplement,
        "postgres": ZhihuDbStoreImplement,
        "json": ZhihuJsonStoreImplement,
        "jsonl": ZhihuJsonlStoreImplement,
        "sqlite": ZhihuSqliteStoreImplement,   # SQLite store implementation

        "mongodb": ZhihuMongoStoreImplement,
        "excel": ZhihuExcelStoreImplement,
    }

```

The `ZhihuSqliteStoreImplement` class inherits from `ZhihuDbStoreImplement` (defined in [`store/zhihu/_store_impl.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/store/zhihu/_store_impl.py)), which handles the actual `INSERT` and `UPDATE` operations using SQLAlchemy's async session from `database/db_session.get_session`. This inheritance pattern ensures SQLite benefits from the same robust error handling and upsert logic as PostgreSQL.

## Complete Workflow Example

Combine these steps to run a crawl that stores data in SQLite.

**Step 1:** Initialize the database file and tables.

```bash
uv run main.py --init_db sqlite

```

**Step 2:** Execute a crawl with SQLite storage enabled.

```bash
uv run main.py --platform zhihu --lt keyword --type search --save_data_option sqlite

```

This command scrapes Zhihu search results and writes every `ZhihuContent`, `ZhihuComment`, and `ZhihuCreator` record into `database/sqlite_tables.db`.

**Programmatic Integration:**
Embed SQLite storage directly in Python scripts without CLI invocation:

```python
import asyncio
import config
from database import db_session, db
from store.zhihu import ZhihuStoreFactory
from model.m_zhihu import ZhihuContent

async def main():
    # 1. Select SQLite as the storage backend

    config.SAVE_DATA_OPTION = "sqlite"
    
    # 2. Initialize database and create tables

    await db.create_tables("sqlite")
    
    # 3. Store a record (simulating crawler output)

    content_data = ZhihuContent(
        content_id="12345",
        content_type="question",
        content_text="What is MediaCrawler?",
        content_url="https://www.zhihu.com/question/12345",
        title="MediaCrawler Overview",
        add_ts=await db_session.utils.get_current_timestamp()
    ).model_dump()
    
    store = ZhihuStoreFactory.create_store()
    await store.store_content(content_data)
    
    # 4. Close the database engine

    await db.close()

if __name__ == "__main__":
    asyncio.run(main())

```

The database automatically upserts records based on primary keys (such as `content_id` or `comment_id`), preventing duplicate rows across multiple crawl sessions.

## Summary

- **Configuration**: Set `SQLITE_DB_PATH` in [`config/db_config.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/config/db_config.py) to control the output file location.
- **Initialization**: Use `--init_db sqlite` or `await db.create_tables("sqlite")` to generate the schema from [`database/models.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/database/models.py).
- **Storage Selection**: Set `SAVE_DATA_OPTION = "sqlite"` in [`config/base_config.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/config/base_config.py) or pass `--save_data_option sqlite` via CLI.
- **Implementation**: The factory returns a SQLite-specific store (e.g., `ZhihuSqliteStoreImplement`) that inherits generic DB logic and uses SQLAlchemy async sessions for transactions.

## Frequently Asked Questions

### What file path does MediaCrawler use for the SQLite database by default?

By default, MediaCrawler creates the SQLite file at `database/sqlite_tables.db` relative to the project root. This path is constructed in [`config/db_config.py`](https://github.com/NanmiCoder/MediaCrawler/blob/main/config/db_config.py) using `os.path.dirname(os.path.dirname(__file__))` to ensure portability across operating systems.

### Does MediaCrawler support SQLite for all supported platforms?

Yes. The repository implements the same factory pattern for all platforms including Zhihu, Tieba, XHS (Xiaohongshu), Weibo, and Douyin. Each platform's `store` module contains a `*SqliteStoreImplement` class that maps to the generic DB store logic, ensuring consistent SQLite support across all crawlers.

### How does MediaCrawler prevent duplicate data in SQLite?

The store implementations use SQLAlchemy's upsert logic based on primary keys defined in the ORM models (such as `content_id` for posts or `comment_id` for comments). When `store_content()` or `store_comment()` is called, the code checks for existing records and updates them in place rather than inserting duplicates, as implemented in the `*DbStoreImplement` classes.

### Can I use SQLite alongside other storage formats like JSON or CSV?

Yes. You can change the storage backend for each run by modifying the `SAVE_DATA_OPTION` configuration or using the `--save_data_option` CLI flag. However, only one backend is active per crawl execution. To export SQLite data to CSV later, you would need to query the database using standard SQLAlchemy or `sqlite3` commands and write the results to your desired format.