SimpleSQLite

Summary

SimpleSQLite is a Python library to simplify SQLite database operations: table creation, data insertion and get data as other data formats. Simple ORM functionality for SQLite.

Features

Automated SQLite table creation from data
Support various data types of record(s) insertion into a table:
- dict
- namedtuple
- list
- tuple
Create table(s) from:
- CSV file/text
- JSON file/text
- pandas.DataFrame instance
- tabledata.TableData instance loaded by pytablereader
Get data from a table as:
- pandas.DataFrame instance
- tabledata.TableData instance
Simple object-relational mapping (ORM) functionality

Examples

Create a table

Create a table from a data matrix

Sample Code:

from simplesqlite import SimpleSQLite


table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")

# create table -----
data_matrix = [[1, 1.1, "aaa", 1, 1], [2, 2.2, "bbb", 2.2, 2.2], [3, 3.3, "ccc", 3, "ccc"]]
con.create_table_from_data_matrix(
    table_name,
    ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
    data_matrix,
)

# display data type for each column in the table -----
print(con.schema_extractor.fetch_table_schema(table_name).dumps())

# display values in the table -----
print("records:")
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    print(record)

Output:

.. table:: sample_table

    +---------+-------+-----------+--------+------+-----+
    |Attribute| Type  |PRIMARY KEY|NOT NULL|UNIQUE|Index|
    +=========+=======+===========+========+======+=====+
    |attr_a   |INTEGER|           |        |      |     |
    +---------+-------+-----------+--------+------+-----+
    |attr_b   |REAL   |           |        |      |     |
    +---------+-------+-----------+--------+------+-----+
    |attr_c   |TEXT   |           |        |      |     |
    +---------+-------+-----------+--------+------+-----+
    |attr_d   |REAL   |           |        |      |     |
    +---------+-------+-----------+--------+------+-----+
    |attr_e   |TEXT   |           |        |      |     |
    +---------+-------+-----------+--------+------+-----+


records:
(1, 1.1, 'aaa', 1.0, '1')
(2, 2.2, 'bbb', 2.2, '2.2')
(3, 3.3, 'ccc', 3.0, 'ccc')

Create a table from CSV

Sample Code:

from simplesqlite import SimpleSQLite

with open("sample_data.csv", "w") as f:
    f.write("\n".join([
        '"attr_a","attr_b","attr_c"',
        '1,4,"a"',
        '2,2.1,"bb"',
        '3,120.9,"ccc"',
    ]))

# create table ---
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_csv("sample_data.csv")

# output ---
table_name = "sample_data"
print(con.fetch_attr_names(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    print(record)

Output:

['attr_a', 'attr_b', 'attr_c']
(1, 4.0, 'a')
(2, 2.1, 'bb')
(3, 120.9, 'ccc')

Create a table from pandas.DataFrame

Sample Code:

from simplesqlite import SimpleSQLite
import pandas

con = SimpleSQLite("pandas_df.sqlite")

con.create_table_from_dataframe(pandas.DataFrame(
    [
        [0, 0.1, "a"],
        [1, 1.1, "bb"],
        [2, 2.2, "ccc"],
    ],
    columns=['id', 'value', 'name']
), table_name="pandas_df")

Output:

$ sqlite3 pandas_df.sqlite
sqlite> .schema
CREATE TABLE 'pandas_df' (id INTEGER, value REAL, name TEXT);

Insert records into a table

Insert dictionary

Sample Code:

from simplesqlite import SimpleSQLite

table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_data_matrix(
    table_name,
    ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
    [[1, 1.1, "aaa", 1,   1]])

con.insert(
    table_name,
    record={
        "attr_a": 4,
        "attr_b": 4.4,
        "attr_c": "ddd",
        "attr_d": 4.44,
        "attr_e": "hoge",
    })
con.insert_many(
    table_name,
    records=[
        {
            "attr_a": 5,
            "attr_b": 5.5,
            "attr_c": "eee",
            "attr_d": 5.55,
            "attr_e": "foo",
        },
        {
            "attr_a": 6,
            "attr_c": "fff",
        },
    ])

result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    print(record)

Output:

(1, 1.1, 'aaa', 1, 1)
(4, 4.4, 'ddd', 4.44, 'hoge')
(5, 5.5, 'eee', 5.55, 'foo')
(6, None, 'fff', None, None)

Insert list/tuple/namedtuple

Sample Code:

from collections import namedtuple
from simplesqlite import SimpleSQLite

table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_data_matrix(
    table_name,
    ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
    [[1, 1.1, "aaa", 1, 1]],
)

# insert namedtuple
SampleTuple = namedtuple("SampleTuple", "attr_a attr_b attr_c attr_d attr_e")

con.insert(table_name, record=[7, 7.7, "fff", 7.77, "bar"])
con.insert_many(
    table_name,
    records=[(8, 8.8, "ggg", 8.88, "foobar"), SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge")],
)

# print
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    print(record)

Output:

(1, 1.1, 'aaa', 1, 1)
(7, 7.7, 'fff', 7.77, 'bar')
(8, 8.8, 'ggg', 8.88, 'foobar')
(9, 9.9, 'ggg', 9.99, 'hogehoge')

Fetch data from a table as pandas DataFrame

Sample Code:

from simplesqlite import SimpleSQLite

con = SimpleSQLite("sample.sqlite", "w", profile=True)

con.create_table_from_data_matrix(
    "sample_table",
    ["a", "b", "c", "d", "e"],
    [
        [1, 1.1, "aaa", 1,   1],
        [2, 2.2, "bbb", 2.2, 2.2],
        [3, 3.3, "ccc", 3,   "ccc"],
    ])

print(con.select_as_dataframe(table_name="sample_table"))

Output:

$ sample/select_as_dataframe.py
   a    b    c    d    e
0  1  1.1  aaa  1.0    1
1  2  2.2  bbb  2.2  2.2
2  3  3.3  ccc  3.0  ccc

ORM functionality

Sample Code:

from simplesqlite import connect_memdb
from simplesqlite.model import Integer, Model, Real, Text


class Sample(Model):
    foo_id = Integer(primary_key=True)
    name = Text(not_null=True, unique=True)
    value = Real(default=0)


def main() -> None:
    con = connect_memdb()

    Sample.attach(con)
    Sample.create()
    Sample.insert(Sample(name="abc", value=0.1))
    Sample.insert(Sample(name="xyz", value=1.11))
    Sample.insert(Sample(name="bar"))

    print(Sample.fetch_schema().dumps())
    print("records:")
    for record in Sample.select():
        print(f"    {record}")


if __name__ == "__main__":
    main()

Output:

.. table:: sample

    +--------+---------+----------+-----+---------+-------+-------+
    | Field  |  Type   | Nullable | Key | Default | Index | Extra |
    +========+=========+==========+=====+=========+=======+=======+
    | foo_id | INTEGER | YES      | PRI | NULL    |   X   |       |
    +--------+---------+----------+-----+---------+-------+-------+
    | name   | TEXT    | NO       | UNI |         |   X   |       |
    +--------+---------+----------+-----+---------+-------+-------+
    | value  | REAL    | YES      |     | 0       |       |       |
    +--------+---------+----------+-----+---------+-------+-------+

records:
    Sample (foo_id=1, name=abc, value=0.1)
    Sample (foo_id=2, name=xyz, value=1.11)
    Sample (foo_id=3, name=bar, value=0.0)

For more information

More examples are available at https://simplesqlite.rtfd.io/en/latest/pages/examples/index.html

Installation

Install from PyPI

pip install SimpleSQLite

Install from PPA (for Ubuntu)

sudo add-apt-repository ppa:thombashi/ppa
sudo apt update
sudo apt install python3-simplesqlite

Dependencies

Python 3.9+
Python package dependencies (automatically installed)

Optional Dependencies

loguru
- Used for logging if the package installed
pandas
pytablereader

Metadata

Release files for SimpleSQLite 1.5.4

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for SimpleSQLite 1.5.4
File	Size	Uploaded
simplesqlite-1.5.4.tar.gz	62.3 kB	Mar 1, 2025	Details

Built distribution (wheel)

Table of built distributions (wheels) for SimpleSQLite 1.5.4
File	Interpreter	ABI	Platform	Reset
simplesqlite-1.5.4-py3-none-any.whl 34.4 kB Mar 1, 2025	Python 3	none	any	Details

Total release size: 96.7 kB

Release files / simplesqlite-1.5.4.tar.gz

Download URL	simplesqlite-1.5.4.tar.gz
Size	62.3 kB
Tags	Source
SHA-256 checksum How to use checksums	`7007d2abed75f2f9b0f1e544a9af2b6d0bd341d8b5519446708e6595ea092d4c`
BLAKE2b-256 checksum How to use checksums	`1a7394ee1a1711ca04c7269f2d32472d10ad8cdc85ebf32c42e1cfc6924d39d8`
Upload date	Mar 1, 2025
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/6.1.0 CPython/3.12.9`

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/thombashi/SimpleSQLite
Publishing commit: github.com/thombashi/SimpleSQLite/tree/69c4ad8fd4224fbd00e6fe929a3cc8ba8da3e324

Workflow

Publishing configuration: github.com/thombashi/SimpleSQLite/blob/69c4ad8fd4224fbd00e6fe929a3cc8ba8da3e324/.github/workflows/publish.yml
Publishing logs: github.com/thombashi/SimpleSQLite/actions/runs/13606878712/attempts/2

Artifact

Subject: simplesqlite-1.5.4.tar.gz
SHA-256 checksum: 7007d2abed75f2f9b0f1e544a9af2b6d0bd341d8b5519446708e6595ea092d4c Verifying attestations

Release files / simplesqlite-1.5.4-py3-none-any.whl

Download URL	simplesqlite-1.5.4-py3-none-any.whl
Size	34.4 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`6f4b4ed8b039b562d1dff8ca8e46281addaf1ad6970553285c16592b61183a8f`
BLAKE2b-256 checksum How to use checksums	`c1b89a1ecfc654270bb4755587cb419b65357fcca3f10fc6f35d2f4799cb8078`
Upload date	Mar 1, 2025
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/6.1.0 CPython/3.12.9`

Provenance

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/thombashi/SimpleSQLite
Publishing commit: github.com/thombashi/SimpleSQLite/tree/69c4ad8fd4224fbd00e6fe929a3cc8ba8da3e324

Workflow

Publishing configuration: github.com/thombashi/SimpleSQLite/blob/69c4ad8fd4224fbd00e6fe929a3cc8ba8da3e324/.github/workflows/publish.yml
Publishing logs: github.com/thombashi/SimpleSQLite/actions/runs/13606878712/attempts/2

Artifact

Subject: simplesqlite-1.5.4-py3-none-any.whl
SHA-256 checksum: 6f4b4ed8b039b562d1dff8ca8e46281addaf1ad6970553285c16592b61183a8f Verifying attestations