How to Store Crawled Data in SQLite with MediaCrawler

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 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. The SQLITE_DB_PATH variable constructs an absolute path to database/sqlite_tables.db relative to the project root.

# 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. 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:

uv run main.py --init_db sqlite

Programmatic Method: Call create_tables() directly from database/db_session.py inside an async context:

# 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, set the default storage option:

# 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:

# 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), 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.

uv run main.py --init_db sqlite

Step 2: Execute a crawl with SQLite storage enabled.

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:

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 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.
• Storage Selection: Set SAVE_DATA_OPTION = "sqlite" in 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}