class Test_SimpleSQLite_create_table_from_tabledata(object):
@pytest.mark.parametrize(["value", "expected"], [
[
ptr.TableData("json1", ["attr_a", "attr_b", "attr_c"], [
{
'attr_a': 1,
'attr_b': 4,
'attr_c': 'a'
},
{
'attr_a': 2,
'attr_b': 2.1,
'attr_c': 'bb'
},
{
'attr_a': 3,
'attr_b': 120.9,
'attr_c': 'ccc'
},
]), [(1, 4.0, 'a'), (2, 2.1, 'bb'), (3, 120.9, 'ccc')]
],
[
ptr.TableData(
"multibyte_char", ["姓", "名", "生年月日", "郵便番号", "住所", "電話番号"], [
[
"山田", "太郎", "2001/1/1", "100-0002", "東京都千代田区皇居外苑",
"03-1234-5678"
],
[
"山田", "次郎", "2001/1/2", "251-0036", "神奈川県藤沢市江の島１丁目",
"03-9999-9999"
],
]),
[
("山田", "太郎", "2001/1/1", "100-0002", "東京都千代田区皇居外苑",
"03-1234-5678"),
("山田", "次郎", "2001/1/2", "251-0036", "神奈川県藤沢市江の島１丁目",
"03-9999-9999"),
]
],
])
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.get_table_name_list() == [value.table_name]
assert con.get_attr_name_list(value.table_name) == value.header_list
result = con.select(select="*", table_name=value.table_name)
result_matrix = result.fetchall()
assert result_matrix == expected
actual = con.select_as_tabledata(column_list=value.header_list,
table_name=value.table_name)
assert actual == value
def test_normal_json(self, tmpdir, file_path, format_name):
p_file_path = Path(str(tmpdir.join(file_path)))
p_file_path.parent.makedirs_p()
with open(p_file_path, "w") as f:
f.write('''[
{"attr_a": 1},
{"attr_b": 2.1, "attr_c": "bb"}
]''')
expeced_list = [
ptr.TableData("validdata_json1", ["attr_a", "attr_b", "attr_c"], [
{
'attr_a': 1
},
{
'attr_b': 2.1,
'attr_c': 'bb'
},
])
]
loader = ptr.TableFileLoader(p_file_path, format_name)
assert loader.format_name == "json"
for tabledata, expected in zip(loader.load(), expeced_list):
assert tabledata == expected
def test_normal_csv(self, tmpdir, file_path, format_name):
filename = pv.replace_symbol(file_path, "")
p_file_path = Path(
six.text_type(tmpdir.join(filename + Path(file_path).ext)))
p_file_path.parent.makedirs_p()
with open(p_file_path, "w") as f:
f.write('''"attr_a","attr_b","attr_c"
1,4,"a"
2,2.1,"bb"
3,120.9,"ccc"''')
expeced_list = [
ptr.TableData(filename, ["attr_a", "attr_b", "attr_c"], [
[1, 4, "a"],
[2, "2.1", "bb"],
[3, "120.9", "ccc"],
])
]
loader = ptr.TableFileLoader(p_file_path, format_name)
assert loader.format_name == "csv"
for tabledata, expected in zip(loader.load(), expeced_list):
print(ptw.dump_tabledata(expected))
print(ptw.dump_tabledata(tabledata))
assert tabledata == expected
def test_normal_json(self, url, format_name):
responses.add(responses.GET,
url,
body='''[
{"attr_a": 1},
{"attr_b": 2.1, "attr_c": "bb"}
]''',
content_type='text/plain; charset=utf-8',
status=200)
expeced_list = [
ptr.TableData("json1", ["attr_a", "attr_b", "attr_c"], [
{
'attr_a': 1
},
{
'attr_b': 2.1,
'attr_c': 'bb'
},
])
]
loader = ptr.TableUrlLoader(url, format_name)
assert loader.format_name == "json"
for tabledata, expected in zip(loader.load(), expeced_list):
assert tabledata == expected
def test_normal_csv(self, url, format_name):
responses.add(responses.GET,
url,
body='''"attr_a","attr_b","attr_c"
1,4,"a"
2,2.1,"bb"
3,120.9,"ccc"''',
content_type='text/plain; charset=utf-8',
status=200)
expeced_list = [
ptr.TableData("csv1", ["attr_a", "attr_b", "attr_c"], [
[1, 4, "a"],
[2, "2.1", "bb"],
[3, "120.9", "ccc"],
])
]
loader = ptr.TableUrlLoader(url, format_name)
assert loader.format_name == "csv"
for tabledata, expected in zip(loader.load(), expeced_list):
print("[expected]\n{}".format(ptw.dump_tabledata(expected)))
print("[actual]\n{}".format(ptw.dump_tabledata(tabledata)))
assert tabledata == expected
def create_table_from_data_matrix(self,
table_name,
attr_name_list,
data_matrix,
index_attr_list=None):
"""
Create a table if not exists. Moreover, insert data into the created
table.
:param str table_name: Table name to create.
:param list attr_name_list: List of attribute names of the table.
:param data_matrix: Data to be inserted into the table.
:type data_matrix: List of |dict|/|namedtuple|/|list|/|tuple|
:param tuple index_attr_list:
List of attribute names of creating indices.
:raises simplesqlite.InvalidTableNameError:
|raises_validate_table_name|
:raises simplesqlite.InvalidAttributeNameError:
|raises_validate_attr_name|
:raises ValueError: If the ``data_matrix`` is empty.
:Example:
:ref:`example-create-table-from-data-matrix`
.. seealso::
:py:meth:`.create_table`
:py:meth:`.insert_many`
:py:meth:`.create_index_list`
"""
self.__create_table_from_tabledata(
ptr.TableData(table_name, attr_name_list, data_matrix),
index_attr_list)
def select_as_tabledata(self,
table_name,
column_list=None,
where=None,
extra=None):
"""
SELECT data in the database and return data as a
:py:class:`pytablereader.TableData`.
:param str table_name: Table name to extract data.
:param list column_list:
Column name list to get data. If the value is |None|,
get data from all of the columns in the table.
:return: Table data as a :py:class:`pytablereader.TableData` instance.
:rtype: pytablereader.TableData
:raises simplesqlite.NullDatabaseConnectionError:
|raises_check_connection|
:raises simplesqlite.TableNotFoundError:
|raises_verify_table_existence|
:raises simplesqlite.OperationalError: |raises_operational_error|
.. note::
``pandas`` package required to execute this method.
"""
if column_list is None:
column_list = self.get_attr_name_list(table_name)
result = self.select(select=",".join(
SqlQuery.to_attr_str_list(column_list)),
table_name=table_name,
where=where,
extra=extra)
if result is None:
return ptr.TableData(table_name=None,
header_list=[],
record_list=[])
return ptr.TableData(table_name=table_name,
header_list=column_list,
record_list=result.fetchall())
def test_normal_excel(self):
url = 'https://github.com/thombashi/valid/test/data/validdata.xlsx'
data_path = os.path.join(os.path.dirname(__file__),
"data/validdata.xlsx")
with open(data_path, "rb") as f:
responses.add(responses.GET,
url,
body=f.read(),
content_type='application/octet-stream',
status=200)
expeced_list = [
ptr.TableData(table_name='testsheet1',
header_list=['a1', 'b1', 'c1'],
record_list=[
['aa1', 'ab1', 'ac1'],
[1.0, 1.1, 'a'],
[2.0, 2.2, 'bb'],
[3.0, 3.3, 'cc'],
]),
ptr.TableData(table_name='testsheet3',
header_list=['a3', 'b3', 'c3'],
record_list=[
['aa3', 'ab3', 'ac3'],
[4.0, 1.1, 'a'],
[5.0, '', 'bb'],
[6.0, 3.3, ''],
]),
]
loader = ptr.TableUrlLoader(url)
assert loader.format_name == "excel"
for tabledata, expected in zip(loader.load(), expeced_list):
assert tabledata == expected
def test_normal_excel(self, tmpdir):
file_path = '/tmp/valid/test/data/validdata.xlsx'
p_file_path = Path(str(tmpdir.join(file_path)))
p_file_path.parent.makedirs_p()
tabledata_list = [
ptr.TableData(table_name='testsheet1',
header_list=['a1', 'b1', 'c1'],
record_list=[
['aa1', 'ab1', 'ac1'],
[1.0, 1.1, 'a'],
[2.0, 2.2, 'bb'],
[3.0, 3.3, 'cc'],
]),
ptr.TableData(table_name='testsheet3',
header_list=['a3', 'b3', 'c3'],
record_list=[
['aa3', 'ab3', 'ac3'],
[4.0, 1.1, 'a'],
[5.0, '', 'bb'],
[6.0, 3.3, ''],
]),
]
writer = ptw.ExcelXlsxTableWriter()
writer.open_workbook(p_file_path)
for tabledata in tabledata_list:
writer.from_tabledata(tabledata)
writer.write_table()
writer.close()
loader = ptr.TableFileLoader(p_file_path)
assert loader.format_name == "excel"
for tabledata in loader.load():
assert tabledata in tabledata_list
def database_path(tmpdir):
p = tmpdir.join("tmp.db")
db_path = str(p)
con = simplesqlite.SimpleSQLite(db_path, "w")
con.create_table_from_tabledata(ptr.TableData(
"testdb0",
["attr_a", "attr_b"],
[
[1, 2],
[3, 4],
]),
index_attr_list=["attr_a"]
)
con.create_table_from_tabledata(ptr.TableData(
"testdb1",
["foo", "bar", "hoge"],
[
[1, 2.2, "aa"],
[3, 4.4, "bb"],
]),
index_attr_list=("foo", "hoge")
)
con.create_table(
"constraints",
[
"primarykey_id INTEGER PRIMARY KEY",
"notnull_value REAL NOT NULL",
"unique_value INTEGER UNIQUE",
]
)
return db_path
def test_smoke_database_connection(self, database_path, verbosity_level,
output_format, expected_v):
con = simplesqlite.SimpleSQLite(database_path, "a")
extractor = ss.SqliteSchemaExtractor(con, verbosity_level,
output_format)
assert len(extractor.dumps()) > 10
assert extractor.verbosity_level == expected_v
assert extractor.get_database_schema() is not None
for table_name in extractor.get_table_name_list():
extractor.get_table_schema_text(table_name)
con.create_table_from_tabledata(
ptr.TableData("newtable", ["foo", "bar", "hoge"], [
[1, 2.2, "aa"],
[3, 4.4, "bb"],
]))
extractor.get_table_schema_text("newtable")
class Test_SimpleSQLite_select_as_dict(object):
@pytest.mark.parametrize(["value", "expected"], [
[
ptr.TableData("json1", ["attr_a", "attr_b", "attr_c"], [
{
'attr_a': 1,
'attr_b': 4,
'attr_c': 'a'
},
{
'attr_a': 2,
'attr_b': 2.1,
'attr_c': 'bb'
},
{
'attr_a': 3,
'attr_b': 120.9,
'attr_c': 'ccc'
},
]),
{
'json1': [
OrderedDict([('attr_a', 1), ('attr_b', 4),
('attr_c', 'a')]),
OrderedDict([('attr_a', 2), ('attr_b', 2.1),
('attr_c', 'bb')]),
OrderedDict([('attr_a', 3), ('attr_b', 120.9),
('attr_c', 'ccc')]),
],
},
],
])
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.select_as_dict(table_name=value.table_name) == expected
class Test_ExcelTableFileLoader_load:
def setup_method(self, method):
TableLoader.clear_table_count()
@pytest.mark.parametrize([
"table_name",
"start_row",
"expected_tabledata",
], [
[
"%(sheet)s", 0,
[
ptr.TableData(table_name="boolsheet",
header_list=["true", "false", "tf", "lost"],
record_list=[
[True, False, True, True],
[True, False, False, ""],
[True, False, False, False],
]),
ptr.TableData(table_name="testsheet1",
header_list=["a1", "b1", "c1"],
record_list=[
["aa1", "ab1", "ac1"],
[1.0, 1.1, "a"],
[2.0, 2.2, "bb"],
[3.0, 3.3, "cc"],
]),
ptr.TableData(table_name="testsheet3",
header_list=["a3", "b3", "c3"],
record_list=[
["aa3", "ab3", "ac3"],
[4.0, 1.1, "a"],
[5.0, "", "bb"],
[6.0, 3.3, ""],
]),
]
],
[
"%(filename)s_%(sheet)s", 2,
[
ptr.TableData(table_name="tmp_boolsheet",
header_list=["TRUE", "FALSE", "False", "False"],
record_list=[]),
ptr.TableData(table_name="tmp_testsheet1",
header_list=["aa1", "ab1", "ac1"],
record_list=[
[1.0, 1.1, "a"],
[2.0, 2.2, "bb"],
[3.0, 3.3, "cc"],
]),
ptr.TableData(table_name="tmp_testsheet3",
header_list=["a3", "b3", "c3"],
record_list=[
["aa3", "ab3", "ac3"],
[4.0, 1.1, "a"],
[5.0, "", "bb"],
[6.0, 3.3, ""],
]),
]
],
])
def test_normal(self, valid_excel_file_path, table_name, start_row,
expected_tabledata):
loader = ptr.ExcelTableFileLoader(valid_excel_file_path)
loader.table_name = table_name
loader.start_row = start_row
for tabledata, expected in zip(loader.load(), expected_tabledata):
assert tabledata == expected
@pytest.mark.parametrize([
"table_name",
"start_row",
"expected",
], [
[
"%(sheet)s",
0,
ptr.InvalidDataError,
],
])
def test_abnormal(self, invalid_excel_file_path, table_name, start_row,
expected):
loader = ptr.ExcelTableFileLoader(invalid_excel_file_path)
loader.table_name = table_name
loader.start_row = start_row
for tabletuple in loader.load():
assert tabletuple == []
@pytest.mark.parametrize([
"source",
"expected",
], [
["", ptr.InvalidFilePathError],
[None, ptr.InvalidFilePathError],
])
def test_null_file_path(self, source, expected):
loader = ptr.ExcelTableFileLoader(source)
with pytest.raises(expected):
for _tabletuple in loader.load():
pass
@pytest.mark.parametrize([
"table_name",
"expected",
], [
["", ValueError],
[None, ValueError],
])
def test_null_table_name(self, valid_excel_file_path, table_name,
expected):
loader = ptr.ExcelTableFileLoader(valid_excel_file_path)
loader.table_name = table_name
with pytest.raises(expected):
for _tabletuple in loader.load():
pass
