def test_substitute_container_variables(self):
plain_query = """SELECT * FROM t_member
WHERE TRUE
AND t_member.member_id IN /*:member_id*/(100, 200)
AND t_member.nickname LIKE /*:nickname*/'%kjim%'
AND t_member.sex IN /*:sex*/('male', 'female')
"""
parameters = dict(member_id=[3845, 295, 1, 637, 221, 357], nickname='%keiji%', sex=('male', 'female', 'other'))
# format: list
template = Template(plain_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND t_member.member_id IN (?, ?, ?, ?, ?, ?)
AND t_member.nickname LIKE ?
AND t_member.sex IN (?, ?, ?)
"""
assert bound_variables == [3845, 295, 1, 637, 221, 357, '%keiji%', 'male', 'female', 'other']
# format: dict
template = Template(plain_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND t_member.member_id IN (:member_id_1, :member_id_2, :member_id_3, :member_id_4, :member_id_5, :member_id_6)
AND t_member.nickname LIKE :nickname
AND t_member.sex IN (:sex_1, :sex_2, :sex_3)
"""
assert bound_variables == dict(
member_id_1=3845, member_id_2=295, member_id_3=1, member_id_4=637, member_id_5=221, member_id_6=357,
nickname='%keiji%',
sex_1='male', sex_2='female', sex_3='other'
)
def test_using_embed_and_substitute(self):
plain_query = """SELECT * FROM t_member
WHERE TRUE
AND t_member.member_id IN /*:member_ids*/(1, 2, 3, 4, 5)
/*#embed condition_on_runtime*/
AND (t_member.nickname LIKE '%kjim%' or t_member.email LIKE '%linux%')
/*#endembed*/
;
"""
parameters = dict(member_ids=[23, 535, 2],
condition_on_runtime="AND t_member.nickname ILIKE 'linus'")
# format: list
template = Template(plain_query, parameter_format=list)
(query, bound_variables) = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND t_member.member_id IN (?, ?, ?)
AND t_member.nickname ILIKE 'linus'
;
"""
assert bound_variables == parameters['member_ids']
# format: dict
template = Template(plain_query, parameter_format=dict)
(query, bound_variables) = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND t_member.member_id IN (:member_ids_1, :member_ids_2, :member_ids_3)
AND t_member.nickname ILIKE 'linus'
;
"""
assert bound_variables == {'member_ids_1': 23, 'member_ids_2': 535, 'member_ids_3': 2}
def test_select_dararows(self):
select_query = """SELECT * FROM t_favorite
WHERE TRUE
/*#embed where_clause*/AND TRUE/*#endembed*/
;
"""
parameters = dict(
where_clause=self.template_where_clause,
use_condition_keyword=True, keywords=['abc', 'def', 'hij'],
use_condition_fetch_status=False,
use_condition_sector=True, sector_table='t_sector_ZZ',
status_activated=1
)
template = Template(select_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query.count("OR UPPER(t_favorite.remarks) LIKE UPPER('%' || ? || '%')") == 3
assert """AND t_favorite.record_type EXISTS (
SELECT 1 FROM t_sector_ZZ
)""" in query
assert bound_variables == ['abc', 'def', 'hij', 1]
template = Template(select_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert "OR UPPER(t_favorite.remarks) LIKE UPPER('%' || :keyword_1 || '%')" in query
assert "OR UPPER(t_favorite.remarks) LIKE UPPER('%' || :keyword_2 || '%')" in query
assert "OR UPPER(t_favorite.remarks) LIKE UPPER('%' || :keyword_3 || '%')" in query
assert """AND t_favorite.record_type EXISTS (
SELECT 1 FROM t_sector_ZZ
)""" in query
assert bound_variables == dict(keyword_1='abc', keyword_2='def', keyword_3='hij', status_activated=1)
def test_simple_substitute(self):
plain_query = """SELECT * FROM t_member WHERE name = /*:nickname*/'kjim'"""
parameters = dict(nickname='keiji')
template = Template(plain_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query == "SELECT * FROM t_member WHERE name = ?"
assert bound_variables == ['keiji']
template = Template(plain_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert query == "SELECT * FROM t_member WHERE name = :nickname"
assert bound_variables == parameters
def test_using_named_value(self):
plain_query = """SELECT * FROM t_member
WHERE TRUE
/*#for item in nickname_items*/
AND (t_member.firstname = /*:item.firstname*/'keiji')
AND (t_member.lastname = /*:item.lastname*/'muraishi')
/*#endfor*/
;
"""
parameters = dict(nickname_items=[
dict(firstname='keiji', lastname='muraishi'),
dict(firstname='x60', lastname='thinkpad'),
])
# format: list
template = Template(plain_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND (t_member.firstname = ?)
AND (t_member.lastname = ?)
AND (t_member.firstname = ?)
AND (t_member.lastname = ?)
;
"""
assert bound_variables == ['keiji', 'muraishi', 'x60', 'thinkpad']
# format: dict
template = Template(plain_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND (t_member.firstname = :item__dot__firstname_1)
AND (t_member.lastname = :item__dot__lastname_1)
AND (t_member.firstname = :item__dot__firstname_2)
AND (t_member.lastname = :item__dot__lastname_2)
;
"""
assert bound_variables == {
'item__dot__firstname_1': 'keiji', 'item__dot__lastname_1': 'muraishi',
'item__dot__firstname_2': 'x60', 'item__dot__lastname_2': 'thinkpad',
}
def test_usage_embed(self):
plain_query = "SELECT * FROM /*#embed table_name*/t_aggregation_AA/*#/embed*/"
parameters = dict(table_name='t_aggregation_AB')
# format: list
template = Template(plain_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query == "SELECT * FROM t_aggregation_AB"
assert bound_variables == []
(query, _) = template.render(table_name='t_aggregation_CB')
assert query == "SELECT * FROM t_aggregation_CB"
# format: dict
template = Template(plain_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert query == "SELECT * FROM t_aggregation_AB"
assert bound_variables == {}
def test_usage_for(self):
plain_query = """SELECT * FROM t_member
WHERE TRUE
/*#for nickname in nicknames*/
AND (t_member.nickname = /*:nickname*/'')
AND (t_member.nickname LIKE /*:nickname_global_cond*/'%')
/*#endfor*/
;
"""
parameters = dict(nicknames=['kjim', 'keiji'], nickname_global_cond='openbooth')
# format: list
template = Template(plain_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND (t_member.nickname = ?)
AND (t_member.nickname LIKE ?)
AND (t_member.nickname = ?)
AND (t_member.nickname LIKE ?)
;
"""
assert bound_variables == ['kjim', 'openbooth', 'keiji', 'openbooth']
# format: dict
template = Template(plain_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
print query
assert query == """SELECT * FROM t_member
WHERE TRUE
AND (t_member.nickname = :nickname_1)
AND (t_member.nickname LIKE :nickname_global_cond)
AND (t_member.nickname = :nickname_2)
AND (t_member.nickname LIKE :nickname_global_cond)
;
"""
assert bound_variables == dict(nickname_1='kjim', nickname_2='keiji', nickname_global_cond='openbooth')
def test_substitute_same_keys(self):
plain_query = """SELECT *
FROM
t_member
INNER JOIN t_member_activation
ON (t_member_activation.member_id = t_member.member_id)
WHERE TRUE
AND t_member.satus = /*:status_activated*/0
AND t_member_activation.status = /*:status_activated*/0
;
"""
parameters = dict(status_activated=1)
# format: list
template = Template(plain_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query == """SELECT *
FROM
t_member
INNER JOIN t_member_activation
ON (t_member_activation.member_id = t_member.member_id)
WHERE TRUE
AND t_member.satus = ?
AND t_member_activation.status = ?
;
"""
assert bound_variables == [1, 1]
# format: dict
template = Template(plain_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert query == """SELECT *
FROM
t_member
INNER JOIN t_member_activation
ON (t_member_activation.member_id = t_member.member_id)
WHERE TRUE
AND t_member.satus = :status_activated
AND t_member_activation.status = :status_activated
;
"""
assert bound_variables == parameters
def test_substitute_scalar_variables(self):
plain_query = """SELECT * FROM t_member
WHERE TRUE
AND t_member.age = /*:age*/1000
AND t_member.nickname = /*:nickname*/'my nickname is holder'
AND t_member.updated_at = /*:updated_at*/CURRENT_TIMESTAMP
AND t_member.created_at <= /*:created_at*/now()
;
"""
created_at, updated_at = datetime.now(), datetime.now()
parameters = dict(age=25, nickname='kjim', updated_at=updated_at, created_at=created_at)
# format: list
template = Template(plain_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND t_member.age = ?
AND t_member.nickname = ?
AND t_member.updated_at = ?
AND t_member.created_at <= ?
;
"""
assert bound_variables == [25, 'kjim', updated_at, created_at]
# format: dict
template = Template(plain_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert query == """SELECT * FROM t_member
WHERE TRUE
AND t_member.age = :age
AND t_member.nickname = :nickname
AND t_member.updated_at = :updated_at
AND t_member.created_at <= :created_at
;
"""
assert bound_variables == parameters
def test_select_count_query(self):
count_query = """
SELECT COUNT(t_favorite.id) FROM t_favorite WHERE TRUE
/*#embed where_clause*/AND TRUE/*#endembed*/
"""
parameters = dict(
where_clause=self.template_where_clause,
use_condition_keyword=False,
use_condition_fetch_status=False,
use_condition_sector=False,
status_activated=1
)
template = Template(count_query, parameter_format=list)
query, bound_variables = template.render(**parameters)
assert 'SELECT COUNT(t_favorite.id) FROM t_favorite WHERE TRUE' in query
assert 'AND t_favorite.status = ?' in query
assert bound_variables == [1]
template = Template(count_query, parameter_format=dict)
query, bound_variables = template.render(**parameters)
assert 'SELECT COUNT(t_favorite.id) FROM t_favorite WHERE TRUE' in query
assert 'AND t_favorite.status = :status_activated' in query
assert bound_variables == dict(status_activated=parameters['status_activated'])