Skip to main content

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.

Project description

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.

PyPI package version Supported Python versions Supported Python implementations CI status of Linux/macOS/Windows CodeQL Test coverage

Features

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

Optional Dependencies

Documentation

https://simplesqlite.rtfd.io/

Sponsors

Charles Becker (chasbecker) onetime: Arturi0 onetime: Dmitry Belyaev (b4tman)

Become a sponsor

Release history Release notifications | RSS feed

This version

1.5.2

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

SimpleSQLite-1.5.2.tar.gz (60.9 kB view hashes)

Uploaded Source

Built Distribution

SimpleSQLite-1.5.2-py3-none-any.whl (34.0 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page