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_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 pk_and(self):
"""
Return an And() of all this Dict's primary key and values. i.e.
And(id=1, other_primary=4)
"""
return And(*[self._table[k]==v for k,v in self.items() if k in \
self._table.pks])
def args_to_comp(operator, table, *args, **kwargs):
"""
Add arguments to the provided operator paired with their respective primary
key.
"""
operator = operator or And()
pk_uses = 0
pks = table.pks
for val in args:
if isinstance(val, (Comparison, Operator)):
# Already a Comparison/Operator, just add it
operator += (val, )
continue
if not table.pks:
raise NoPrimaryKey('No Primary Keys(s) defined for ' + str(table))
try:
# Create a Comparison using the next Primary Key
operator += (table[pks[pk_uses]] == val, )
except IndexError:
raise NoPrimaryKey('Not enough Primary Keys(s) defined for ' +
str(table))
pk_uses += 1
for k, v in kwargs.items():
operator += table[k] == v
return operator
def test_build(self):
q = Update('some_table', name='Bob')
self.assertEqual(q.build(), ('UPDATE "some_table" SET "name"=%s', [
'Bob',
]))
q = Update('some_table', name='Bob', car_id=2)
self.assertEqual(
q.build(),
('UPDATE "some_table" SET "car_id"=%s, "name"=%s', [2, 'Bob']))
q = Update('some_table', name='Bob', car_id=2).returning('*')
self.assertEqual(
q.build(),
('UPDATE "some_table" SET "car_id"=%s, "name"=%s RETURNING *',
[2, 'Bob']))
q = Update('some_table', name='Bob',
car_id=2).where(Person['id'] == 3).returning('id')
self.assertEqual(q.build(), (
'UPDATE "some_table" SET "car_id"=%s, "name"=%s WHERE "id"=%s RETURNING "id"',
[2, 'Bob', 3]))
q = Update('some_table', name='Bob', car_id=2).where(
And(Person['id'] == 3, Person['car_id'] == 4)).returning('*')
self.assertEqual(q.build(), (
'UPDATE "some_table" SET "car_id"=%s, "name"=%s WHERE "id"=%s AND "car_id"=%s RETURNING *',
[2, 'Bob', 3, 4]))
wheres = And()
wheres += Person['id'] == 3
wheres += Person['car_id'] == 4
q = Update('some_table', name='Bob',
car_id=2).where(wheres).returning('*')
self.assertEqual(q.build(), (
'UPDATE "some_table" SET "car_id"=%s, "name"=%s WHERE "id"=%s AND "car_id"=%s RETURNING *',
[2, 'Bob', 3, 4]))
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_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')