def test_basic_cte():
q1 = Q(lambda: f"select 1")
q2 = Q(lambda: f"select 2 join {q1}")
r = q2.build()
assert r.sql == dedent('''
with
_subQuery0 as (
select 1
)
select 2 join _subQuery0'''
).strip()
assert r.parameters == []
def test_cte_params():
p1 = Parameters(abc='abc')
q1 = Q(lambda: f"select 1 where val = {p1['abc']}", p1)
q2 = Q(lambda: f"select 2 join {q1}")
r = q2.build()
assert r.sql == dedent('''
with
_subQuery0 as (
select 1 where val = :1
)
select 2 join _subQuery0''').strip()
assert r.parameters == ['abc']
def test_parameters_reuse():
p = Parameters(list=[1, 2, 3])
q = Q(lambda: f"select 1 where abc = {p['list']} or def in {p['list']}", p)
assert q.build() == RenderedQuery(
sql="select 1 where abc = ( :1,:2,:3 ) or def in ( :1,:2,:3 )",
parameters=[1, 2, 3])
def test_parameters_list():
p = Parameters(abc='abc', list=[1, 2, 3])
q = Q(lambda: f"select 1 where abc = {p['abc']} or def in {p['list']}", p)
assert q.build() == RenderedQuery(
sql="select 1 where abc = :1 or def in ( :2,:3,:4 )",
parameters=['abc', 1, 2, 3])
def test_reused_params():
p = Parameters(abc='abc', bcd='bcd')
q1 = Q(lambda: f"select 1 where val = {p['abc']}", p)
q2 = Q(
lambda:
f"select 2 join {q1} where val = {p['bcd']} or val = {p['abc']}", p)
r = q2.build()
assert r.sql == dedent('''
with
_subQuery0 as (
select 1 where val = :1
)
select 2 join _subQuery0 where val = :2 or val = :1''').strip()
assert r.parameters == ['abc', 'bcd']
def test_Q_lambda_fstr_in_fn() -> None:
# test what happens when Q call is inside another function...
# yet another thing that broke my AST walker. I'm sure there
# will be many more.
ident = lambda x: x
q = ident(Q(lambda: f"select 1"))
assert q.build() == RenderedQuery(sql="select 1", parameters=[])
def test_multiple_cte():
q1 = Q(lambda: f"select 1")
q2 = Q(lambda: f"select 2 join {q1}")
q3 = Q(lambda: f"select 3 join {q2}")
r = q3.build()
assert r.sql == dedent('''
with
_subQuery0 as (
select 1
),
_subQuery1 as (
select 2 join _subQuery0
)
select 3 join _subQuery1'''
).strip()
assert r.parameters == []
def test_parameters_getattr():
p = Parameters(
abc='abc'
)
q = Q(lambda: f"select 1 where abc = {p.abc}", p)
assert q.build() == RenderedQuery(
sql="select 1 where abc = :1",
parameters=['abc']
)
def test_multi_root_cte():
q1 = Q(lambda: f"select 1")
q2 = Q(lambda: f"select 2 join {q1}")
q3 = Q(lambda: f"select 3")
q4 = Q(lambda: f"select 4 join {q2} join {q3}")
r = q4.build()
assert r.sql == dedent('''
with
_subQuery0 as (
select 1
),
_subQuery1 as (
select 2 join _subQuery0
),
_subQuery2 as (
select 3
)
select 4 join _subQuery1 join _subQuery2'''
).strip()
assert r.parameters == []
def preview_pd(self, con, rows=10) -> "pd.DataFrame":
import pandas as pd
from csql import Q
p = Parameters(rows=rows)
previewQ = Q(lambda: f"""select * from {self} limit {p['rows']}""", p)
return pd.read_sql(**previewQ.build().pd, con=con)
def test_Q_lambda_str() -> None:
q = Q(lambda: "select 1")
assert q.build() == RenderedQuery(sql="select 1", parameters=[])
def test_Q_lambda_fstr_no_assign() -> None:
# can't test the result of this, waahh, but the AST looks
# different and this used to crash.
Q(lambda: f"select 1")
pass
def test_Q_str():
q = Q("select 1")
assert q.build() == RenderedQuery(
sql="select 1",
parameters=[]
)