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