def test_not_in_(self):
v = Foo.age.not_in_([1])
sql, _ = v.get_sql_and_params()
self.assertEqual(sql, '`age` NOT IN %s')
f = UnionField(Foo.age, Foo.id)
v = f.not_in_([1])
sql, _ = v.get_sql_and_params()
self.assertEqual(sql, '(`age`, `id`) NOT IN %s')
def test_not_in_(self):
exp = Foo.age.not_in_([1])
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE', 'NOT IN', ['COLUMN', 'foo', 'age'],
['VALUE', [1]]
])
f = UnionField(Foo.age, Foo.id)
exp = f.not_in_([1])
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE', 'NOT IN',
['BRACKET', ['COLUMN', 'foo', 'age'], ['COLUMN', 'foo', 'id']],
['VALUE', [1]]
])
def _get_multi(cls, idents, filter_none=True):
if not idents:
return []
if not type_checker([dict], idents):
pk_name = cls.get_singleness_pk_name()
pk_field = getattr(cls, pk_name)
items = cls._get_multi_by(pk_field.in_(idents))
mapping = {str(getattr(item, pk_name)): item for item in items}
else:
ident = idents[0]
items = cls._get_multi_by(
UnionField(*[getattr(cls, k) for k in ident]).in_(
[_ident.values() for _ident in idents]))
mapping = {
tuple(str(getattr(item, k)) for k in ident): item
for item in items
}
res = []
for ident in idents:
if not isinstance(ident, dict):
item = mapping.get(str(ident))
else:
item = mapping.get(tuple(map(str, ident.values())))
if item is None and filter_none:
continue
res.append(item)
return res
def test_expression(self):
cond = Dummy.id == Dummy.age
ast = cond.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'id'],
['COLUMN', 'dummy', 'age']
])
cond = Dummy.id > 2
ast = cond.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'>',
['COLUMN', 'dummy', 'id'],
['VALUE', 2]
])
cond = Dummy.id << [1, 2]
ast = cond.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'IN',
['COLUMN', 'dummy', 'id'],
['VALUE', (1, 2)]
])
cond = (Dummy.id > 1) & ((Dummy.age == 3) | (Dummy.age == 2))
ast = cond.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'AND',
['BINARY_OPERATE',
'>',
['COLUMN', 'dummy', 'id'],
['VALUE', 1]
],
['BINARY_OPERATE',
'OR',
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 3]],
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 2]
]]
])
cond = (Dummy.id > 2) & (Dummy.age == 3) | (Dummy.age == 1)
ast = cond.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'OR',
['BINARY_OPERATE',
'AND',
['BINARY_OPERATE',
'>',
['COLUMN', 'dummy', 'id'],
['VALUE', 2]
],
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 3]]],
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 1]],
])
cond = ((Dummy.age == 3) | (Dummy.age == 2)) & (Dummy.id > 2)
ast = cond.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'AND',
['BINARY_OPERATE',
'OR',
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 3]
],
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 2]]],
['BINARY_OPERATE',
'>',
['COLUMN', 'dummy', 'id'],
['VALUE', 2]],
])
cond = (Dummy.id > 2) & ((Dummy.age == 3) | (Dummy.age == 2)) & (Dummy.id < 4) # noqa
ast = cond.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'AND',
['BINARY_OPERATE',
'AND',
['BINARY_OPERATE',
'>',
['COLUMN', 'dummy', 'id'],
['VALUE', 2]],
['BINARY_OPERATE',
'OR',
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 3]
],
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 2]]],
],
['BINARY_OPERATE',
'<',
['COLUMN', 'dummy', 'id'],
['VALUE', 4]],
])
exp = Dummy.age == Dummy.age + 1
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['BINARY_OPERATE',
'+',
['COLUMN', 'dummy', 'age'],
['VALUE', 1]]
])
exp = Dummy.age == 1
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 1]
])
exp = Dummy.age.in_([1, 2])
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'IN',
['COLUMN', 'dummy', 'age'],
['VALUE', (1, 2)]
])
a = Dummy.age.alias('xixi')
exp = a == 1
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'=',
['ALIAS',
['COLUMN', 'dummy', 'age'],
'xixi'],
['VALUE', 1]
])
a = Dummy.age.asc()
ast = a.get_sql_ast()
self.assertEqual(ast, [
'UNARY_OPERATE',
['COLUMN', 'dummy', 'age'],
'ASC',
True
])
a = Dummy.age == 1
exp = a.asc()
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'UNARY_OPERATE',
['BINARY_OPERATE',
'=',
['COLUMN', 'dummy', 'age'],
['VALUE', 1]],
'ASC',
True
])
f = UnionField(Dummy.age, Dummy.id)
exp = f.not_in_([1])
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'NOT IN',
['BRACKET',
['COLUMN', 'dummy', 'age'],
['COLUMN', 'dummy', 'id']],
['VALUE', (1,)]
])
exp = Dummy.id.in_(Foo.query('id').filter(Foo.age > 2))
ast = exp.get_sql_ast()
self.assertEqual(ast, [
'BINARY_OPERATE',
'IN',
['COLUMN', 'dummy', 'id'],
['SELECT',
['SERIES',
['COLUMN', 'foo', 'id']],
['FROM',
['TABLE', 'foo'],
],
['WHERE',
['BINARY_OPERATE',
'>',
['COLUMN', 'foo', 'age'],
['VALUE', 2]]]]
])
def test_expression(self):
with field_verbose_context(True):
cond = Dummy.id == Dummy.age
sql, params = cond.get_sql_and_params()
self.assertEqual(sql, '`dummy`.`id` = `dummy`.`age`')
self.assertEqual(params, [])
cond = Dummy.id > 2
sql, params = cond.get_sql_and_params()
self.assertEqual(sql, '`dummy`.`id` > %s')
self.assertEqual(params, [2])
cond = Dummy.id << [1, 2]
sql, params = cond.get_sql_and_params()
self.assertEqual(sql, '`dummy`.`id` IN %s')
self.assertEqual(params, [[1, 2]])
cond = (Dummy.id > 1) & ((Dummy.age == 3) | (Dummy.age == 2))
sql, params = cond.get_sql_and_params()
self.assertEqual(
sql,
'`dummy`.`id` > %s AND (`dummy`.`age` = %s OR `dummy`.`age` = %s)' # noqa
)
self.assertEqual(params, [1, 3, 2])
cond = (Dummy.id > 2) & (Dummy.age == 3) | (Dummy.age == 1)
sql, params = cond.get_sql_and_params()
self.assertEqual(
sql,
'`dummy`.`id` > %s AND `dummy`.`age` = %s OR `dummy`.`age` = %s' # noqa
)
self.assertEqual(params, [2, 3, 1])
cond = ((Dummy.age == 3) | (Dummy.age == 2)) & (Dummy.id > 2)
sql, params = cond.get_sql_and_params()
self.assertEqual(
sql,
'(`dummy`.`age` = %s OR `dummy`.`age` = %s) AND `dummy`.`id` > %s' # noqa
)
self.assertEqual(params, [3, 2, 2])
cond = (Dummy.id > 2) & ((Dummy.age == 3) |
(Dummy.age == 2)) & (Dummy.id < 4) # noqa
sql, params = cond.get_sql_and_params()
self.assertEqual(
sql,
'`dummy`.`id` > %s AND (`dummy`.`age` = %s OR `dummy`.`age` = %s) AND `dummy`.`id` < %s' # noqa
)
self.assertEqual(params, [2, 3, 2, 4])
cond = Dummy.id == Dummy.age
sql, params = cond.get_sql_and_params()
self.assertEqual(sql, '`id` = `age`')
cond = ((Dummy.age == 3) | (Dummy.age == 2)) & (Dummy.id > 2) # noqa
sql, params = cond.get_sql_and_params()
self.assertEqual(
sql,
'(`age` = %s OR `age` = %s) AND `id` > %s' # noqa
)
cond = ((Dummy.age == 3) | (Dummy.age == 2)) & (
(Dummy.id > 2) | (Dummy.id < 1)) # noqa
sql, params = cond.get_sql_and_params()
self.assertEqual(
sql,
'(`age` = %s OR `age` = %s) AND (`id` > %s OR `id` < %s)' # noqa
)
exp = Dummy.age == Dummy.age + 1
sql, params = exp.get_sql_and_params()
self.assertEqual(sql, '`age` = `age` + %s')
self.assertEqual(params, [1])
exp = Dummy.age == 1
sql, params = exp.get_sql_and_params()
self.assertEqual(sql, '`age` = %s')
self.assertEqual(params, [1])
exp = Dummy.age.in_([1, 2])
sql, params = exp.get_sql_and_params()
self.assertEqual(sql, '`age` IN %s')
self.assertEqual(params, [[1, 2]])
a = Dummy.age.alias('xixi')
exp = a == 1
sql, params = exp.get_sql_and_params()
self.assertEqual(sql, 'xixi = %s')
self.assertEqual(params, [1])
a = Dummy.age.asc()
sql, params = a.get_sql_and_params()
self.assertEqual(sql, '`age` ASC')
a = Dummy.age == 1
exp = a.asc()
sql, params = exp.get_sql_and_params()
self.assertEqual(sql, '`age` = %s ASC')
self.assertEqual(params, [1])
f = UnionField(Dummy.age, Dummy.id)
exp = f.not_in_([1])
sql, params = exp.get_sql_and_params()
self.assertEqual(sql, '(`age`, `id`) NOT IN %s')
exp = Dummy.id.in_(Foo.query('id').filter(Foo.age > 2))
sql, params = exp.get_sql_and_params()
self.assertEqual(sql,
'`id` IN (SELECT `id` FROM `foo` WHERE `age` > %s )')
self.assertEqual(params, [2])