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

Project details


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 details)

Uploaded Source

Built Distribution

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

Uploaded Python 3

File details

Details for the file SimpleSQLite-1.5.2.tar.gz.

File metadata

  • Download URL: SimpleSQLite-1.5.2.tar.gz
  • Upload date:
  • Size: 60.9 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/4.0.2 CPython/3.11.4

File hashes

Hashes for SimpleSQLite-1.5.2.tar.gz
Algorithm Hash digest
SHA256 f9a25b9e43d7aa28f551d170a42cfae10139c5c81a1bcb8776f857816ef5ae0f
MD5 a907352592539345bf8145c3e39015cb
BLAKE2b-256 c80792e3291fda6f1bbf6e7ff4721d87566e1615572d9c205ef64398c5d22efe

See more details on using hashes here.

File details

Details for the file SimpleSQLite-1.5.2-py3-none-any.whl.

File metadata

File hashes

Hashes for SimpleSQLite-1.5.2-py3-none-any.whl
Algorithm Hash digest
SHA256 f62300ce1e367fd5234a959fa3a32d7c81bdf719204ebc1e70c4f3cd91672dc6
MD5 c95b5b223d7fbfa13d9a113af4b1f73a
BLAKE2b-256 f5d5f51ce79a7d07de6d3f684a07cc7554bfa7a0d3f4dbffe258e79e832c364b

See more details on using hashes here.

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