def test_select_tuple(self):
q = Select('cool_table', Person['id'].In((1, 2)))
self.assertEqual(q.build(),
('SELECT * FROM "cool_table" WHERE "id" IN %s', [
(1, 2),
]))
q = Select('cool_table', Person['id'].In((1, 2))).order_by('id DESC')
self.assertEqual(
q.build(),
('SELECT * FROM "cool_table" WHERE "id" IN %s ORDER BY id DESC', [
(1, 2),
]))
def test_offset(self):
q = Select('other_table',
Person['name'] == 'Steve').order_by('id ASC').offset(8)
self.assertEqual(q.build(), (
'SELECT * FROM "other_table" WHERE "name"=%s ORDER BY id ASC OFFSET 8',
[
'Steve',
]))
q = Select(
'other_table',
Person['name'] == 'Steve').order_by('id ASC').offset(8).limit(12)
self.assertEqual(q.build(), (
'SELECT * FROM "other_table" WHERE "name"=%s ORDER BY id ASC LIMIT 12 OFFSET 8',
[
'Steve',
]))
def test_order_by(self):
q = Select('other_table', Person['name'] == 'Steve').order_by('id ASC')
self.assertEqual(
q.build(),
('SELECT * FROM "other_table" WHERE "name"=%s ORDER BY id ASC', [
'Steve',
]))
def test_build(self):
"""
building the query results in a tuple of the sql and a list of values
that need to be interpolated into the sql by Psycopg2.
"""
q = Select('other_table', Person['name'] == 'Steve')
self.assertEqual(q.build(),
('SELECT * FROM "other_table" WHERE "name"=%s', [
'Steve',
]))
q = Select('other_table',
And(Person['name'] == 'Steve', Person['car_id'] == 12))
self.assertEqual(
q.build(),
('SELECT * FROM "other_table" WHERE "name"=%s AND "car_id"=%s',
['Steve', 12]))
q = Select(
'other_table',
Or(And(Person['name'] == 'Steve', Person['car_id'] == 12),
And(Person['name'] == 'Bob', Person['car_id'] == 1)))
self.assertEqual(q.build(), (
'SELECT * FROM "other_table" WHERE ("name"=%s AND "car_id"=%s) OR ("name"=%s AND "car_id"=%s)',
['Steve', 12, 'Bob', 1]))
q = Select('other_table', Person['name'].Is('Bob'))
self.assertEqual(q.build(),
('SELECT * FROM "other_table" WHERE "name" IS %s', [
'Bob',
]))
q = Select('other_table', Person['name'].IsNot('Bob'))
self.assertEqual(
q.build(), ('SELECT * FROM "other_table" WHERE "name" IS NOT %s', [
'Bob',
]))
q = Select('other_table', Person['name'].IsNull())
self.assertEqual(
q.build(),
('SELECT * FROM "other_table" WHERE "name" IS NULL', []))
q = Select(
'other_table',
And(
Person['name'].IsNull(),
Person['foo'] == 'bar',
Person['baz'].Is('bake'),
Person['whatever'].IsDistinct('foo'),
Person['whatever'].IsNotDistinct('bar'),
))
self.assertEqual(q.build(), (
'SELECT * FROM "other_table" WHERE "name" IS NULL AND "foo"=%s AND "baz" IS %s AND "whatever" IS DISTINCT FROM %s AND "whatever" IS NOT DISTINCT FROM %s',
['bar', 'bake', 'foo', 'bar']))
q = Select('other_table', Person['name'].IsNotNull())
self.assertEqual(
q.build(),
('SELECT * FROM "other_table" WHERE "name" IS NOT NULL', []))
def test_returning(self):
q = Select('some_table', Person['name'] == 'Bob', returning='*')
self.assertEqual(
str(q), 'SELECT * FROM "some_table" WHERE "name"=%s RETURNING *')
self.assertEqual(
q.build(),
('SELECT * FROM "some_table" WHERE "name"=%s RETURNING *', [
'Bob',
]))
q = Select('some_table', 'Bob' == Person['name'], returning='id')
self.assertEqual(
str(q),
'SELECT * FROM "some_table" WHERE "name"=%s RETURNING "id"')
self.assertEqual(
q.build(),
('SELECT * FROM "some_table" WHERE "name"=%s RETURNING "id"', [
'Bob',
]))
def test_like(self):
q = Select('other_table', Person['name'].Like('Steve'))
self.assertEqual(q.build(),
('SELECT * FROM "other_table" WHERE "name" LIKE %s', [
'Steve',
]))
q = Select('other_table', Person['name'].Ilike('Steve'))
self.assertEqual(
q.build(), ('SELECT * FROM "other_table" WHERE "name" ILIKE %s', [
'Steve',
]))
q = Select('other_table', Person['name'].Like('%Steve%'))
self.assertEqual(q.build(),
('SELECT * FROM "other_table" WHERE "name" LIKE %s', [
'%Steve%',
]))
q = Select('other_table', Person['name'].Ilike('%Steve%'))
self.assertEqual(
q.build(), ('SELECT * FROM "other_table" WHERE "name" ILIKE %s', [
'%Steve%',
]))