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.
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
Optional Dependencies
- loguru
Used for logging if the package installed
Documentation
Sponsors
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
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
Algorithm | Hash digest | |
---|---|---|
SHA256 | f9a25b9e43d7aa28f551d170a42cfae10139c5c81a1bcb8776f857816ef5ae0f |
|
MD5 | a907352592539345bf8145c3e39015cb |
|
BLAKE2b-256 | c80792e3291fda6f1bbf6e7ff4721d87566e1615572d9c205ef64398c5d22efe |
File details
Details for the file SimpleSQLite-1.5.2-py3-none-any.whl
.
File metadata
- Download URL: SimpleSQLite-1.5.2-py3-none-any.whl
- Upload date:
- Size: 34.0 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/4.0.2 CPython/3.11.4
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | f62300ce1e367fd5234a959fa3a32d7c81bdf719204ebc1e70c4f3cd91672dc6 |
|
MD5 | c95b5b223d7fbfa13d9a113af4b1f73a |
|
BLAKE2b-256 | f5d5f51ce79a7d07de6d3f684a07cc7554bfa7a0d3f4dbffe258e79e832c364b |