4.4. Get Data from a Table

4.4.1. Get Data from a table as OrderedDict

select_as_dict() method can get data from a table in a SQLite database as a collections.OrderedDict list.

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"],
    ])

for record in con.select_as_dict(table_name="sample_table"):
    print(record)

Output:

OrderedDict([('a', 1), ('b', 1.1), ('c', 'aaa'), ('d', 1), ('e', 1)])
OrderedDict([('a', 2), ('b', 2.2), ('c', 'bbb'), ('d', 2.2), ('e', 2.2)])
OrderedDict([('a', 3), ('b', 3.3), ('c', 'ccc'), ('d', 3), ('e', 'ccc')])

4.4.2. Get Data from a table as pandas DataFrame

select_as_dataframe() method can get data from a table in a SQLite database as a pandas.Dataframe instance.

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