async def test_manual_logic(conn):
query, params = render(
'SELECT :select FROM users WHERE :where ORDER BY :order_by',
select=select_fields('first_name'),
where=V('created') > datetime(2021, 1, 1),
order_by=V('last_name'),
)
v = await conn.fetch(query, *params)
assert ['Fred', 'Joe'] == [r[0] for r in v]
async def test_cursor(conn):
results = []
q = 'SELECT :s FROM users ORDER BY :o'
async for r in conn.cursor_b(q,
s=select_fields('first_name'),
o=V('first_name').asc()):
results.append(tuple(r))
if len(results) == 2:
break
assert results == [('Franks', ), ('Fred', )]
async def test_manual_select(conn):
query, params = render('SELECT :v FROM users ORDER BY first_name',
v=select_fields('first_name', 'last_name'))
v = await conn.fetch(query, *params)
assert [
{
'first_name': 'Franks',
'last_name': 'spencer'
},
{
'first_name': 'Fred',
'last_name': 'blogs'
},
{
'first_name': 'Joe',
'last_name': None
},
] == [dict(r) for r in v]
(lambda: funcs.comma_sep(V('first_name'), 123), 'first_name, $1'),
(lambda: Func('foobar', V('x'), V('y')), 'foobar(x, y)'),
(lambda: Func('foobar', funcs.comma_sep('x', 'y')), 'foobar($1, $2)'),
(lambda: Empty() & (V('foo') == 4), ' AND foo = $1'),
(lambda: Empty() & (V('bar') == 4), ' AND bar = $1'),
(lambda: V('epoch').at_time_zone('MST'), 'epoch AT TIME ZONE $1'),
(lambda: S('2032-02-16 19:38:40-08').at_time_zone('MST'), '$1 AT TIME ZONE $2'),
(lambda: V('foo').matches(V('bar')), 'foo @@ bar'),
(lambda: V('foo').is_(V('bar')), 'foo is bar'),
(lambda: V('foo').is_not(V('bar')), 'foo is not bar'),
(
lambda: funcs.to_tsvector('fat cats ate rats').matches(funcs.to_tsquery('cat & rat')),
'to_tsvector($1) @@ to_tsquery($2)',
),
(lambda: funcs.now(), 'now()'),
(lambda: select_fields('foo', 'bar'), 'foo, bar'),
(lambda: select_fields('foo', S(RawDangerous("'raw text'"))), "foo, 'raw text'"),
(lambda: funcs.NOT(V('a').in_([1, 2])), 'not(a in $1)'),
(lambda: ~V('a').in_([1, 2]), 'not(a in $1)'),
(lambda: funcs.NOT(V('a') == funcs.any([1, 2])), 'not(a = any($1))'),
],
)
def test_simple_blocks(block, expected_query):
query, _ = render(':v', v=block())
assert expected_query == query
@pytest.mark.parametrize(
'block,s',
[
(lambda: SqlBlock(1) == 2, '<SQL: "1 = 2">'),