Skip to main content

SimpleSQLite is a Python library to simplify SQLite database operations: table creation, data insertion and get data as other data formats.

Project description

Home-page: https://github.com/thombashi/SimpleSQLite
Author: Tsuyoshi Hombashi
Author-email: tsuyoshi.hombashi@gmail.com
License: MIT License
Description:
SimpleSQLite
==============
.. image:: https://badge.fury.io/py/SimpleSQLite.svg
:target: https://badge.fury.io/py/SimpleSQLite

.. image:: https://img.shields.io/travis/thombashi/SimpleSQLite/master.svg?label=Linux
:target: https://travis-ci.org/thombashi/SimpleSQLite
:alt: Linux CI test status

.. image:: https://img.shields.io/appveyor/ci/thombashi/simplesqlite/master.svg?label=Windows
:target: https://ci.appveyor.com/project/thombashi/simplesqlite/branch/master

.. image:: https://coveralls.io/repos/github/thombashi/SimpleSQLite/badge.svg?branch=master
:target: https://coveralls.io/github/thombashi/SimpleSQLite?branch=master

.. image:: https://img.shields.io/github/stars/thombashi/SimpleSQLite.svg?style=social&label=Star
:target: https://github.com/thombashi/SimpleSQLite


Summary
---------
SimpleSQLite is a Python library to simplify SQLite database operations: table creation, data insertion and get data as other data formats.

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 <http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html>`__ instance
- `pytablereader.TableData <http://pytablereader.rtfd.io/en/latest/pages/reference/data.html#tabledata>`__ instance loaded by `pytablereader <https://github.com/thombashi/pytablereader>`__
- Get data from a table as:
- `pandas.DataFrame <http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html>`__ instance
- `pytablereader.TableData <http://pytablereader.rtfd.io/en/latest/pages/reference/data.html#tabledata>`__ instance




Examples
==========

Create a table
----------------

Create a table from data matrix
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:Sample Code:
.. code-block:: python

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

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

# display data type for each column in the table -----
print(json.dumps(con.get_attr_type(table_name), indent=4))

:Output:
.. code-block:: none

['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"
}


Create a table from CSV
~~~~~~~~~~~~~~~~~~~~~~~~~
:Sample Code:
.. code-block:: python

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

:Output:
.. code-block:: none

['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:
.. code-block:: python

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:
.. code-block:: none

$ 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:
.. code-block:: python

from simplesqlite import SimpleSQLite

table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_data_matrix(
table_name,
attr_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():
print(record)

:Output:
.. code-block:: none

(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:
.. code-block:: python

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_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():
print(record)

:Output:
.. code-block:: none

(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')


Get Data from a table as pandas DataFrame
-------------------------------------------
:Sample Code:
.. code-block:: python

from simplesqlite import SimpleSQLite

con = SimpleSQLite("sample.sqlite", "w", profile=True)

con.create_table_from_data_matrix(
table_name="sample_table",
attr_name_list=["a", "b", "c", "d", "e"],
data_matrix=[
[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:
.. code-block:: none

$ 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


For more information
----------------------
More examples are available at
http://simplesqlite.rtfd.io/en/latest/pages/examples/index.html
Installation
============

::

pip install SimpleSQLite


Dependencies
============
Python 2.7+ or 3.3+

Mandatory
-----------------
- `DataPropery <https://github.com/thombashi/DataProperty>`__ (Used to extract data types)
- `logbook <http://logbook.readthedocs.io/en/stable/>`__
- `mbstrdecoder <https://github.com/thombashi/mbstrdecoder>`__
- `pathvalidate <https://github.com/thombashi/pathvalidate>`__
- `pytablereader <https://github.com/thombashi/pytablereader>`__
- `six <https://pypi.python.org/pypi/six/>`__
- `typepy <https://github.com/thombashi/typepy>`__

Test dependencies
-----------------
- `pytest <http://pytest.org/latest/>`__
- `pytest-runner <https://pypi.python.org/pypi/pytest-runner>`__
- `tox <https://testrun.org/tox/latest/>`__


Documentation
===============
http://simplesqlite.rtfd.io/

Related project
=================
- `sqlitebiter <https://github.com/thombashi/sqlitebiter>`__: CLI tool to convert CSV/Excel/HTML/JSON/LTSV/Markdown/TSV/Google-Sheets SQLite database by using SimpleSQLite

Keywords: SQLite,CSV,Google Sheets,JSON
Platform: UNKNOWN
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: Microsoft :: Windows
Classifier: Operating System :: POSIX
Classifier: Operating System :: POSIX :: Linux
Classifier: Programming Language :: Python :: 2
Classifier: Programming Language :: Python :: 2.7
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.3
Classifier: Programming Language :: Python :: 3.4
Classifier: Programming Language :: Python :: 3.5
Classifier: Programming Language :: Python :: 3.6
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
Classifier: Topic :: Software Development :: Libraries :: Python Modules

Project details


Release history Release notifications

History Node

0.28.1

History Node

0.28.0

History Node

0.27.1

History Node

0.27.0

History Node

0.26.0

History Node

0.25.2

History Node

0.25.1

History Node

0.25.0

History Node

0.24.0

History Node

0.23.0

History Node

0.22.0

History Node

0.21.2

History Node

0.21.1

History Node

0.21.0

History Node

0.20.4

History Node

0.20.3

History Node

0.20.2

History Node

0.20.1

History Node

0.20.0

History Node

0.19.0

History Node

0.18.1

History Node

0.18.0

History Node

0.17.0

History Node

0.16.0

History Node

0.15.0

History Node

0.14.1

This version
History Node

0.14.0

History Node

0.13.1

History Node

0.13.0

History Node

0.12.1

History Node

0.12.0

History Node

0.11.0

History Node

0.10.0

History Node

0.9.0

History Node

0.8.1

History Node

0.8.0

History Node

0.7.7

History Node

0.7.6

History Node

0.7.5

History Node

0.7.4

History Node

0.7.2

History Node

0.7.1

History Node

0.7.0

History Node

0.6.10

History Node

0.6.9

History Node

0.6.8

History Node

0.6.7

History Node

0.6.6

History Node

0.6.5

History Node

0.6.4

History Node

0.6.3

History Node

0.6.2

History Node

0.6.1

History Node

0.6.0

History Node

0.5.5

History Node

0.5.2

History Node

0.5.1

History Node

0.5.0

History Node

0.4.8

History Node

0.4.7

History Node

0.4.6

History Node

0.4.5

History Node

0.4.4

History Node

0.4.3

History Node

0.4.2

History Node

0.4.1

History Node

0.4.0

History Node

0.3.6

History Node

0.3.5

History Node

0.3.4

History Node

0.3.3

History Node

0.3.2

History Node

0.3.1

History Node

0.3.0

History Node

0.2.3

History Node

0.2.2

History Node

0.2.1

History Node

0.2.0

History Node

0.1.0

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
SimpleSQLite-0.14.0-py2.py3-none-any.whl (22.7 kB) Copy SHA256 hash SHA256 Wheel py2.py3 Aug 13, 2017
SimpleSQLite-0.14.0.tar.gz (42.9 kB) Copy SHA256 hash SHA256 Source None Aug 13, 2017

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging CloudAMQP CloudAMQP RabbitMQ AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page