• Stars
    star
    128
  • Rank 274,452 (Top 6 %)
  • Language
    Python
  • License
    MIT License
  • Created over 8 years ago
  • Updated 6 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

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.

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 Test coverage GitHub stars

Features

Examples

Create a table

Create a table from 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, u'a')
(2, 2.1, u'bb')
(3, 120.9, u'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()


def main():
    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", value=2.22))

    print(Sample.fetch_schema().dumps())
    print("records:")
    for record in Sample.select():
        print("    {}".format(record))

    return 0


if __name__ == "__main__":
    sys.exit(main())
Output:
.. table:: sample

    +---------+-------+-----------+--------+------+-----+
    |Attribute| Type  |PRIMARY KEY|NOT NULL|UNIQUE|Index|
    +=========+=======+===========+========+======+=====+
    |foo_id   |INTEGER|X          |        |      |     |
    +---------+-------+-----------+--------+------+-----+
    |name     |TEXT   |           |X       |X     |     |
    +---------+-------+-----------+--------+------+-----+
    |value    |REAL   |           |        |      |     |
    +---------+-------+-----------+--------+------+-----+


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=2.22

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/

Related Project

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

Sponsors

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

Become a sponsor

More Repositories

1

sqlitebiter

A CLI tool to convert CSV / Excel / HTML / JSON / Jupyter Notebook / LDJSON / LTSV / Markdown / SQLite / SSV / TSV / Google-Sheets to a SQLite database file.
Python
831
star
2

tcconfig

A tc command wrapper. Make it easy to set up traffic control of network bandwidth/latency/packet-loss/packet-corruption/etc. to a network-interface/Docker-container(veth).
Python
767
star
3

pytablewriter

pytablewriter is a Python library to write a table in various formats: AsciiDoc / CSV / Elasticsearch / HTML / JavaScript / JSON / LaTeX / LDJSON / LTSV / Markdown / MediaWiki / NumPy / Excel / Pandas / Python / reStructuredText / SQLite / TOML / TSV.
Python
594
star
4

allpairspy

A python library for test combinations generator. The generator allows one to create a set of tests using "pairwise combinations" method, reducing a number of combinations of variables into a lesser set that covers most situations.
Python
251
star
5

pathvalidate

A Python library to sanitize/validate a string such as filenames/file-paths/etc.
Python
207
star
6

pytablereader

A Python library to load structured table data from files/strings/URL with various data format: CSV / Excel / Google-Sheets / HTML / JSON / LDJSON / LTSV / Markdown / SQLite / TSV.
Python
105
star
7

DateTimeRange

DateTimeRange is a Python library to handle a time range. e.g. check whether a time is within the time range, get the intersection of time ranges, truncate a time range, iterate through a time range, and so forth.
Python
100
star
8

pingparsing

pingparsing is a CLI-tool/Python-library parser and transmitter for ping command ↪️
Python
75
star
9

pytest-md-report

A pytest plugin to generate test outcomes reports with markdown table format.
Python
28
star
10

subprocrunner

A Python wrapper library for subprocess module.
Python
21
star
11

typepy

A Python library for variable type checker/validator/converter at a run time.
Python
16
star
12

DataProperty

A Python library for extract property from data.
Python
15
star
13

humanreadable

humanreadable is a Python library to convert human-readable values to other units.
Python
15
star
14

cleanpy

cleanpy is a CLI tool to remove caches and temporary files related to Python.
Python
11
star
15

sqliteschema

sqliteschema is a Python library to dump table schema of a SQLite database file.
Python
10
star
16

elasticsearch-faker

elasticsearch-faker is a CLI tool to generate fake data for Elasticsearch.
Python
10
star
17

tcolorpy

tcolopy is a Python library to apply true color for terminal text.
Python
10
star
18

excelrd

excelrd is a modified version of xlrd to work for the latest Python versions.
Python
9
star
19

cmakew

cmakew is a CMake wrapper CLI tool.
Python
9
star
20

ghscard

A JavaScript widget to generate interactive GitHub user/repository/organization cards for static web pages (like GitHub pages).
JavaScript
9
star
21

tabledata

tabledata is a Python library to represent tabular data.
Python
7
star
22

thank-you-stars

thank-you-stars is a CLI tool to stars to a PyPI package and its dependencies hosted on GitHub ⭐
Python
7
star
23

mbstrdecoder

Python library for multi-byte character string decoder.
Python
6
star
24

pytest-discord

A pytest plugin to notify test results to a Discord channel.
Python
5
star
25

tblfaker

tblfaker is a Python library to generate fake tabular data.
Python
5
star
26

CriterionSample

📔 Examples of Criterion (https://github.com/Snaipe/Criterion)
C
5
star
27

python-lib-project-template

A project template for a Python library with CI configurations
Python
5
star
28

envinfopy

envinfopy is a Python Library to get execution environment information.
Python
4
star
29

NFStest

NFStest mirror with command help wiki.
Python
4
star
30

readmemaker

A Python utility library to help make a README file from document files.
Python
4
star
31

retryrequests

A Python library that make HTTP requests with exponential back-off retry by using requests package.
Python
4
star
32

gtest-project-template

C++ test project template using Google Test.
C++
4
star
33

appconfigpy

A Python library to create/load an application configuration file.
Python
3
star
34

msgfy

msgfy is a Python library for convert Exception instance to a human-readable error message.
Python
3
star
35

python-cli-project-template

A project template for a Python CLI tool with CI configurations
Python
2
star
36

releasecmd

releasecmd is a release subcommand for setup.py (setuptools.setup). The subcommand create a git tag and push, and upload packages to PyPI.
Python
2
star
37

homebrew-sqlitebiter

Homebrew Formula for sqlitebiter
Ruby
2
star
38

docker-opengrok

Docker image for OpenGrok
Dockerfile
2
star
39

vscode-snippets

User defined snippets for Visual Studio Code
2
star
40

pytablewriter-altrow-theme

pytablewriter-altrow-theme is a pytablewriter plugin to provide a theme that colored rows alternatively.
Python
1
star
41

thutils

Personal python utility library (may delete in the future)
Python
1
star
42

install-gh

Simple one-liner installer of gh (cli/cli) from the release assets.
Shell
1
star
43

Connectathon_README

Connectathon test suite README
HTML
1
star
44

docker-alias

Docker aliases and functions 🐳
Shell
1
star
45

universal-ctags-installer

Build and installation script for Universal Ctags
Shell
1
star
46

df-diskcache

df-diskcache is a Python library for caching pandas.DataFrame objects to local disk.
Python
1
star
47

pathvalidate-cli

pathvalidate-cli is a command line interface for pathvalidate library. The tool can do sanitize/validate a string such as file-names/file-paths.
Python
1
star
48

gh-upgrade

gh-upgrade is a gh extension that updates the gh and its extensions to the latest version.
Shell
1
star
49

PythonExamples

📔 Learning Python libraries by examples.
Jupyter Notebook
1
star
50

dotfiles

♻️ dotfiles
Python
1
star