Skip to main content

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

Project description

SimpleSQLite

https://img.shields.io/pypi/pyversions/SimpleSQLite.svg https://travis-ci.org/thombashi/SimpleSQLite.svg?branch=master https://ci.appveyor.com/api/projects/status/b564t8y34lkcd1hq/branch/master?svg=true 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 type for insertion : dictionary, namedtuple, list and tuple

  • Create table from csv file

Installation

pip install SimpleSQLite

Usage

Create table

Create table from data matrix

from simplesqlite import SimpleSQLite

con = SimpleSQLite("sample.sqlite")

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

# display values -----
print(con.get_attribute_name_list("sample_table"))
result = con.select(select="*", table_name="sample_table")
for record in result.fetchall():
    print(record)

# display data type for each column -----
print(con.get_attribute_type_list(table_name="sample_table"))
['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')
(u'integer', u'real', u'text', u'real', u'text')

Create table from csv file

Input: sample_data.csv
"attr_a","attr_b","attr_c"
1,4,"a"
2,2.1,"bb"
3,120.9,"ccc"
Example
from simplesqlite import SimpleSQLite

con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_csv(csv_path="sample_data.csv")

print(con.get_attribute_name_list("sample_data"))
result = con.select(select="*", table_name="sample_data")
for record in result.fetchall():
    print(record)
Output of example
['attr_a', 'attr_b', 'attr_c']
(1, 4.0, u'a')
(2, 2.1, u'bb')
(3, 120.9, u'ccc')

Insert records

Insert dictionary

from simplesqlite import SimpleSQLite

con = SimpleSQLite("sample.sqlite", "w")
con.create_table_with_data(
    table_name="sample_table",
    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="sample_table",
    insert_record={
        "attr_a": 4,
        "attr_b": 4.4,
        "attr_c": "ddd",
        "attr_d": 4.44,
        "attr_e": "hoge",
    }
)
con.insert_many(
    table_name="sample_table",
    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="sample_table")
for record in result.fetchall():
    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

con = SimpleSQLite("sample.sqlite", "w")
con.create_table_with_data(
    table_name="sample_table",
    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="sample_table",
    insert_record=[7, 7.7, "fff", 7.77, "bar"])
con.insert_many(
    table_name="sample_table",
    insert_record_list=[
        (8, 8.8, "ggg", 8.88, "foobar"),
        SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge"),
    ]
)

result = con.select(select="*", table_name="sample_table")
for record in result.fetchall():
    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')

Documentation

http://simplesqlite.readthedocs.org/en/stable/apis/simplesqlite.html

Dependencies

Python 2.5+ or 3.3+

Test dependencies

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.2.0.tar.gz (19.5 kB view hashes)

Uploaded Source

Built Distribution

SimpleSQLite-0.2.0-py2.py3-none-any.whl (13.2 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