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