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