def test_one_table_using_mapdict_as_dict(self): mysession = Session() self.data = [ ["Birth Date", "Id", "Name", "Weight"], [datetime.date(2014, 11, 11), 0, "Adam", 11.25], [datetime.date(2014, 11, 12), 1, "Smith", 12.25], ] mapdict = { "Birth Date": "birth", "Id": "id", "Name": "name", "Weight": "weight", } importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] adapter.column_name_mapping_dict = mapdict writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader( query_sets, ["birth", "id", "name", "weight"] ).to_array() assert list(results) == self.results mysession.close()
def test_skipping_rows_if_data_exist(self): mysession = Session() # write existing data importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == self.results # update data using custom initializer update_data = [ ["birth", "id", "name", "weight"], [datetime.date(2014, 11, 11), 0, "Adam_E", 12.25], [datetime.date(2014, 11, 12), 1, "Smith_E", 11.25], ] def row_updater(row): an_instance = mysession.query(Pyexcel).get(row["id"]) if an_instance is not None: raise PyexcelSQLSkipRowException() importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = update_data[0] adapter.row_initializer = row_updater writer = SQLTableWriter(importer, adapter) writer.write_array(update_data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == self.results mysession.close()
def test_one_table_using_mapdict_as_dict(self): mysession = Session() self.data = [ ["Birth Date", "Id", "Name", "Weight"], [datetime.date(2014, 11, 11), 0, "Adam", 11.25], [datetime.date(2014, 11, 12), 1, "Smith", 12.25], ] mapdict = { "Birth Date": "birth", "Id": "id", "Name": "name", "Weight": "weight", } importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] adapter.column_name_mapping_dict = mapdict writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader( query_sets, ["birth", "id", "name", "weight"]).to_array() assert list(results) == self.results mysession.close()
def test_unknown_sheet(self): importer = SQLTableImporter(None) category_adapter = SQLTableImportAdapter(Category) category_adapter.column_names = [""] importer.append(category_adapter) writer = SQLBookWriter() writer.open_content(importer) to_store = OrderedDict() to_store.update({"you do not see me": [[]]}) writer.write(to_store)
def test_unknown_sheet(self): importer = SQLTableImporter(None) category_adapter = SQLTableImportAdapter(Category) category_adapter.column_names = [""] importer.append(category_adapter) writer = SQLBookWriter() writer.open_content(importer) to_store = OrderedDict() to_store.update({"you do not see me": [[]]}) writer.write(to_store)
def setUp(self): Base.metadata.drop_all(engine) Base.metadata.create_all(engine) self.session = Session() data = { "Category": [["id", "name"], [1, "News"], [2, "Sports"]], "Post": [ ["id", "title", "body", "pub_date", "category"], [ 1, "Title A", "formal", datetime.datetime(2015, 1, 20, 23, 28, 29), "News", ], [ 2, "Title B", "informal", datetime.datetime(2015, 1, 20, 23, 28, 30), "Sports", ], ], } def category_init_func(row): c = Category(row["name"]) c.id = row["id"] return c def post_init_func(row): c = (self.session.query(Category).filter_by( name=row["category"]).first()) p = Post(row["title"], row["body"], c, row["pub_date"]) return p importer = SQLTableImporter(self.session) category_adapter = SQLTableImportAdapter(Category) category_adapter.column_names = data["Category"][0] category_adapter.row_initializer = category_init_func importer.append(category_adapter) post_adapter = SQLTableImportAdapter(Post) post_adapter.column_names = data["Post"][0] post_adapter.row_initializer = post_init_func importer.append(post_adapter) writer = SQLBookWriter() writer.open_content(importer) to_store = OrderedDict() to_store.update({category_adapter.get_name(): data["Category"][1:]}) to_store.update({post_adapter.get_name(): data["Post"][1:]}) writer.write(to_store) writer.close()
def test_one_table(self): mysession = Session() importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() reader = QuerysetsReader(query_sets, self.data[0]) results = reader.to_array() assert list(results) == self.results mysession.close()
def test_one_table(self): mysession = Session() importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() reader = QuerysetsReader(query_sets, self.data[0]) results = reader.to_array() assert list(results) == self.results mysession.close()
def test_one_table_with_empty_rows(self): mysession = Session() data = [['birth', 'id', 'name', 'weight'], ['', '', ''], [datetime.date(2014, 11, 11), 0, 'Adam', 11.25], [datetime.date(2014, 11, 12), 1, 'Smith', 12.25]] importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, data[0]).to_array() assert list(results) == self.results mysession.close()
def test_one_table(self): if PY36: # skip the test # beause python 3.6 sqlite give segmentation fault return mysession = Session() importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter, auto_commit=False) writer.write_array(self.data[1:]) writer.close() mysession.close() mysession2 = Session() query_sets = mysession2.query(Pyexcel).all() eq_(len(query_sets), 0) mysession2.close()
def test_one_table(self): if PY36: # skip the test # beause python 3.6 sqlite give segmentation fault return mysession = Session() importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter, auto_commit=False) writer.write_array(self.data[1:]) writer.close() mysession.close() mysession2 = Session() query_sets = mysession2.query(Pyexcel).all() eq_(len(query_sets), 0) mysession2.close()
def test_skipping_rows_if_data_exist(self): mysession = Session() # write existing data importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == self.results # update data using custom initializer update_data = [ ["birth", "id", "name", "weight"], [datetime.date(2014, 11, 11), 0, "Adam_E", 12.25], [datetime.date(2014, 11, 12), 1, "Smith_E", 11.25], ] def row_updater(row): an_instance = mysession.query(Pyexcel).get(row["id"]) if an_instance is not None: raise PyexcelSQLSkipRowException() importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = update_data[0] adapter.row_initializer = row_updater writer = SQLTableWriter(importer, adapter) writer.write_array(update_data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == self.results mysession.close()
def setUp(self): Base.metadata.drop_all(engine) Base.metadata.create_all(engine) self.session = Session() data = { "Category": [["id", "name"], [1, "News"], [2, "Sports"]], "Post": [ ["id", "title", "body", "pub_date", "category"], [ 1, "Title A", "formal", datetime.datetime(2015, 1, 20, 23, 28, 29), "News", ], [ 2, "Title B", "informal", datetime.datetime(2015, 1, 20, 23, 28, 30), "Sports", ], ], } def category_init_func(row): c = Category(row["name"]) c.id = row["id"] return c def post_init_func(row): c = ( self.session.query(Category) .filter_by(name=row["category"]) .first() ) p = Post(row["title"], row["body"], c, row["pub_date"]) return p importer = SQLTableImporter(self.session) category_adapter = SQLTableImportAdapter(Category) category_adapter.column_names = data["Category"][0] category_adapter.row_initializer = category_init_func importer.append(category_adapter) post_adapter = SQLTableImportAdapter(Post) post_adapter.column_names = data["Post"][0] post_adapter.row_initializer = post_init_func importer.append(post_adapter) writer = SQLBookWriter() writer.open_content(importer) to_store = OrderedDict() to_store.update({category_adapter.get_name(): data["Category"][1:]}) to_store.update({post_adapter.get_name(): data["Post"][1:]}) writer.write(to_store) writer.close()
def test_one_table_using_mapdict_as_array(self): mysession = Session() self.data = [["Birth Date", "Id", "Name", "Weight"], [datetime.date(2014, 11, 11), 0, 'Adam', 11.25], [datetime.date(2014, 11, 12), 1, 'Smith', 12.25]] mapdict = ['birth', 'id', 'name', 'weight'] importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] adapter.column_name_mapping_dict = mapdict writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, mapdict).to_array() assert list(results) == self.results mysession.close()
def test_one_table_with_empty_rows(self): mysession = Session() data = [ ["birth", "id", "name", "weight"], ["", "", ""], [datetime.date(2014, 11, 11), 0, "Adam", 11.25], [datetime.date(2014, 11, 12), 1, "Smith", 12.25], ] importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, data[0]).to_array() assert list(results) == self.results mysession.close()
def test_update_existing_row(self): mysession = Session() # write existing data importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == self.results # update data using custom initializer update_data = [ ["birth", "id", "name", "weight"], [datetime.date(2014, 11, 11), 0, "Adam_E", 12.25], [datetime.date(2014, 11, 12), 1, "Smith_E", 11.25], ] updated_results = [ ["birth", "id", "name", "weight"], ["2014-11-11", 0, "Adam_E", 12.25], ["2014-11-12", 1, "Smith_E", 11.25], ] def row_updater(row): an_instance = mysession.query(Pyexcel).get(row["id"]) if an_instance is None: an_instance = Pyexcel() for name in row.keys(): setattr(an_instance, name, row[name]) return an_instance importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = update_data[0] adapter.row_initializer = row_updater writer = SQLTableWriter(importer, adapter) writer.write_array(update_data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == updated_results mysession.close()
def test_one_table_with_empty_string_in_unique_field(self): mysession = Session() data = [ ["birth", "id", "name", "weight"], [datetime.date(2014, 11, 11), 0, "", 11.25], [datetime.date(2014, 11, 12), 1, "", 12.25], ] importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, data[0]).to_array() assert list(results) == [ ["birth", "id", "name", "weight"], ["2014-11-11", 0, None, 11.25], ["2014-11-12", 1, None, 12.25], ] mysession.close()
def test_update_existing_row(self): mysession = Session() # write existing data importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = self.data[0] writer = SQLTableWriter(importer, adapter) writer.write_array(self.data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == self.results # update data using custom initializer update_data = [ ["birth", "id", "name", "weight"], [datetime.date(2014, 11, 11), 0, "Adam_E", 12.25], [datetime.date(2014, 11, 12), 1, "Smith_E", 11.25], ] updated_results = [ ["birth", "id", "name", "weight"], ["2014-11-11", 0, "Adam_E", 12.25], ["2014-11-12", 1, "Smith_E", 11.25], ] def row_updater(row): an_instance = mysession.query(Pyexcel).get(row["id"]) if an_instance is None: an_instance = Pyexcel() for name in row.keys(): setattr(an_instance, name, row[name]) return an_instance importer = SQLTableImporter(mysession) adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = update_data[0] adapter.row_initializer = row_updater writer = SQLTableWriter(importer, adapter) writer.write_array(update_data[1:]) writer.close() query_sets = mysession.query(Pyexcel).all() results = QuerysetsReader(query_sets, self.data[0]).to_array() assert list(results) == updated_results mysession.close()
def test_sql_table_import_adapter(): adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = ["a"] adapter.row_initializer = "abc" eq_(adapter.row_initializer, "abc")
def test_sql_table_import_adapter(): adapter = SQLTableImportAdapter(Pyexcel) adapter.column_names = ["a"] adapter.row_initializer = "abc" eq_(adapter.row_initializer, "abc")