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