You're Invited:Meet the Socket Team at BlackHat and DEF CON in Las Vegas, Aug 4-6.RSVP
Socket
Book a DemoInstallSign in
Socket

simple-sqlite3

Package Overview
Dependencies
Maintainers
1
Alerts
File Explorer

Advanced tools

Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

simple-sqlite3

A simple SQLite3 wrapper for Python

0.0.10
pipPyPI
Maintainers
1

Simple SQLite3

PyPI version License: MIT Python SQLite 3

Effortless, Pythonic SQLite database management with a modern API and CLI.

Simple SQLite3 is a lightweight, Pythonic wrapper for Python’s built-in sqlite3 module, making it easy to work with SQLite databases. It offers a user-friendly API for managing tables, inserting and querying data, and exporting results, with built-in support for JSON/CSV/TXT, schema evolution, and a convenient CLI.

Features

  • Easy-to-use API for SQLite database and table management.
  • Command-line interface (CLI) for database operations.
  • Support for exporting data to JSON, CSV and TXT formats.
  • Robust to both nested and non-nested data, with datetime support.
  • Utilities for processing queried results.

Installation

Requires Python 3.9+.

The package is available on PyPI:

pip install simple-sqlite3

Quick Start

Programmatic Usage

1. Insert and Query

This example demonstrates how to insert multiple rows into a table and query all records.

from simple_sqlite3 import Database

db = Database("database.db")
table = db.table("people")

table.insert(
    [
        {"name": "Amy", "age": 30, "city": "Helsinki"},
        {"name": "Bob", "age": 25, "city": "Cambridge"},
        {"name": "Cat", "age": 20, "city": "Paris"},
    ]
)

results = table.query("SELECT *")

print(results)

Example Output (from print(results)):

[
    {"name": "Amy", "age": 30, "city": "Helsinki"},
    {"name": "Bob", "age": 25, "city": "Cambridge"},
    {"name": "Cat", "age": 20, "city": "Paris"},
]

2. Bulk Insert Large Datasets

This example demonstrates how to efficiently insert many rows using either the insert_fast or insert_many methods. Both are designed for high performance with large datasets:

  • insert_fast: Use when your data is a list of dictionaries, and all dictionaries have the same keys (homogeneous). This is ideal for bulk-inserting structured data with column names, and supports schema inference or explicit schema.
  • insert_many: Use when your data is a list of tuples/lists (not dicts). This is fastest for simple, flat data and is less flexible (does not handle missing columns or nested data).

Note: Both methods provide significant speed efficiencies for large datasets. Use insert_fast for homogeneous dicts, and insert_many for tuple/list rows.

Example: Using insert_fast (homogeneous dicts)

from simple_sqlite3 import Database

db = Database(":memory:")
table = db.table("people")

data = [
    {"name": "Amy", "age": 30, "city": "Helsinki"},
    {"name": "Bob", "age": 25, "city": "Cambridge"},
    {"name": "Cat", "age": 20, "city": "Paris"},
]

table.insert_fast(data) # Or table.insert(data, fast=True)

Example: Using insert_many (tuples/lists)

from simple_sqlite3 import Database

db = Database(":memory:")
table = db.table("people")

rows = [
    ("Amy", 30, "Helsinki"),
    ("Bob", 25, "Cambridge"),
    ("Cat", 20, "Paris"),
]
columns = ("name", "age", "city")
schema = "name TEXT, age INTEGER, city TEXT" # Optional

table.insert_many(rows, columns, schema=schema)

3. Insert Nested Data

This example demonstrates inserting nested (dictionary) data, which is automatically stored as JSON in SQLite.

from simple_sqlite3 import Database

db = Database("database.db")
table = db.table("nested")

table.insert(
    [
        {
            "country": "Finland",
            "info": {
                "capital": "Helsinki",
                "latitude": 60.1699,
                "longitude": 24.9384,
            },
        },
        {
            "country": "France",
            "info": {
                "capital": "Paris",
                "latitude": 48.8566,
                "longitude": 2.3522,
            },
        },
        {
            "country": "Japan",
            "info": {
                "capital": "Tokyo",
                "latitude": 35.6895,
                "longitude": 139.6917,
            },
        },
    ]
)

4. Insert and Query Timeseries

This example demonstrates how to insert timeseries data using Python datetime objects and conditionally query rows, automatically parsing dates.

from simple_sqlite3 import Database
from datetime import datetime as dt

db = Database("database.db")
table = db.table("timeseries")

table.insert(
    [
        {"date": dt(2024, 6, 1), "value": 1.2345, "pair": "EURUSD"},
        {"date": dt(2024, 6, 2), "value": 1.2350, "pair": "EURUSD"},
        {"date": dt(2024, 6, 3), "value": 1.2360, "pair": "EURUSD"},
        {"date": dt(2024, 6, 4), "value": 1.2375, "pair": "EURUSD"},
        {"date": dt(2024, 6, 1), "value": 109.45, "pair": "USDJPY"},
        {"date": dt(2024, 6, 2), "value": 109.60, "pair": "USDJPY"},
        {"date": dt(2024, 6, 3), "value": 109.75, "pair": "USDJPY"},
        {"date": dt(2024, 6, 4), "value": 109.90, "pair": "USDJPY"},
    ]
)

results = table.query("SELECT date, value WHERE pair = 'EURUSD'", auto_parse_dates=True)

print(results)

Example Output (from print(results)):

[
    {"date": datetime.datetime(2024, 6, 1, 0, 0), "value": 1.2345},
    {"date": datetime.datetime(2024, 6, 2, 0, 0), "value": 1.235},
    {"date": datetime.datetime(2024, 6, 3, 0, 0), "value": 1.236},
    {"date": datetime.datetime(2024, 6, 4, 0, 0), "value": 1.2375},
]

5. Insert Mixed Data

This example demonstrates inserting mixed data, including deeply-nested dictionaries.

from simple_sqlite3 import Database
from datetime import datetime as dt

db = Database("database.db")
table = db.table("mixed_data")

table.insert(
    [
        {
            "date": dt(2024, 6, 1),
            "value": 1.2345,
            "pair": "EURUSD",
            "source": "ECB",
        },
        {
            "date": dt(2024, 6, 1),
            "value": 109.45,
            "pair": "USDJPY",
            "source": "BOJ",
        },
        {
            "date": dt(2024, 6, 2),
            "value": 0.8567,
            "pair": "EURGBP",
            "source": "ECB",
        },
        {
            "date": dt(2024, 6, 2),
            "value": 1.4200,
            "pair": "GBPUSD",
            "source": "FED",
        },
        {
            "date": dt(2024, 6, 2),
            "value": 1.2370,
            "pair": "EURUSD",
            "source": "ECB",
            "meta": {
                "confidence": 0.98,
                "contributors": ["ECB", "Bloomberg"],
                "valuation": {"buy": 0.4, "hold": 0.2, "sell": 0.4},
            },
        },
        {
            "date": dt(2024, 6, 3),
            "value": 109.80,
            "pair": "USDJPY",
            "source": "BOJ",
            "meta": {"confidence": 0.95, "contributors": ["BOJ"]},
        },
    ]
)

6. Insert Data Into Memory and Export as JSON, CSV and TXT

This example demonstrates inserting data into an in-memory database and exporting the table to JSON, CSV, and TXT formats.

from simple_sqlite3 import Database
from datetime import datetime as dt

db = Database(":memory:")
table = db.table("timeseries")

table.insert(
    [
        {"date": dt(2025, 5, 22), "value": 5328, "idx": "S&P 500"},
        {"date": dt(2025, 5, 21), "value": 5421, "idx": "S&P 500"},
        {"date": dt(2025, 5, 22), "value": 5448, "idx": "EURO STOXX 50"},
        {"date": dt(2025, 5, 21), "value": 5452, "idx": "EURO STOXX 50"},
    ]
)

table.export_to_json("timeseries.json")
table.export_to_csv("timeseries.csv")
table.export_to_txt("timeseries.txt")

7. Exporting Queried Results

This example demonstrates how to export queried results using the QueryResultsProcessor utility.

from simple_sqlite3 import Database
from simple_sqlite3.utils import QueryResultsProcessor
from datetime import datetime as dt

db = Database(":memory:")
table = db.table("timeseries")

table.insert(
    [
        {"date": dt(2025, 5, 22), "value": 5328, "idx": "S&P 500"},
        {"date": dt(2025, 5, 21), "value": 5421, "idx": "S&P 500"},
        {"date": dt(2025, 5, 22), "value": 5448, "idx": "EURO STOXX 50"},
        {"date": dt(2025, 5, 21), "value": 5452, "idx": "EURO STOXX 50"},
    ]
)

results = table.query("SELECT * WHERE idx = 'EURO STOXX 50'")

processor = QueryResultsProcessor(results)

processor.to_json("timeseries.json")
processor.to_csv("timeseries.csv")
processor.to_txt("timeseries.txt")

8. Grouping Queried Data

This example demonstrates how to group queried data into a matrix format for easy analysis.

from simple_sqlite3 import Database
from simple_sqlite3.utils import QueryResultsProcessor
from datetime import datetime as dt

db = Database(":memory:")
table = db.table("timeseries")

table.insert(
    [
        {"date": dt(2024, 6, 1), "value": 1.2345, "pair": "EURUSD"},
        {"date": dt(2024, 6, 2), "value": 1.2350, "pair": "EURUSD"},
        {"date": dt(2024, 6, 3), "value": 1.2360, "pair": "EURUSD"},
        {"date": dt(2024, 6, 4), "value": 1.2375, "pair": "EURUSD"},
        {"date": dt(2024, 6, 1), "value": 109.45, "pair": "USDJPY"},
        {"date": dt(2024, 6, 2), "value": 109.60, "pair": "USDJPY"},
        {"date": dt(2024, 6, 3), "value": 109.75, "pair": "USDJPY"},
        {"date": dt(2024, 6, 4), "value": 109.90, "pair": "USDJPY"},
    ]
)

results = table.query("SELECT *", auto_parse_dates=True)

processor = QueryResultsProcessor(results)

results_matrix_format = processor.to_matrix_format(
    index_key="date", group_key="pair", value_key="value"
)

print(results_matrix_format)

Example Output (from print(results)):

{
    "index": [
        datetime.datetime(2024, 6, 1, 0, 0),
        datetime.datetime(2024, 6, 2, 0, 0),
        datetime.datetime(2024, 6, 3, 0, 0),
        datetime.datetime(2024, 6, 4, 0, 0),
    ],
    "columns": ["EURUSD", "USDJPY"],
    "values": [[1.2345, 109.45], [1.235, 109.6], [1.236, 109.75], [1.2375, 109.9]],
}

9. Querying Directly From a Database

This example demonstrates querying directly from a pre-existing database called database.db with a table called timeseries using context manager.

from simple_sqlite3 import Database

with Database("database.db") as db:
    results = db.query("SELECT * FROM timeseries")

...

CLI Usage

The CLI is installed automatically with the package:

pip install simple-sqlite3

You can run the CLI using the db command (if your Python scripts directory is in your PATH or you have an active virtual environment), or with:

python -m simple_sqlite3.cli

CLI Command Overview

CommandDescription
db --helpShow help and available commands
queryQuery records from a table
insertInsert data from a file (CSV, JSON, TXT)
exportExport table data to a file (CSV, JSON, TXT)
rename-columnRename a column in a table
rename-columnsRename multiple columns in a table
delete-columnDelete a column from a table
delete-columnsDelete multiple columns from a table
rename-tableRename a table
delete-duplicatesRemove duplicate rows from a table
delete-tableDelete a table from the database
delete-databaseDelete the entire database file
vacuumReclaim unused space and optimize the database file

Common Flags

ShortLongDescription
-d--databasePath to the SQLite database
-t--tableName of the table
-f--fileInput/output file path
-s--sqlSQL query to execute
-F--forceForce action without confirmation

Show CLI help

Displays help and available commands.

db --help

Insert data from a JSON file into a table

Inserts data from timeseries.json into the timeseries table in database.db.

db insert -d database.db -t timeseries -f timeseries.json

If you don't have a timeseries.json file, you can create one with the following example content:

[
    {"date": "2025-05-22", "value": 5328, "idx": "S&P 500"},
    {"date": "2025-05-21", "value": 5421, "idx": "S&P 500"},
    {"date": "2025-05-22", "value": 5448, "idx": "EURO STOXX 50"},
    {"date": "2025-05-21", "value": 5452, "idx": "EURO STOXX 50"}
]

Query all rows from a table

Queries all rows from the timeseries table.

db query -d database.db -t timeseries -s "SELECT *"

Query database and save results to JSON format

Queries specific rows and saves results as results.json.

db query -d database.db -s "SELECT * FROM timeseries WHERE idx = 'EURO STOXX 50'" -f results.json

Remove duplicate rows from a table

Removes duplicate rows from the timeseries table.

db delete-duplicates -d database.db -t timeseries

Export a table to CSV format

Exports the timeseries table to timeseries.csv.

db export -d database.db -t timeseries -f timeseries.csv

Delete a table from the database

Deletes the timeseries table from database.db. Use -F to skip confirmation.

db delete-table -d database.db -t timeseries -F

Delete the entire database file

Deletes the database.db file. Use -F to skip confirmation.

db delete-database -d database.db -F

Advanced Features

  • Automatic WAL Mode: Write-Ahead Logging for better concurrency (default).
  • Schema Evolution: New columns are added automatically on insert if force=True (default).
  • Batch Export: Efficiently export large tables in batches to avoid memory issues.

License

This project is developed by Rob Suomi and licensed under the MIT License.
See the LICENSE file for details.

FAQs

Did you know?

Socket

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

Related posts