4.1. Create table

4.1.1. Create a table from a data matrix

create_table_from_data_matrix() method create a table in a SQLite database from data matrix. Data matrix required one of the types: dict/namedtuple()/list/tuple.

Sample Code:

Create a table in a SQLite database from data matrix

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

4.1.2. Create a table from CSV

create_table_from_csv() method create a table from a CSV file/text.

Sample Code:

Create a table in a SQLite database from CSV

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

4.1.3. Create table(s) from JSON

create_table_from_json() method can create a table from a JSON file/text.

Sample Code 1:

Create multiple tables in a SQLite database from multiple table data in a JSON file

from simplesqlite import SimpleSQLite

file_path = "sample_data_multi.json"

# create sample data file ---
with open(file_path, "w") as f:
    f.write("""{
        "table_a" : [
            {"attr_b": 4, "attr_c": "a", "attr_a": 1},
            {"attr_b": 2.1, "attr_c": "bb", "attr_a": 2},
            {"attr_b": 120.9, "attr_c": "ccc", "attr_a": 3}
        ],
        "table_b" : [
            {"a": 1, "b": 4},
            {"a": 2 },
            {"a": 3, "b": 120.9}
        ]
    }""")

# create table ---
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_json(file_path)

# output ---
for table_name in con.fetch_table_names():
    print("table: " + table_name)
    print(con.fetch_attr_names(table_name))
    result = con.select(select="*", table_name=table_name)
    for record in result.fetchall():
        print(record)
    print()

Output:

Output of the sample code 1

table: table_b
['a', 'b']
(1, '4')
(2, 'NULL')
(3, '120.9')

table: table_a
['attr_a', 'attr_b', 'attr_c']
(1, 4.0, 'a')
(2, 2.1, 'bb')
(3, 120.9, 'ccc')

Sample Code 2:

Create a table in a SQLite database from a table data in a JSON file

from simplesqlite import SimpleSQLite

file_path = "sample_data_single.json"

# create sample data file ---
with open(file_path, "w") as f:
    f.write("""[
        {"attr_b": 4, "attr_c": "a", "attr_a": 1},
        {"attr_b": 2.1, "attr_c": "bb", "attr_a": 2},
        {"attr_b": 120.9, "attr_c": "ccc", "attr_a": 3}
    ]""")

# create table ---
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_json(file_path)

# output ---
for table_name in con.fetch_table_names():
    print("table: " + table_name)
    print(con.fetch_attr_names(table_name))
    result = con.select(select="*", table_name=table_name)
    for record in result.fetchall():
        print(record)
    print()

Output:

Output of the sample code 2

table: sample_data_single
['attr_a', 'attr_b', 'attr_c']
(1, 4.0, 'a')
(2, 2.1, 'bb')
(3, 120.9, 'ccc')

4.1.4. Create a table from pandas DataFrame

create_table_from_dataframe() method can create a table from a pandas.DataFrame instance.

Sample Code:

Create a table in a SQLite database from pandas.DataFrame

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:

Output sqlite database file

$ sqlite3 pandas_df.sqlite
sqlite> .schema
CREATE TABLE 'pandas_df' (id INTEGER, value REAL, name TEXT);

4.1.5. Create table(s) from Excel

You can extract tabular data from an Excel file by pytablereader.ExcelTableFileLoader class defined in pytablereader module. And you can create a table from extracted data by using create_table_from_tabledata() method.

Sample Code:

Create a table in a SQLite database from an Excel file

import pytablereader
import simplesqlite
import xlsxwriter

file_path = "sample_data.xlsx"

# create sample data file ---
workbook = xlsxwriter.Workbook(file_path)

worksheet = workbook.add_worksheet("samplesheet1")
table = [
    ["", "", "", ""],
    ["", "a", "b", "c"],
    ["", 1, 1.1, "a"],
    ["", 2, 2.2, "bb"],
    ["", 3, 3.3, "cc"],
]
for row_idx, row in enumerate(table):
    for col_idx, item in enumerate(row):
        worksheet.write(row_idx, col_idx, item)

worksheet = workbook.add_worksheet("samplesheet2")

worksheet = workbook.add_worksheet("samplesheet3")
table = [
    ["", "", ""],
    ["", "", ""],
    ["aa", "ab", "ac"],
    [1, "hoge", "a"],
    [2, "", "bb"],
    [3, "foo", ""],
]
for row_idx, row in enumerate(table):
    for col_idx, item in enumerate(row):
        worksheet.write(row_idx, col_idx, item)

workbook.close()

# create table ---
con = simplesqlite.SimpleSQLite("sample.sqlite", "w")

loader = pytablereader.ExcelTableFileLoader(file_path)
for table_data in loader.load():
    con.create_table_from_tabledata(table_data)

# output ---
for table_name in con.fetch_table_names():
    print("table: " + table_name)
    print(con.fetch_attr_names(table_name))
    result = con.select(select="*", table_name=table_name)
    for record in result.fetchall():
        print(record)
    print()

Output:

table: samplesheet1
['a', 'b', 'c']
(1.0, 1.1, 'a')
(2.0, 2.2, 'bb')
(3.0, 3.3, 'cc')

table: samplesheet3
['aa', 'ab', 'ac']
(1.0, 'hoge', 'a')
(2.0, '', 'bb')
(3.0, 'foo', '')

4.1.6. Create table(s) from Google Sheets

GoogleSheetsTableLoader class and create_table_from_tabledata() method can create a table from Google Spreadsheet.

Required packages:

• oauth2client

• pyOpenSSL

See also

https://gspread.rtfd.io/en/latest/oauth2.html

Sample Code:

Create a table in a SQLite database from Google Sheets

import simplesqlite
import pytablereader as ptr


credentials_file = "sample-xxxxxxxxxxxx.json"

# create table ---
con = simplesqlite.SimpleSQLite("sample.sqlite", "w")

loader = ptr.GoogleSheetsTableLoader(credentials_file)
loader.title = "samplebook"

for table_data in loader.load():
    con.create_table_from_tabledata(table_data)

# output ---
for table_name in con.fetch_table_names():
    print("table: " + table_name)
    print(con.fetch_attr_names(table_name))
    result = con.select(select="*", table_name=table_name)
    for record in result.fetchall():
        print(record)
    print()