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_logical(self):
bob_name = Person['name'] == 'Bob'
bob_car = Person['car_id'] == 2
q = Select('some_table', bob_name.Or(bob_car))
self.assertEqual(
str(q),
'SELECT * FROM "some_table" WHERE "name"=%s OR "car_id"=%s')
q = Select('some_table', bob_name.And(bob_car))
self.assertEqual(
str(q),
'SELECT * FROM "some_table" WHERE "name"=%s AND "car_id"=%s')
def test_logical_groups(self):
q = Select(
'some_table',
Or(And(Person['name'] >= 'Bob', Person['car_id'] == 2.3),
And(Person['name'] <= 'Alice', Person['car_id'] != 3)))
self.assertEqual(
str(q),
'SELECT * FROM "some_table" WHERE ("name">=%s AND "car_id"=%s) OR ("name"<=%s AND "car_id"!=%s)'
)
def test_basic(self):
q = Select('some_table')
self.assertEqual(str(q), 'SELECT * FROM "some_table"')
q = Select('some_table', And())
self.assertEqual(str(q), 'SELECT * FROM "some_table"')
q = Select('some_table', Person['name'] == 'Bob')
self.assertEqual(str(q), 'SELECT * FROM "some_table" WHERE "name"=%s')
q = Select('some_table', 'Bob' == Person['name'])
self.assertEqual(str(q), 'SELECT * FROM "some_table" WHERE "name"=%s')
q = Select('some_table', Person['name'] > 'Bob')
self.assertEqual(str(q), 'SELECT * FROM "some_table" WHERE "name">%s')
q = Select('some_table', Person['name'] >= 'Bob')
self.assertEqual(str(q), 'SELECT * FROM "some_table" WHERE "name">=%s')
q = Select('some_table', Person['name'] < 'Bob')
self.assertEqual(str(q), 'SELECT * FROM "some_table" WHERE "name"<%s')
q = Select('some_table', Person['name'] <= 'Bob')
self.assertEqual(str(q), 'SELECT * FROM "some_table" WHERE "name"<=%s')
q = Select('some_table', Person['name'] != 'Bob')
self.assertEqual(str(q), 'SELECT * FROM "some_table" WHERE "name"!=%s')
def get_where(self, *a, **kw):
"""
Get all rows as Dicts where column values are as specified. This always
returns a generator-like object ResultsGenerator.
If you provide only arguments, they will be paired in their respective
order to the primary keys defined or this table. If the primary keys
of this table was ('id',) only:
get_where(4) is equal to get_where(id=4)
get_where(4, 5) would raise a NoPrimaryKey error because there is
only one primary key.
Primary keys are defined automatically during the init of the Table, but
you can overwrite that by changing .pks:
>>> your_table.pks = ['id', 'some_column', 'whatever_you_want']
get_where(4, 5, 6) is now equal to get_where(id=4, some_column=5,
whatever_you_want=6)
If there were two primary keys, such as in a join table (id, group):
get_where(4, 5) is equal to get_where(id=4, group=5)
You cannot use this method without primary keys, unless you specify the
column you are matching.
>>> get_where(some_column=83)
ResultsGenerator()
>>> get_where(4) # no primary keys defined!
NoPrimaryKey()
"""
# All args/kwargs are combined in an SQL And comparison
operator_group = args_to_comp(And(), self, *a, **kw)
order_by = None
if self.order_by:
order_by = self.order_by
elif self.pks:
order_by = str(self.pks[0]) + ' ASC'
query = Select(self.name, operator_group).order_by(order_by)
return ResultsGenerator(self, query, self.db)
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_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_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_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%',
]))