Python SimpleSQLite.select方法代碼示例

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
    def test_normal_file(
        self,
        tmpdir,
        json_text,
        filename,
        table_name,
        expected_table_name,
        expected_attr_names,
        expected_data_matrix,
    ):
        p_db = tmpdir.join("tmp.db")
        p_json = tmpdir.join(filename)

        with open(str(p_json), "w") as f:
            f.write(json_text)

        con = SimpleSQLite(str(p_db), "w")
        con.create_table_from_json(str(p_json), table_name)

        assert con.fetch_table_names() == [expected_table_name]
        assert expected_attr_names == con.fetch_attr_names(expected_table_name)

        result = con.select(select="*", table_name=expected_table_name)
        result_matrix = result.fetchall()
        assert len(result_matrix) == 3
        assert result_matrix == expected_data_matrix

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
    def test_normal(self, tmpdir, value, expected):
        p_db = tmpdir.join("tmp.db")

        con = SimpleSQLite(str(p_db), "w")
        con.create_table_from_tabledata(value)

        assert con.fetch_table_names() == [value.table_name]
        assert con.fetch_attr_names(value.table_name) == value.headers

        result = con.select(select="*", table_name=value.table_name)
        result_matrix = result.fetchall()
        assert result_matrix == expected

        actual = con.select_as_tabledata(columns=value.headers, table_name=value.table_name)
        assert actual.equals(value)

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
    def test_normal_multi_file_same_table_same_structure(self):
        db_path = "test.sqlite"
        runner = CliRunner()

        with runner.isolated_filesystem():
            files = [valid_json_multi_file_2_1(), valid_json_multi_file_2_2()]

            result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
            print_traceback(result)
            assert result.exit_code == ExitCode.SUCCESS

            con = SimpleSQLite(db_path, "r")
            expected_tables = ["multij2", SourceInfo.get_table_name()]
            actual_tables = con.fetch_table_names()

            print_test_result(expected=expected_tables, actual=actual_tables)

            assert set(actual_tables) == set(expected_tables)

            expected_data_table = {
                "multij2": [
                    (1, 4.0, "a"),
                    (2, 2.1, "bb"),
                    (3, 120.9, "ccc"),
                    (1, 4.0, "a"),
                    (2, 2.1, "bb"),
                    (3, 120.9, "ccc"),
                ]
            }

            for table in con.fetch_table_names():
                if table == SourceInfo.get_table_name():
                    continue

                expected_data = expected_data_table.get(table)
                actual_data = con.select("*", table_name=table).fetchall()

                message = "table={}, expected={}, actual={}".format(
                    table, expected_data, actual_data
                )

                print("--- table: {} ---".format(table))
                print_test_result(expected=expected_data, actual=actual_data)

                assert expected_data == actual_data, message

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
    def test_normal_text(
        self,
        tmpdir,
        csv_text,
        table_name,
        attr_names,
        expected_table_name,
        expected_attr_names,
        expected_data_matrix,
    ):
        p_db = tmpdir.join("tmp.db")

        con = SimpleSQLite(str(p_db), "w")
        con.create_table_from_csv(csv_text, table_name, attr_names)

        assert con.fetch_table_names() == [expected_table_name]
        assert expected_attr_names == con.fetch_attr_names(expected_table_name)

        result = con.select(select="*", table_name=expected_table_name)
        result_matrix = result.fetchall()
        assert len(result_matrix) == 3
        assert result_matrix == expected_data_matrix

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
#!/usr/bin/env python
# encoding: utf-8


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

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
#!/usr/bin/env python
# encoding: utf-8

from __future__ import print_function

from simplesqlite import SimpleSQLite
from simplesqlite.query import Where


table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")

data_matrix = [[1, "aaa"], [2, "bbb"]]
con.create_table_from_data_matrix(table_name, ["key", "value"], data_matrix)

print("---- before update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
    print(record)
print()

con.update(table_name, set_query="value = 'ccc'", where=Where(key="key", value=1))

print("---- after update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
    print(record)

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
    def test_normal_append(self):
        db_path = "test.sqlite"
        runner = CliRunner()

        with runner.isolated_filesystem():
            files = [valid_json_multi_file_2_1()]
            table_name = "multij2"
            expected_tables = [table_name, SourceInfo.get_table_name()]

            # first execution without --append option (new) ---
            result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
            print_traceback(result)
            assert result.exit_code == ExitCode.SUCCESS

            con = SimpleSQLite(db_path, "r")

            actual_tables = con.fetch_table_names()

            print_test_result(expected=expected_tables, actual=actual_tables)

            assert set(actual_tables) == set(expected_tables)

            actual_data = con.select("*", table_name=table_name).fetchall()
            expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")]

            print_test_result(expected=expected_data, actual=actual_data)

            assert expected_data == actual_data

            # second execution with --append option ---
            result = runner.invoke(cmd, ["-o", db_path, "--append", "file"] + files)
            print_traceback(result)
            assert result.exit_code == ExitCode.SUCCESS

            con = SimpleSQLite(db_path, "r")

            actual_tables = con.fetch_table_names()

            print_test_result(expected=expected_tables, actual=actual_tables)

            assert set(actual_tables) == set(expected_tables)

            actual_data = con.select("*", table_name=table_name).fetchall()
            expected_data = [
                (1, 4.0, "a"),
                (2, 2.1, "bb"),
                (3, 120.9, "ccc"),
                (1, 4.0, "a"),
                (2, 2.1, "bb"),
                (3, 120.9, "ccc"),
            ]

            print_test_result(expected=expected_data, actual=actual_data)

            assert expected_data == actual_data

            # third execution without --append option (overwrite) ---
            result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
            print_traceback(result)
            assert result.exit_code == ExitCode.SUCCESS

            con = SimpleSQLite(db_path, "r")

            actual_tables = con.fetch_table_names()

            print_test_result(expected=expected_tables, actual=actual_tables)

            assert set(actual_tables) == set(expected_tables)

            actual_data = con.select("*", table_name=table_name).fetchall()
            expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")]

            print_test_result(expected=expected_data, actual=actual_data)

            assert expected_data == actual_data

# 需要導入模塊: from simplesqlite import SimpleSQLite [as 別名]
# 或者: from simplesqlite.SimpleSQLite import select [as 別名]
    def test_normal_multi_file_different_table(self):
        db_path = "test.sqlite"
        runner = CliRunner()

        with runner.isolated_filesystem():
            files = [
                valid_json_single_file(),
                invalid_json_single_file(),
                valid_json_multi_file_1(),
                valid_json_kv_file(),
                valid_csv_file_1_1(),
                valid_csv_file_2_1(),
                invalid_csv_file(),
                valid_tsv_file(),
                invalid_tsv_file(),
                valid_excel_file(),
                invalid_excel_file_1(),
                invalid_excel_file_2(),
                valid_html_file(),
                invalid_html_file(),
                valid_ltsv_file(),
                invalid_ltsv_file(),
                valid_markdown_file(),
                not_supported_format_file(),
            ]

            result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
            assert result.exit_code == ExitCode.SUCCESS

            con = SimpleSQLite(db_path, "r")
            expected_tables = [
                "singlejson",
                "multij1",
                "multij2",
                "valid_kv",
                "csv_a",
                "rename_insert",
                "excel_sheet_a",
                "excel_sheet_c",
                "excel_sheet_d",
                "valid_ltsv_a",
                "testtitle_tablename",
                "testtitle_html2",
                "tsv_a",
                "valid_mdtable_markdown1",
                SourceInfo.get_table_name(),
            ]
            actual_tables = con.fetch_table_names()

            print_test_result(expected=expected_tables, actual=actual_tables)

            assert set(actual_tables) == set(expected_tables)

            expected_data_table = {
                "singlejson": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
                "multij1": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
                "multij2": [(1, 4.0), (2, None), (3, 120.9)],
                "valid_kv": [("json_b", "hoge"), ("json_c", "bar")],
                "csv_a": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
                "rename_insert": [
                    (1, 55, "D Sam", 31, "Raven"),
                    (2, 36, "J Ifdgg", 30, "Raven"),
                    (3, 91, "K Wedfb", 28, "Raven"),
                ],
                "excel_sheet_a": [(1.0, 1.1, "a"), (2.0, 2.2, "bb"), (3.0, 3.3, "cc")],
                "excel_sheet_c": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")],
                "excel_sheet_d": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")],
                "testtitle_tablename": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")],
                "valid_ltsv_a": [
                    (1, 123.1, u'"ltsv0"', 1.0, u'"1"'),
                    (2, 2.2, u'"ltsv1"', 2.2, u'"2.2"'),
                    (3, 3.3, u'"ltsv2"', 3.0, u'"cccc"'),
                ],
                "testtitle_html2": [(1, 123.1), (2, 2.2), (3, 3.3)],
                "tsv_a": [(1, 4.0, "tsv0"), (2, 2.1, "tsv1"), (3, 120.9, "tsv2")],
                "valid_mdtable_markdown1": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")],
            }
            for table in con.fetch_table_names():
                if table == SourceInfo.get_table_name():
                    continue

                result = con.select("*", table_name=table)
                expected_data = expected_data_table.get(table)
                actual_data = result.fetchall()

                message = "table={}, expected={}, actual={}".format(
                    table, expected_data, actual_data
                )

                print("--- table: {} ---".format(table))
                print_test_result(expected=expected_data, actual=actual_data)

                assert sorted(expected_data) == sorted(actual_data), message