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 Links
Meta
Author: Tsuyoshi Hombashi
Requires Python: >=3.9
Classifiers
Development Status
- 5 - Production/Stable
Intended Audience
- Developers
License
- OSI Approved :: MIT License
Operating System
- Microsoft :: Windows
- POSIX
- POSIX :: Linux
Programming Language
- Python :: 3
- Python :: 3.9
- Python :: 3.10
- Python :: 3.11
- Python :: 3.12
- Python :: 3.13
- Python :: 3 :: Only
- Python :: Implementation :: CPython
- Python :: Implementation :: PyPy
Topic
- Database
- Software Development :: Libraries
- Software Development :: Libraries :: Python Modules
Typing
- Typed
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
1.5.4
Mar 01, 2025
1.5.3
Jan 01, 2025
1.5.2
Dec 01, 2023
1.5.1
Nov 27, 2023
1.5.0
Nov 26, 2023
1.4.0
Sep 10, 2023
1.3.2
Jun 24, 2023
1.3.1
Jun 04, 2023
1.3.0
Jun 20, 2021
1.2.0
Jun 13, 2021
1.1.4
Dec 13, 2020
1.1.3
Aug 04, 2020
1.1.2
Apr 23, 2020
1.1.1
Apr 01, 2020
1.1.0
Mar 29, 2020
1.0.1
Mar 08, 2020
1.0.0
Feb 29, 2020
0.48.0
Feb 16, 2020
0.47.3
Feb 14, 2020
0.47.2
Feb 11, 2020
0.47.1
Feb 09, 2020
0.47.0
Feb 09, 2020
0.45.5
Jan 18, 2020
0.45.4
Jan 05, 2020
0.45.3
Jan 05, 2020
0.45.2
May 11, 2019
0.45.1
May 06, 2019
0.45.0
May 02, 2019
0.44.2
May 01, 2019
0.44.1
Apr 20, 2019
0.44.0
Apr 13, 2019
0.43.0
Mar 23, 2019
0.42.0
Mar 21, 2019
0.41.1
Mar 16, 2019
0.41.0
Mar 03, 2019
0.40.0
Feb 23, 2019
0.39.1
Feb 11, 2019
0.39.0
Feb 03, 2019
0.38.0
Jan 27, 2019
0.37.0
Jan 22, 2019
0.36.0
Jan 20, 2019
0.35.1
Jan 20, 2019
0.35.0
Jan 20, 2019
0.34.0
Jan 12, 2019
0.33.6
Jan 06, 2019
0.33.5
Jan 03, 2019
0.33.4
Dec 30, 2018
0.33.3
Dec 24, 2018
0.33.2
Nov 25, 2018
0.33.1
Oct 10, 2018
0.33.0
Sep 17, 2018
0.32.1
Sep 17, 2018
0.32.0
Sep 06, 2018
0.31.0
Sep 05, 2018
0.30.2
Aug 19, 2018
0.30.1
Aug 05, 2018
0.30.0
Aug 05, 2018
0.29.0
Jul 28, 2018
0.28.1
Jul 15, 2018
0.28.0
Jul 15, 2018
0.27.1
Jul 13, 2018
0.27.0
Jul 13, 2018
0.26.0
Jul 08, 2018
0.25.2
Jul 02, 2018
0.25.1
Jul 01, 2018
0.25.0
Jul 01, 2018
0.24.0
Jun 30, 2018
0.23.0
Jun 24, 2018
0.22.0
Jun 10, 2018
0.21.2
Jun 02, 2018
0.21.1
May 27, 2018
0.21.0
May 27, 2018
0.20.4
May 20, 2018
0.20.3
Apr 30, 2018
0.20.2
Apr 22, 2018
0.20.1
Apr 08, 2018
0.20.0
Mar 21, 2018
0.19.0
Nov 19, 2017
0.18.1
Nov 14, 2017
0.18.0
Nov 12, 2017
0.17.0
Nov 04, 2017
0.16.0
Oct 21, 2017
0.15.0
Aug 18, 2017
0.14.1
Aug 15, 2017
0.14.0
Aug 13, 2017
0.13.1
Aug 01, 2017
0.13.0
Jul 31, 2017
0.12.1
Jul 17, 2017
0.12.0
Jul 08, 2017
0.11.0
May 28, 2017
0.10.0
May 27, 2017
0.9.0
May 21, 2017
0.8.1
May 04, 2017
0.8.0
Apr 30, 2017
0.7.7
Apr 23, 2017
0.7.6
Feb 26, 2017
0.7.5
Jan 09, 2017
0.7.4
Dec 31, 2016
0.7.2
Dec 31, 2016
0.7.1
Dec 30, 2016
0.7.0
Dec 29, 2016
0.6.10
Dec 25, 2016
0.6.9
Dec 24, 2016
0.6.8
Dec 10, 2016
0.6.7
Nov 23, 2016
0.6.6
Nov 20, 2016
0.6.5
Nov 15, 2016
0.6.4
Nov 13, 2016
0.6.3
Nov 07, 2016
0.6.2
Nov 05, 2016
0.6.1
Oct 30, 2016
0.6.0
Oct 29, 2016
0.5.5
Sep 19, 2016
0.5.2
Sep 19, 2016
0.5.1
Sep 18, 2016
0.5.0
Sep 18, 2016
0.4.8
Sep 10, 2016
0.4.7
Sep 10, 2016
0.4.6
Aug 11, 2016
0.4.5
Aug 11, 2016
0.4.4
Jul 28, 2016
0.4.3
Jul 24, 2016
0.4.2
Jul 09, 2016
0.4.1
Jul 04, 2016
0.4.0
Jul 03, 2016
0.3.6
Jul 02, 2016
0.3.5
Jun 25, 2016
0.3.4
Jun 19, 2016
0.3.3
Jun 03, 2016
0.3.2
May 28, 2016
0.3.1
May 15, 2016
0.3.0
May 03, 2016
0.2.3
Mar 28, 2016
0.2.2
Mar 26, 2016
0.2.1
Mar 24, 2016
0.2.0
Mar 11, 2016
0.1.0
Feb 21, 2016
