def test_batch(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'bulk_update_1'
}, {
'id': 5,
'name': 'bulk_update_5'
}, {
'id': 11,
'name': 'bulk_update_11'
}], batch_size=1)
self.assertEqual(3, res)
# 9 from fixture + 1 created
self.assertEqual(10, TestModel.objects.all().count())
for pk, name, int_field in TestModel.objects.all().order_by('id').values_list('id', 'name', 'int_field'):
if pk in {1, 5, 11}:
self.assertEqual('bulk_update_%d' % pk, name)
else:
self.assertEqual('test%d' % pk, name)
if pk == 11:
self.assertIsNone(int_field)
else:
self.assertEqual(pk, int_field)
# Test for empty values correct
res = bulk_update_or_create(TestModel, [], batch_size=10)
self.assertEqual(0, res)
def test_batch(self):
with self.assertRaises(TypeError):
bulk_update_or_create(TestModel, [{'id': 1, 'name': 'test1'}], batch_size='abc')
with self.assertRaises(ValueError):
bulk_update_or_create(TestModel, [{'id': 1, 'name': 'test1'}], batch_size=-2)
with self.assertRaises(TypeError):
bulk_update_or_create(TestModel, [{'id': 1, 'name': 'test1'}], batch_size=2.5)
with self.assertRaises(TypeError):
bulk_update_or_create(TestModel, [{'id': 1, 'name': 'test1'}], batch_size=1, batch_delay='abc')
with self.assertRaises(ValueError):
bulk_update_or_create(TestModel, [{'id': 1, 'name': 'test1'}], batch_size=1, batch_delay=-2)
def test_auto_now(self):
res = bulk_update_or_create(
AutoNowModel,
[{
'id': 1,
'checked': None
}, {
'id': 11,
'checked': datetime(2020, 1, 2, 0, 0, 0, tzinfo=tz_utc)
}])
self.assertEqual(2, res)
# 1 from fixture + 1 created
self.assertEqual(2, AutoNowModel.objects.all().count())
for instance in AutoNowModel.objects.all():
# On PostgreSQL 9.4 native bulk_create is used. It contains a known issue:
# it does not respect django TIME_ZONE setting. See https://code.djangoproject.com/ticket/32320#ticket.
# Though, updated field uses NOW() PostgreSQL function, which sets correct date
if instance.pk <= 10:
self.assertEqual(instance.updated, now().date())
self.assertEqual(datetime(2019, 1, 1, 0, 0, 0, tzinfo=tz_utc),
instance.created)
else:
self.assertEqual(instance.updated, get_auto_now_date())
self.assertGreaterEqual(instance.created,
now() - timedelta(seconds=1))
self.assertLessEqual(instance.created,
now() + timedelta(seconds=1))
def test_django_expression(self):
from django.db.models import F
from django.db.models.functions import Upper
res = bulk_update_or_create(TestModel, [{
'id': 1,
'int_field': 1
}, {
'id': 5,
'int_field': 5
}, {
'id': 11,
'int_field': 11
}],
set_functions={
'int_field':
F('int_field') + BulkValue(),
'name': Upper('name')
})
self.assertEqual(3, res)
for pk, name, int_field in TestModel.objects.all().order_by(
'id').values_list('id', 'name', 'int_field'):
if pk in {1, 5}:
self.assertEqual(pk * 2, int_field)
self.assertEqual('TEST%d' % pk, name)
elif pk > 10:
self.assertEqual(pk, int_field)
self.assertEqual('', name)
else:
self.assertEqual(pk, int_field)
self.assertEqual('test%d' % pk, name)
def test_array(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'array_field': [1]
}, {
'id': 2,
'array_field': [2]
}, {
'id': 11,
'array_field': [11]
}, {
'id': 4,
'array_field': []
}])
self.assertEqual(4, res)
for pk, name, array_field in TestModel.objects.all().order_by(
'id').values_list('id', 'name', 'array_field'):
if pk in {1, 2, 11}:
self.assertListEqual([pk], array_field)
elif pk == 4:
self.assertListEqual([], array_field)
else:
self.assertIsNone(array_field)
if pk != 11:
self.assertEqual('test%d' % pk, name)
else:
self.assertEqual('', name)
def test_concat_str(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'bulk_update_1'
}, {
'id': 5,
'name': 'bulk_update_5'
}, {
'id': 11,
'name': 'bulk_update_11'
}],
set_functions={'name': '||'})
self.assertEqual(3, res)
for pk, name, int_field in TestModel.objects.all().order_by(
'id').values_list('id', 'name', 'int_field'):
if pk in {1, 5}:
self.assertEqual('test%dbulk_update_%d' % (pk, pk), name)
elif pk == 11:
self.assertEqual('bulk_update_%d' % pk, name)
else:
self.assertEqual('test%d' % pk, name)
if pk != 11:
self.assertEqual(pk, int_field)
else:
self.assertIsNone(int_field)
def test_example(self):
# Skip bulk_create and bulk_update section (tested in other test), and init data as bulk_update_or_create start
TestModel.objects.bulk_create([
TestModel(pk=1, name="updated1", int_field=2),
TestModel(pk=2, name="updated2", int_field=3),
TestModel(pk=3, name="incr", int_field=4),
])
res = bulk_update_or_create(TestModel, [{
"id": 3,
"name": "_concat1",
"int_field": 4
}, {
"id": 4,
"name": "concat2",
"int_field": 5
}], set_functions={'name': '||'})
self.assertEqual(2, res)
self.assertListEqual([
{"id": 1, "name": "updated1", "int_field": 2},
{"id": 2, "name": "updated2", "int_field": 3},
{"id": 3, "name": "incr_concat1", "int_field": 4},
{"id": 4, "name": "concat2", "int_field": 5},
], list(TestModel.objects.all().order_by("id").values("id", "name", "int_field")))
def test_key_update(self):
res = bulk_update_or_create(TestModel, {
(1, 'test1'): {
'id': 1,
'name': 'bulk_update_1'
},
(5, 'test5'): {
'id': 5,
'name': 'bulk_update_5'
},
(11, 'test11'): {
'id': 11,
'name': 'bulk_update_11'
}
}, key_fields=('id', 'name'))
self.assertEqual(3, res)
for pk, name, int_field in TestModel.objects.all().order_by('id').values_list('id', 'name', 'int_field'):
if pk in {1, 5}:
# Note due to insert on conflict restrictions key fields will be prior to update ones on insert.
self.assertEqual('bulk_update_%d' % pk, name)
else:
self.assertEqual('test%d' % pk, name)
if pk != 11:
self.assertEqual(pk, int_field)
else:
self.assertIsNone(int_field)
def test_concat_jsonb(self):
for i in range(1, 5):
res = bulk_update_or_create(TestModel, [{'id': 1, 'json_field': {i: 1}},
{'id': 2, 'json_field': {i: 2}},
{'id': 11, 'json_field': {i: 11}},
{'id': 4, 'json_field': {}}], set_functions={'json_field': '||'})
self._test_concat_dict(i, res, 'json_field')
def test_using(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'bulk_update_1'
}, {
'id': 5,
'name': 'bulk_update_5'
}, {
'id': 11,
'name': 'bulk_update_11'
}], using='secondary')
self.assertEqual(3, res)
# 9 from fixture + 1 created
self.assertEqual(10, TestModel.objects.all().using('secondary').count())
self.assertEqual(9, TestModel.objects.all().using('default').count())
for pk, name, int_field in TestModel.objects.all().using('secondary').order_by('id').\
values_list('id', 'name', 'int_field'):
if pk in {1, 5, 11}:
self.assertEqual('bulk_update_%d' % pk, name)
else:
self.assertEqual('test%d' % pk, name)
if pk == 11:
self.assertIsNone(int_field)
else:
self.assertEqual(pk, int_field)
for pk, name, int_field in TestModel.objects.all().order_by('id').values_list('id', 'name', 'int_field'):
self.assertEqual('test%d' % pk, name)
self.assertEqual(pk, int_field)
def test_eq_not_null(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'bulk_update_1'
}, {
'id': 5,
'name': None
}, {
'id': 11,
'name': None
}], set_functions={'name': 'eq_not_null'})
self.assertEqual(3, res)
# 9 from fixture + 1 created
self.assertEqual(10, TestModel.objects.all().count())
for pk, name, int_field in TestModel.objects.all().order_by('id').values_list('id', 'name', 'int_field'):
if pk in {1}:
self.assertEqual('bulk_update_%d' % pk, name)
elif pk == 11:
# Default name, not None, look https://github.com/M1hacka/django-pg-bulk-update/issues/2
self.assertEqual('', name)
else:
self.assertEqual('test%d' % pk, name)
if pk == 11:
self.assertIsNone(int_field)
else:
self.assertEqual(pk, int_field)
def test_auto_now_given_directly(self):
res = bulk_update_or_create(
AutoNowModel,
[{
'id': 1,
'checked': None,
'created': now(),
'updated': now().date()
}, {
'id': 11,
'checked': datetime(2020, 1, 2, 0, 0, 0, tzinfo=tz_utc),
'created': now(),
'updated': now().date()
}])
self.assertEqual(2, res)
# 1 from fixture + 1 created
self.assertEqual(2, AutoNowModel.objects.all().count())
for instance in AutoNowModel.objects.all():
if instance.pk <= 10:
self.assertEqual(instance.updated, now().date())
self.assertEqual(datetime(2019, 1, 1, 0, 0, 0, tzinfo=tz_utc),
instance.created)
else:
self.assertEqual(instance.updated, get_auto_now_date())
self.assertGreaterEqual(instance.created,
now() - timedelta(seconds=1))
self.assertLessEqual(instance.created,
now() + timedelta(seconds=1))
def test_auto_now(self):
res = bulk_update_or_create(
AutoNowModel,
[{
'id': 1,
'checked': None
}, {
'id': 11,
'checked': datetime(2020, 1, 2, 0, 0, 0, tzinfo=pytz.utc)
}])
self.assertEqual(2, res)
# 1 from fixture + 1 created
self.assertEqual(2, AutoNowModel.objects.all().count())
for instance in AutoNowModel.objects.all():
self.assertEqual(instance.updated, datetime.now(pytz.utc).date())
if instance.pk <= 10:
print(instance.pk)
self.assertEqual(
datetime(2019, 1, 1, 0, 0, 0, tzinfo=pytz.utc),
instance.created)
else:
self.assertGreaterEqual(instance.created,
now() - timedelta(seconds=1))
self.assertLessEqual(instance.created,
now() + timedelta(seconds=1))
def test_returning_not_unique(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'bulk_update_1'
}, {
'id': 5,
'name': 'bulk_update_5'
}, {
'id': 11,
'name': 'bulk_update_11'
}], returning=('id', 'name', 'int_field'), key_is_unique=False)
from django_pg_returning import ReturningQuerySet
self.assertIsInstance(res, ReturningQuerySet)
self.assertSetEqual({
(1, 'bulk_update_1', 1),
(5, 'bulk_update_5', 5),
(11, 'bulk_update_11', None),
}, set(res.values_list('id', 'name', 'int_field')))
# 9 from fixture + 1 created
self.assertEqual(10, TestModel.objects.all().count())
for pk, name, int_field in TestModel.objects.all().order_by('id').values_list('id', 'name', 'int_field'):
if pk in {1, 5, 11}:
self.assertEqual('bulk_update_%d' % pk, name)
else:
self.assertEqual('test%d' % pk, name)
if pk == 11:
self.assertIsNone(int_field)
else:
self.assertEqual(pk, int_field)
def test_returning_not_unique_empty(self):
res = bulk_update_or_create(TestModel, [],
returning='id',
key_is_unique=False)
from django_pg_returning import ReturningQuerySet
self.assertIsInstance(res, ReturningQuerySet)
self.assertEqual(0, res.count())
def test_empty(self):
res = bulk_update_or_create(TestModel, [])
self.assertEqual(0, res)
for pk, name, int_field in TestModel.objects.all().order_by(
'id').values_list('id', 'name', 'int_field'):
self.assertEqual('test%d' % pk, name)
self.assertEqual(pk, int_field)
def test_union_array(self):
for i in range(1, 5):
res = bulk_update_or_create(TestModel, [{'id': 1, 'array_field': [1]},
{'id': 2, 'array_field': [i]},
{'id': 11, 'array_field': [i]},
{'id': 4, 'array_field': []}], set_functions={'array_field': 'union'})
self._test_union_array(i, res)
def test_concat_hstore(self):
for i in range(1, 5):
res = bulk_update_or_create(TestModel, [{'id': 1, 'hstore_field': {i: 1}},
{'id': 2, 'hstore_field': {i: 2}},
{'id': 11, 'hstore_field': {i: 11}},
{'id': 4, 'hstore_field': {}}],
set_functions={'hstore_field': '||'})
self._test_concat_dict(i, res, 'hstore_field', val_as_str=True)
def test_concat_empty(self):
res = bulk_update_or_create(TestModel, [{
'id': 11,
'big_array_field': [2147483649]
}],
set_functions={'big_array_field': '||'})
self.assertEqual(1, res)
self.assertListEqual([2147483649],
TestModel.objects.get(pk=11).big_array_field)
def test_auto_now_respects_override(self):
with self.subTest('Create'):
bulk_update_or_create(
AutoNowModel,
[{
'id': 1,
'created': datetime(2011, 1, 2, 0, 0, 0, tzinfo=tz_utc),
'updated': date(2011, 1, 3),
'checked': datetime(2011, 1, 4, 0, 0, 0, tzinfo=tz_utc),
}],
set_functions={
"created": "eq",
"updated": "eq",
"checked": "eq"
})
instance = AutoNowModel.objects.get()
self.assertEqual(datetime(2011, 1, 2, 0, 0, 0, tzinfo=tz_utc),
instance.created)
self.assertEqual(date(2011, 1, 3), instance.updated)
self.assertEqual(datetime(2011, 1, 4, 0, 0, 0, tzinfo=tz_utc),
instance.checked)
with self.subTest('Update'):
bulk_update_or_create(
AutoNowModel,
[{
'id': 1,
'created': datetime(2012, 2, 5, 1, 2, 3, tzinfo=tz_utc),
'updated': date(2012, 2, 6),
'checked': datetime(2012, 2, 7, 3, 2, 1, tzinfo=tz_utc),
}],
set_functions={
"created": "eq",
"updated": "eq",
"checked": "eq"
})
instance = AutoNowModel.objects.get()
self.assertEqual(datetime(2012, 2, 5, 1, 2, 3, tzinfo=tz_utc),
instance.created)
self.assertEqual(date(2012, 2, 6), instance.updated)
self.assertEqual(datetime(2012, 2, 7, 3, 2, 1, tzinfo=tz_utc),
instance.checked)
def test_quoted_table_name(self):
# Test for https://github.com/M1ha-Shvn/django-pg-bulk-update/issues/63
self.assertEqual(
2,
bulk_update_or_create(TestModelWithSchema, [{
'id': 1,
'name': 'abc'
}, {
'id': 21,
'name': 'create'
}]))
def test_update(self):
with self.assertRaises(TypeError):
bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'test1'
}],
update=123)
with self.assertRaises(TypeError):
bulk_update_or_create(TestModel, [{
'id': 1,
'name': ['test1']
}],
update='123')
self.assertEqual(
1,
bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'test30'
}, {
'id': 20,
'name': 'test30'
}],
update=False))
self.assertEqual(
2,
bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'test30'
}, {
'id': 19,
'name': 'test30'
}],
update=True))
def _test_array_remove(kwargs):
res = bulk_update_or_create(TestModel, [{'id': 1, 'array_field': 1},
{'id': 2, 'array_field': 2},
{'id': 13, 'array_field': 13}],
set_functions={'array_field': 'array_remove'}, **kwargs)
self.assertEqual(3, res)
for pk, array_field in TestModel.objects.filter(id__in=[1, 2, 13]).values_list('pk', 'array_field'):
if pk == 1:
self.assertEqual([2], array_field)
elif pk == 2:
self.assertEqual([1], array_field)
elif pk == 13:
self.assertEqual(None, array_field)
def test_unique_not_primary(self):
"""
Test for issue https://github.com/M1hacka/django-pg-bulk-update/issues/19
:return:
"""
# Test object
UniqueNotPrimary.objects.create(int_field=1)
res = bulk_update_or_create(UniqueNotPrimary, [{
'int_field': 1,
}, {
'int_field': 2,
}], key_fields='int_field')
self.assertEqual(1, res)
self.assertSetEqual({1, 2}, set(UniqueNotPrimary.objects.values_list('int_field', flat=True)))
def test_hstore(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'hstore_field': {
'test': '1'
}
}, {
'id': 2,
'hstore_field': {
'test': '2'
}
}, {
'id': 11,
'hstore_field': {
'test': '11'
}
}, {
'id': 4,
'hstore_field': {}
}, {
'id': 5,
'hstore_field': {
'single': "'",
"multi": '"'
}
}])
self.assertEqual(5, res)
for item in TestModel.objects.all().order_by('id'):
if item.pk in {1, 2, 11}:
self.assertDictEqual({'test': str(item.pk)}, item.hstore_field)
elif item.pk == 4:
self.assertDictEqual({}, item.hstore_field)
elif item.pk == 5:
self.assertDictEqual({
'single': "'",
"multi": '"'
}, item.hstore_field)
else:
self.assertIsNone(item.hstore_field)
if item.pk != 11:
self.assertEqual('test%d' % item.pk, item.name)
else:
self.assertEqual('', item.name)
def test_returning_all(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'name': 'bulk_update_1'
}, {
'id': 5,
'name': 'bulk_update_5'
}, {
'id': 11,
'name': 'bulk_update_11'
}], returning='*')
from django_pg_returning import ReturningQuerySet
self.assertIsInstance(res, ReturningQuerySet)
self.assertSetEqual({
(1, 'bulk_update_1', 1),
(5, 'bulk_update_5', 5),
(11, 'bulk_update_11', None),
}, set(res.values_list('id', 'name', 'int_field')))
def test_jsonb(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'json_field': {
'test': '1'
}
}, {
'id': 2,
'json_field': {
'test': '2'
}
}, {
'id': 11,
'json_field': {
'test': '11'
}
}, {
'id': 4,
'json_field': {}
}, {
'id': 5,
'json_field': {
'single': "'",
"multi": '"'
}
}])
self.assertEqual(5, res)
for pk, name, json_field in TestModel.objects.all().order_by(
'id').values_list('id', 'name', 'json_field'):
if pk in {1, 2, 11}:
self.assertDictEqual({'test': str(pk)}, json_field)
elif pk == 4:
self.assertDictEqual({}, json_field)
elif pk == 5:
self.assertDictEqual({'single': "'", "multi": '"'}, json_field)
else:
self.assertIsNone(json_field)
if pk != 11:
self.assertEqual('test%d' % pk, name)
else:
self.assertEqual('', name)
def test_upper_case(self):
res = bulk_update_or_create(UpperCaseModel, [{
'id': 1,
'UpperCaseName': 'BulkUpdate1'
}, {
'id': 3,
'UpperCaseName': 'BulkUpdate3'
}, {
'id': 4,
'UpperCaseName': 'BulkUpdate4'
}])
self.assertEqual(3, res)
# 3 from fixture + 1 created
self.assertEqual(4, UpperCaseModel.objects.all().count())
for pk, name in UpperCaseModel.objects.all().order_by('id').values_list('id', 'UpperCaseName'):
if pk in {1, 3, 4}:
self.assertEqual('BulkUpdate%d' % pk, name)
else:
self.assertEqual('test%d' % pk, name)
def test_quotes(self):
res = bulk_update_or_create(TestModel, [{
'id': 1,
'name': '\''
}, {
'id': 11,
'name': '"'
}])
self.assertEqual(2, res)
for pk, name, int_field in TestModel.objects.all().order_by('id').values_list('id', 'name', 'int_field'):
if pk == 1:
self.assertEqual('\'', name)
elif pk == 11:
self.assertEqual('"', name)
else:
self.assertEqual('test%d' % pk, name)
if pk != 11:
self.assertEqual(pk, int_field)
else:
self.assertIsNone(int_field)
def test_auto_now_key_not_unique(self):
# Let's check 2 timezones with highest and lowest offsets, one of them should fail
timezones = [(-11, 'Pacific/Pago_Pago'), (14, 'Pacific/Kiritimati')]
for index, (tz_offset, tz_name) in enumerate(timezones, start=11):
with self.subTest('Timezone "%s" with offset %.2f hours' %
(tz_name, tz_offset)):
with override_settings(TIME_ZONE=tz_name):
res = bulk_update_or_create(AutoNowModel, [{
'id': index,
'checked': None
}, {
'id': 1,
'checked': None
}],
key_is_unique=False)
self.assertEqual(2, res)
dt = get_auto_now_date(key_is_unique=False)
for instance in AutoNowModel.objects.filter(pk_in={1, index}):
self.assertEqual(dt, instance.updated)
