def convert_nb(logger, source_info: "SourceInfo", con: SimpleSQLite,
result_logger: ResultLogger, nb) -> Set[str]:
changed_table_name_set = set() # type: Set[str]
changed_table_name_set |= CellConverter(logger, source_info, con,
result_logger, nb.cells).convert()
changed_table_name_set |= MetaDataConverter(logger, source_info, con,
result_logger,
nb.metadata).convert()
table_name = KEY_VALUE_TABLE
need_create_table = not con.has_table(table_name)
kv_records = [[source_info.source_id, key,
nb.get(key)] for key in ("nbformat", "nbformat_minor")]
if len(kv_records) > 0:
con.create_table(
table_name,
[NbAttrDesc.SOURECE_ID, NbAttrDesc.KEY, NbAttrDesc.VALUE])
con.insert_many(table_name, kv_records)
result_logger.logging_success(
"{}: {}".format(source_info.base_name, table_name), table_name,
need_create_table)
changed_table_name_set.add(table_name)
con.commit()
return changed_table_name_set
def test_smoke(self, tmpdir, filename):
p = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p), "w")
test_data_file_path = os.path.join(
os.path.dirname(__file__), "data", filename)
loader = ptr.TableFileLoader(test_data_file_path)
success_count = 0
for tabledata in loader.load():
if tabledata.is_empty():
continue
print(ptw.dump_tabledata(tabledata))
try:
con.create_table_from_tabledata(
ptr.SQLiteTableDataSanitizer(tabledata).sanitize())
success_count += 1
except ValueError as e:
print(e)
con.commit()
assert success_count > 0
def con_profile(tmpdir):
p = tmpdir.join("tmp_profile.db")
con = SimpleSQLite(str(p), "w", profile=True)
con.create_table_from_data_matrix(TEST_TABLE_NAME, ["attr_a", "attr_b"], [[1, 2], [3, 4]])
con.commit()
return con
def con_profile(tmpdir):
p = tmpdir.join("tmp_profile.db")
con = SimpleSQLite(str(p), "w", profile=True)
con.create_table_from_data_matrix(TEST_TABLE_NAME, ["attr_a", "attr_b"],
[[1, 2], [3, 4]])
con.commit()
return con
def con(tmpdir):
p = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p), "w")
con.create_table_from_data_matrix(TEST_TABLE_NAME, ["attr_a", "attr_b"],
[[1, 2], [3, 4]])
con.execute_query(
f"CREATE VIEW view1 AS SELECT attr_a, attr_b FROM {TEST_TABLE_NAME}")
con.commit()
return con
def test_normal(self, tmpdir, attr_name_list, data_matrix, index_attr_list,
expected_attr):
p = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p), "w")
table_name = TEST_TABLE_NAME
con.create_table_from_data_matrix(table_name, attr_name_list,
data_matrix, index_attr_list)
con.commit()
# check data ---
result = con.select(select=",".join(
SqlQuery.to_attr_str_list(attr_name_list)),
table_name=table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
print("expected: {}".format(expected_attr))
print("actual: {}".format(con.get_attr_type(table_name)))
assert con.get_attr_type(table_name) == expected_attr
def test_smoke(self, tmpdir, filename):
try:
import pytablereader as ptr
except ImportError:
pytest.skip("requires pytablereader")
p = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p), "w")
test_data_file_path = os.path.join(os.path.dirname(__file__), "data",
filename)
loader = ptr.TableFileLoader(test_data_file_path)
success_count = 0
for table_data in loader.load():
if table_data.is_empty():
continue
try:
from pytablewriter import dumps_tabledata
print(dumps_tabledata(table_data))
except ImportError:
pass
try:
con.create_table_from_tabledata(
SQLiteTableDataSanitizer(table_data).normalize())
success_count += 1
except ValueError as e:
print(e)
con.commit()
assert success_count > 0
# SimpleSQLite
# pip install simplesqlite
from simplesqlite import SimpleSQLite
import pandas as pd
import sys
import sqlite3
# read annotation file
print('reading annotation file...')
anno_df = pd.read_csv(sys.argv[1], sep='\t', index_col=0, header=0)
new_list = ['GeneID'] + list(anno_df.columns)
anno_df = anno_df.reindex(columns=new_list)
anno_df.GeneID = anno_df.index
print('connect gff sqlite3 database...')
con = SimpleSQLite(sys.argv[2])
print('create new table to gff sqlite3 database...')
con.create_table_from_dataframe(anno_df, table_name='anno')
print('create index to sqlite3 database...')
con.create_index_list(
'anno', ['GeneID', 'Nr', 'Swissprot', 'KEGG', 'TrEMBL', 'Interpro'])
con.commit()
con.close()
