Skip to main content

SimpleSQLite is a python library to simplify the table creation and data insertion in SQLite database.

Project description

SimpleSQLite

https://badge.fury.io/py/SimpleSQLite.svg https://img.shields.io/pypi/pyversions/SimpleSQLite.svg Linux CI test status https://img.shields.io/appveyor/ci/thombashi/simplesqlite/master.svg?label=Windows https://coveralls.io/repos/github/thombashi/SimpleSQLite/badge.svg?branch=master

Summary

SimpleSQLite is a python library to simplify the table creation and data insertion in SQLite database.

Feature

  • Automatic table creation from data

  • Support various data types of record(s) insertion into a table:
    • dictionary

    • namedtuple

    • list

    • tuple

  • Create table(s) from:
    • CSV file/text

    • HTML file/text

    • JSON file/text

    • Microsoft Excel TM file

    • Google Sheets

Examples

Create a table

Create a table from data matrix

import json
from simplesqlite import SimpleSQLite
import six

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_with_data(
    table_name,
    attribute_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
    data_matrix=data_matrix)

# display values in the table -----
six.print_(con.get_attribute_name_list(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    six.print_(record)

# display data type for each column in the table -----
six.print_(json.dumps(con.get_attr_type(table_name), indent=4))
['attr_a', 'attr_b', 'attr_c', 'attr_d', 'attr_e']
(1, 1.1, u'aaa', 1.0, u'1')
(2, 2.2, u'bbb', 2.2, u'2.2')
(3, 3.3, u'ccc', 3.0, u'ccc')
{
    "attr_b": " REAL",
    "attr_c": " TEXT",
    "attr_a": " INTEGER",
    "attr_d": " REAL",
    "attr_e": " TEXT"
}

Insert records into a table

Insert dictionary

from simplesqlite import SimpleSQLite
import six

table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_with_data(
    table_name,
    attribute_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
    data_matrix=[[1, 1.1, "aaa", 1,   1]])

con.insert(
    table_name,
    insert_record={
        "attr_a": 4,
        "attr_b": 4.4,
        "attr_c": "ddd",
        "attr_d": 4.44,
        "attr_e": "hoge",
    }
)
con.insert_many(
    table_name,
    insert_record_list=[
        {
            "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():
    six.print_(record)
(1, 1.1, u'aaa', 1, 1)
(4, 4.4, u'ddd', 4.44, u'hoge')
(5, 5.5, u'eee', 5.55, u'foo')
(6, u'NULL', u'fff', u'NULL', u'NULL')

Insert list/tuple/namedtuple

from collections import namedtuple
from simplesqlite import SimpleSQLite
import six

table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_with_data(
    table_name,
    attribute_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
    data_matrix=[[1, 1.1, "aaa", 1,   1]])

SampleTuple = namedtuple(
    "SampleTuple", "attr_a attr_b attr_c attr_d attr_e")

con.insert(table_name, insert_record=[7, 7.7, "fff", 7.77, "bar"])
con.insert_many(
    table_name,
    insert_record_list=[
        (8, 8.8, "ggg", 8.88, "foobar"),
        SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge"),
    ]
)

result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    six.print_(record)
(1, 1.1, u'aaa', 1, 1)
(7, 7.7, u'fff', 7.77, u'bar')
(8, 8.8, u'ggg', 8.88, u'foobar')
(9, 9.9, u'ggg', 9.99, u'hogehoge')

For more information

More examples are available at http://simplesqlite.readthedocs.org/en/latest/pages/examples/index.html

Installation

pip install SimpleSQLite

Dependencies

Python 2.7+ or 3.3+

Mandatory

Optional

  • lxml (Faster HTML convert if installed)

Test dependencies

Documentation

http://simplesqlite.readthedocs.org/en/latest/

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

SimpleSQLite-0.6.8.tar.gz (41.2 kB view hashes)

Uploaded Source

Built Distribution

SimpleSQLite-0.6.8-py2.py3-none-any.whl (22.0 kB view hashes)

Uploaded Python 2 Python 3

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