SimpleSQLite

• Summary
  • Features
• Examples
  • Create a table
  • Insert records into a table
  • Fetch data from a table as pandas DataFrame
  • ORM functionality
  • For more information
• Installation
  • Install from PyPI
  • Install from PPA (for Ubuntu)
• Dependencies
  • Optional Dependencies
• Documentation
• Related Project
• Sponsors

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.7+
• Python package dependencies (automatically installed)

Optional Dependencies

• loguru

  • Used for logging if the package installed

• pandas
• pytablereader

Documentation

https://simplesqlite.rtfd.io/

Related Project

• sqlitebiter: CLI tool to convert CSV/Excel/HTML/JSON/LTSV/Markdown/TSV/Google-Sheets SQLite database by using SimpleSQLite

Sponsors

Become a sponsor