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_skipping_rows_if_data_exist(self): mysession = Session() # write existing data writer = SQLTableWriter(mysession, [Pyexcel, self.data[0], None, None]) 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() writer = SQLTableWriter(mysession, [Pyexcel, update_data[0], None, row_updater]) 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 __init__(self, model, export_columns=None, **keywords): self.__model = model if export_columns: column_names = export_columns else: column_names = sorted([ field.attname for field in self.__model._meta.concrete_fields ]) QuerysetsReader.__init__(self, self.__model.objects.all(), column_names, **keywords)
def get_data(self): params = dict(row_renderer=self.__row_renderer, start_row=self.__start_row, row_limit=self.__row_limit) if self.__skip_row_func is not None: params['skip_row_func'] = self.__skip_row_func reader = QuerysetsReader(self.__query_sets, self.__column_names, **params) data = reader.to_array() return {self.__sheet_name: data}
def __init__(self, model, export_columns=None, **keywords): self.__model = model if export_columns: column_names = export_columns else: column_names = sorted( [field.attname for field in self.__model._meta.concrete_fields]) QuerysetsReader.__init__(self, self.__model.objects.all(), column_names, **keywords)
def test_one_table(self): mysession = Session() writer = SQLTableWriter(mysession, [Pyexcel, self.data[0], None, None]) 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 __init__(self, session, table, export_columns=None, **keywords): everything = session.query(table).all() column_names = None if export_columns: column_names = export_columns else: if len(everything) > 0: column_names = sorted([ column for column in everything[0].__dict__ if column != '_sa_instance_state']) QuerysetsReader.__init__(self, everything, column_names, **keywords)
def __init__(self, session, table, export_columns=None, **keywords): everything = session.query(table).all() column_names = None if export_columns: column_names = export_columns else: if len(everything) > 0: column_names = sorted([ column for column in everything[0].__dict__ if column != '_sa_instance_state' ]) QuerysetsReader.__init__(self, everything, column_names, **keywords)
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_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_foreign_key(self): all_posts = self.session.query(Post).all() column_names = ['category__name', 'title'] data = list(QuerysetsReader(all_posts, column_names).to_array()) eq_(json.dumps(data), '[["category__name", "title"], ["News", "Title A"],' + ' ["Sports", "Title B"]]')
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' } writer = SQLTableWriter( mysession, [Pyexcel, self.data[0], mapdict, None]) 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_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_update_existing_row(self): mysession = Session() # write existing data writer = SQLTableWriter(mysession, [Pyexcel, self.data[0], None, None]) 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 writer = SQLTableWriter(mysession, [Pyexcel, update_data[0], None, row_updater]) 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_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_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] ] writer = SQLTableWriter(mysession, [Pyexcel, data[0], None, None]) 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_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_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()