def test_deterministic_merge_into(sql_compiler):
meta = MetaData()
users = Table(
'users', meta,
Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
Column('name', String), Column('fullname', String))
onboarding_users = Table(
'onboarding_users', meta,
Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
Column('name', String), Column('fullname', String),
Column('delete', Boolean))
merge = MergeInto(users, onboarding_users,
users.c.id == onboarding_users.c.id)
merge.when_matched_then_update().values(
name=onboarding_users.c.name, fullname=onboarding_users.c.fullname)
merge.when_not_matched_then_insert().values(
id=onboarding_users.c.id,
name=onboarding_users.c.name,
fullname=onboarding_users.c.fullname,
).where(onboarding_users.c.fullname != None)
assert sql_compiler(merge) == "MERGE INTO users USING onboarding_users ON users.id = onboarding_users.id " \
"WHEN MATCHED THEN UPDATE SET fullname = onboarding_users.fullname, " \
"name = onboarding_users.name WHEN NOT MATCHED AND onboarding_users.fullname " \
"IS NOT NULL THEN INSERT (fullname, id, name) VALUES (onboarding_users.fullname, " \
"onboarding_users.id, onboarding_users.name)"
def test_deterministic_merge_into(sql_compiler):
meta = MetaData()
users = Table('users', meta,
Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
Column('name', String),
Column('fullname', String))
onboarding_users = Table('onboarding_users', meta,
Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
Column('name', String),
Column('fullname', String),
Column('delete', Boolean))
merge = MergeInto(users, onboarding_users, users.c.id == onboarding_users.c.id)
merge.when_matched_then_update().values(name=onboarding_users.c.name,
fullname=onboarding_users.c.fullname)
merge.when_not_matched_then_insert().values(
id=onboarding_users.c.id,
name=onboarding_users.c.name,
fullname=onboarding_users.c.fullname,
).where(onboarding_users.c.fullname != None)
assert sql_compiler(merge) == "MERGE INTO users USING onboarding_users ON users.id = onboarding_users.id " \
"WHEN MATCHED THEN UPDATE SET fullname = onboarding_users.fullname, " \
"name = onboarding_users.name WHEN NOT MATCHED AND onboarding_users.fullname " \
"IS NOT NULL THEN INSERT (fullname, id, name) VALUES (onboarding_users.fullname, " \
"onboarding_users.id, onboarding_users.name)"
def test_upsert(engine_testaccount, update_flag, insert_flag, delete_flag,
conditional_flag):
meta = MetaData()
users = Table(
'users', meta,
Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
Column('name', String), Column('fullname', String))
onboarding_users = Table(
'onboarding_users', meta,
Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
Column('name', String), Column('fullname', String),
Column('delete', Boolean))
meta.create_all(engine_testaccount)
conn = engine_testaccount.connect()
try:
conn.execute(users.insert(), [{
'id': 1,
'name': 'mark',
'fullname': 'Mark Keller'
}, {
'id': 4,
'name': 'luke',
'fullname': 'Luke Lorimer'
}, {
'id': 2,
'name': 'amanda',
'fullname': 'Amanda Harris'
}])
conn.execute(onboarding_users.insert(), [{
'id': 2,
'name': 'amanda',
'fullname': 'Amanda Charlotte Harris',
'delete': True
}, {
'id': 3,
'name': 'jim',
'fullname': 'Jim Wang',
'delete': False
}, {
'id': 4,
'name': 'lukas',
'fullname': 'Lukas Lorimer',
'delete': False
}, {
'id': 5,
'name': 'andras',
'fullname': None,
'delete': False
}])
merge = MergeInto(users, onboarding_users,
users.c.id == onboarding_users.c.id)
if update_flag:
clause = merge.when_matched_then_update().values(
name=onboarding_users.c.name,
fullname=onboarding_users.c.fullname)
if conditional_flag:
clause.where(onboarding_users.c.name != 'amanda')
if insert_flag:
clause = merge.when_not_matched_then_insert().values(
id=onboarding_users.c.id,
name=onboarding_users.c.name,
fullname=onboarding_users.c.fullname,
)
if conditional_flag:
clause.where(onboarding_users.c.fullname != None)
if delete_flag:
clause = merge.when_matched_then_delete()
if conditional_flag:
clause.where(onboarding_users.c.delete == True)
conn.execute(merge)
users_tuples = {tuple(row) for row in conn.execute(select([users]))}
onboarding_users_tuples = {
tuple(row)
for row in conn.execute(select([onboarding_users]))
}
expected_users = {(1, 'mark', 'Mark Keller'),
(2, 'amanda', 'Amanda Harris'),
(4, 'luke', 'Luke Lorimer')}
if update_flag:
if not conditional_flag:
expected_users.remove((2, 'amanda', 'Amanda Harris'))
expected_users.add((2, 'amanda', 'Amanda Charlotte Harris'))
expected_users.remove((4, 'luke', 'Luke Lorimer'))
expected_users.add((4, 'lukas', 'Lukas Lorimer'))
elif delete_flag:
if not conditional_flag:
expected_users.remove((4, 'luke', 'Luke Lorimer'))
expected_users.remove((2, 'amanda', 'Amanda Harris'))
if insert_flag:
if not conditional_flag:
expected_users.add((5, 'andras', None))
expected_users.add((3, 'jim', 'Jim Wang'))
expected_onboarding_users = {(2, 'amanda', 'Amanda Charlotte Harris',
True), (3, 'jim', 'Jim Wang', False),
(4, 'lukas', 'Lukas Lorimer', False),
(5, 'andras', None, False)}
assert users_tuples == expected_users
assert onboarding_users_tuples == expected_onboarding_users
finally:
conn.close()
users.drop(engine_testaccount)
onboarding_users.drop(engine_testaccount)
def test_upsert(engine_testaccount, update_flag, insert_flag, delete_flag, conditional_flag):
meta = MetaData()
users = Table('users', meta,
Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
Column('name', String),
Column('fullname', String))
onboarding_users = Table('onboarding_users', meta,
Column('id', Integer, Sequence('new_user_id_seq'), primary_key=True),
Column('name', String),
Column('fullname', String),
Column('delete', Boolean))
meta.create_all(engine_testaccount)
conn = engine_testaccount.connect()
try:
conn.execute(users.insert(), [
{'id': 1, 'name': 'mark', 'fullname': 'Mark Keller'},
{'id': 4, 'name': 'luke', 'fullname': 'Luke Lorimer'},
{'id': 2, 'name': 'amanda', 'fullname': 'Amanda Harris'}])
conn.execute(onboarding_users.insert(), [
{'id': 2, 'name': 'amanda', 'fullname': 'Amanda Charlotte Harris', 'delete': True},
{'id': 3, 'name': 'jim', 'fullname': 'Jim Wang', 'delete': False},
{'id': 4, 'name': 'lukas', 'fullname': 'Lukas Lorimer', 'delete': False},
{'id': 5, 'name': 'andras', 'fullname': None, 'delete': False}
])
merge = MergeInto(users, onboarding_users, users.c.id == onboarding_users.c.id)
if update_flag:
clause = merge.when_matched_then_update().values(name=onboarding_users.c.name,
fullname=onboarding_users.c.fullname)
if conditional_flag:
clause.where(onboarding_users.c.name != 'amanda')
if insert_flag:
clause = merge.when_not_matched_then_insert().values(
id=onboarding_users.c.id,
name=onboarding_users.c.name,
fullname=onboarding_users.c.fullname,
)
if conditional_flag:
clause.where(onboarding_users.c.fullname != None)
if delete_flag:
clause = merge.when_matched_then_delete()
if conditional_flag:
clause.where(onboarding_users.c.delete == True)
conn.execute(merge)
users_tuples = {tuple(row) for row in conn.execute(select([users]))}
onboarding_users_tuples = {tuple(row) for row in conn.execute(select([onboarding_users]))}
expected_users = {
(1, 'mark', 'Mark Keller'),
(2, 'amanda', 'Amanda Harris'),
(4, 'luke', 'Luke Lorimer')
}
if update_flag:
if not conditional_flag:
expected_users.remove((2, 'amanda', 'Amanda Harris'))
expected_users.add((2, 'amanda', 'Amanda Charlotte Harris'))
expected_users.remove((4, 'luke', 'Luke Lorimer'))
expected_users.add((4, 'lukas', 'Lukas Lorimer'))
elif delete_flag:
if not conditional_flag:
expected_users.remove((4, 'luke', 'Luke Lorimer'))
expected_users.remove((2, 'amanda', 'Amanda Harris'))
if insert_flag:
if not conditional_flag:
expected_users.add((5, 'andras', None))
expected_users.add((3, 'jim', 'Jim Wang'))
expected_onboarding_users = {
(2, 'amanda', 'Amanda Charlotte Harris', True),
(3, 'jim', 'Jim Wang', False),
(4, 'lukas', 'Lukas Lorimer', False),
(5, 'andras', None, False)
}
assert users_tuples == expected_users
assert onboarding_users_tuples == expected_onboarding_users
finally:
conn.close()
users.drop(engine_testaccount)
onboarding_users.drop(engine_testaccount)