def class_level_data_setup(cls):
cls.engine.execute(User.__table__.delete())
cls.session.add_all([
User(id=1, name="Alice"),
User(id=2, name="Bob"),
User(id=3, name="Cathy"),
])
cls.session.commit()
def test_smart_insert(self):
"""
Test performance of smart insert.
**中文文档**
测试smart_insert的基本功能, 以及与普通的insert比较性能。
"""
scale = 6
n_exist = scale
n_all = scale**3
exist_id_list = [random.randint(1, n_all) for _ in range(n_exist)]
exist_id_list = list(set(exist_id_list))
exist_id_list.sort()
# user smart insert
self.eng.execute(User.__table__.delete())
exist_data = [User(id=id) for id in exist_id_list]
all_data = [User(id=id) for id in range(1, 1 + n_all)]
self.ses.add_all(exist_data)
self.ses.commit()
assert self.ses.query(User).count() == len(exist_data)
st = time.process_time()
op_counter = User.smart_insert(self.ses, all_data)
elapse1 = time.process_time() - st
assert self.ses.query(User).count() == n_all
assert op_counter <= (0.5 * n_all)
# user regular insert
self.eng.execute(User.__table__.delete())
exist_data = [User(id=id) for id in exist_id_list]
all_data = [User(id=id) for id in range(1, 1 + n_all)]
self.ses.add_all(exist_data)
self.ses.commit()
assert self.ses.query(User).count() == len(exist_data)
st = time.process_time()
for user in all_data:
try:
self.ses.add(user)
self.ses.commit()
except IntegrityError:
self.ses.rollback()
except FlushError:
self.ses.rollback()
elapse2 = time.process_time() - st
assert self.ses.query(User).count() == n_all
assert elapse1 < elapse2
def test_smart_update(self):
# single primary key column
User.smart_insert(self.eng, [User(id=1)])
assert User.by_pk(1, self.eng).name == None
# update
row_count = User.update_all(self.eng, [
User(id=1, name="Alice"),
User(id=2, name="Bob"),
])
assert row_count == 1
result = self.ses.query(User).all()
assert len(result) == 1 # User(Bob) not updated
result[0].name == "Alice"
# upsert
row_count = User.upsert_all(self.eng, [
User(id=1, name="Adam"),
User(id=2, name="Bob"),
])
assert row_count == 2
result = self.ses.query(User).all()
assert len(result) == 2
result[0].name == "Adam"
result[1].name == "Bob"
# multiple primary key columns
Association.smart_insert(self.ses, Association(x_id=1, y_id=1, flag=0))
assert Association.by_pk((1, 1), self.ses).flag == 0
# update
row_count = Association.update_all(self.ses, [
Association(x_id=1, y_id=1, flag=1),
Association(x_id=1, y_id=2, flag=2),
])
assert row_count == 1
result = self.ses.query(Association).all()
assert len(result) == 1 # User(Bob) not updated
result[0].flag == 1
# upsert
row_count = Association.upsert_all(self.eng, [
Association(x_id=1, y_id=1, flag=999),
Association(x_id=1, y_id=2, flag=2),
])
assert row_count == 2
result = self.ses.query(Association).all()
assert len(result) == 2
result[0].flag == 999
result[1].flag == 2
def test_by_pk(self):
user = User(id=1, name="Michael Jackson")
User.smart_insert(self.eng, user)
assert User.by_pk(1, self.eng).name == "Michael Jackson"
assert User.by_pk(100, self.ses) == None
asso = Association(x_id=1, y_id=2, flag=999)
Association.smart_insert(self.eng, asso)
assert Association.by_pk((1, 2), self.eng).flag == 999
assert Association.by_pk((1, 2), self.ses).flag == 999
assert Association.by_pk(dict(x_id=1, y_id=2), self.ses).flag == 999
def class_level_data_setup(cls):
cls.engine.execute(t_user.delete())
data = [{
"user_id": 1,
"name": "Alice"
}, {
"user_id": 2,
"name": "Bob"
}, {
"user_id": 3,
"name": "Cathy"
}]
cls.engine.execute(t_user.insert(), data)
cls.engine.execute(t_inv.delete())
data = [{
"store_id": 1,
"item_id": 1
}, {
"store_id": 1,
"item_id": 2
}, {
"store_id": 2,
"item_id": 1
}, {
"store_id": 2,
"item_id": 2
}]
cls.engine.execute(t_inv.insert(), data)
cls.engine.execute(User.__table__.delete())
cls.session.add_all([
User(id=1, name="Alice"),
User(id=2, name="Bob"),
User(id=3, name="Cathy"),
])
cls.session.commit()
cls.engine.execute(Association.__table__.delete())
cls.session.add_all([
Association(x_id=1, y_id=1),
Association(x_id=1, y_id=2),
Association(x_id=2, y_id=1),
Association(x_id=2, y_id=2),
])
cls.session.commit()
def test_to_OrderedDict(self):
assert User(id=1, name="Alice").to_OrderedDict(include_null=True) == \
OrderedDict([
("id", 1), ("name", "Alice"),
])
assert User(id=1).to_OrderedDict(include_null=True) == \
OrderedDict([
("id", 1), ("name", None),
])
assert User(id=1).to_OrderedDict(include_null=False) == \
OrderedDict([
("id", 1),
])
assert User(name="Alice").to_OrderedDict(include_null=True) == \
OrderedDict([
("id", None), ("name", "Alice"),
])
assert User(name="Alice").to_OrderedDict(include_null=False) == \
OrderedDict([
("name", "Alice"),
])
def test_smart_insert_single_object(self):
assert selecting.count_row(self.eng, User.__table__) == 0
user = User(id=1)
User.smart_insert(self.eng, user)
assert selecting.count_row(self.eng, User.__table__) == 1
user = User(id=1)
User.smart_insert(self.eng, user)
assert selecting.count_row(self.eng, User.__table__) == 1
def test_by_sql(self):
User.smart_insert(self.eng, [
User(id=1, name="Alice"),
User(id=2, name="Bob"),
User(id=3, name="Cathy"),
])
t_user = User.__table__
sql = select([t_user]).where(t_user.c.id >= 2)
assert len(User.by_sql(sql, self.eng)) == 2
sql = select([t_user]).where(t_user.c.id >= 2).limit(1)
assert len(User.by_sql(sql, self.eng)) == 1
sql = text("""
SELECT *
FROM extended_declarative_base_user AS t_user
WHERE t_user.id >= 2
""")
assert len(User.by_sql(sql, self.eng)) == 2
def test_primary_key_and_id_field(self):
assert User.pk_names() == ("id", )
assert tuple([field.name for field in User.pk_fields()]) == ("id", )
assert User.id_field_name() == "id"
assert User.id_field().name == "id"
assert User(id=1).pk_values() == (1, )
assert User(id=1).id_field_value() == 1
assert Association.pk_names() == ("x_id", "y_id")
assert tuple([field.name for field in Association.pk_fields()
]) == ("x_id", "y_id")
with raises(ValueError):
Association.id_field_name()
Association.id_field()
assert Association(x_id=1, y_id=2).pk_values() == (1, 2)
with raises(ValueError):
Association(x_id=1, y_id=2).id_field_value()
def test_values(self):
assert User(id=1, name="Alice").values() == [1, "Alice"]
assert User(id=1).values() == [1, None]
def test_absorb(self):
user1 = User(id=1)
user2 = User(name="Alice")
user3 = User(name="Bob")
user1.absorb(user2)
assert user1.to_dict() == {"id": 1, "name": "Alice"}
user1.absorb(user3)
assert user1.to_dict() == {"id": 1, "name": "Bob"}
with raises(TypeError):
user1.absorb({"name": "Alice"})
def test_glance(self):
user = User(id=1, name="Alice")
user.glance(_verbose=False)
def test_to_dict(self):
assert User(id=1, name="Alice").to_dict() == {"id": 1, "name": "Alice"}
assert User(id=1).to_dict() == {"id": 1, "name": None}
assert User(id=1).to_dict(include_null=False) == {"id": 1}
def test_repr(self):
user = User(id=1, name="Alice")
assert str(user) == "User(id=1, name='Alice')"
def test_items(self):
assert User(id=1, name="Alice").items() == [("id", 1),
("name", "Alice")]
assert User(id=1).items() == [("id", 1), ("name", None)]
def test_revise(self):
user = User(id=1)
user.revise({"name": "Alice"})
assert user.to_dict() == {"id": 1, "name": "Alice"}
user = User(id=1, name="Alice")
user.revise({"name": "Bob"})
assert user.to_dict() == {"id": 1, "name": "Bob"}
with raises(TypeError):
user.revise(User(name="Bob"))
def test_keys(self):
assert User.keys() == ["id", "name"]
assert User(id=1).keys() == ["id", "name"]