
Research
/Security News
Toptal’s GitHub Organization Hijacked: 10 Malicious Packages Published
Threat actors hijacked Toptal’s GitHub org, publishing npm packages with malicious payloads that steal tokens and attempt to wipe victim systems.
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.
Requires Python 3.9+.
The package is available on PyPI:
pip install simple-sqlite3
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"},
]
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, andinsert_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)
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,
},
},
]
)
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},
]
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"]},
},
]
)
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")
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")
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]],
}
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")
...
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
Command | Description |
---|---|
db --help | Show help and available commands |
query | Query records from a table |
insert | Insert data from a file (CSV, JSON, TXT) |
export | Export table data to a file (CSV, JSON, TXT) |
rename-column | Rename a column in a table |
rename-columns | Rename multiple columns in a table |
delete-column | Delete a column from a table |
delete-columns | Delete multiple columns from a table |
rename-table | Rename a table |
delete-duplicates | Remove duplicate rows from a table |
delete-table | Delete a table from the database |
delete-database | Delete the entire database file |
vacuum | Reclaim unused space and optimize the database file |
Short | Long | Description |
---|---|---|
-d | --database | Path to the SQLite database |
-t | --table | Name of the table |
-f | --file | Input/output file path |
-s | --sql | SQL query to execute |
-F | --force | Force action without confirmation |
Displays help and available commands.
db --help
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"}
]
Queries all rows from the timeseries
table.
db query -d database.db -t timeseries -s "SELECT *"
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
Removes duplicate rows from the timeseries
table.
db delete-duplicates -d database.db -t timeseries
Exports the timeseries
table to timeseries.csv
.
db export -d database.db -t timeseries -f timeseries.csv
Deletes the timeseries
table from database.db
. Use -F
to skip confirmation.
db delete-table -d database.db -t timeseries -F
Deletes the database.db
file. Use -F
to skip confirmation.
db delete-database -d database.db -F
force=True
(default).This project is developed by Rob Suomi and licensed under the MIT License.
See the LICENSE file for details.
FAQs
A simple SQLite3 wrapper for Python
We found that simple-sqlite3 demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
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.
Research
/Security News
Threat actors hijacked Toptal’s GitHub org, publishing npm packages with malicious payloads that steal tokens and attempt to wipe victim systems.
Research
/Security News
Socket researchers investigate 4 malicious npm and PyPI packages with 56,000+ downloads that install surveillance malware.
Security News
The ongoing npm phishing campaign escalates as attackers hijack the popular 'is' package, embedding malware in multiple versions.