def test_unwrap_as_scalar(self): customer = self.tables.customer order = self.tables.order sub_stmt = nested(select([order]).as_scalar()).where(order.c.customer_id == customer.c.id).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) self._assert_basic(stmt)
def test_render_core_nested_alone(self): Customer = self.classes.Customer Order = self.classes.Order n = nested(Order).where(Customer.orders) self.assert_compile( n, '(SELECT "order".id, "order".customer_id, "order".order_info ' 'FROM "order", customer WHERE customer.id = "order".customer_id)')
def test_implicit_select_scalar_arg(self): customer = self.tables.customer order = self.tables.order sub_stmt = nested(order).where(order.c.customer_id == customer.c.id).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) self._assert_basic(stmt)
def test_double(self): customer = self.tables.customer order = self.tables.order item = self.tables.item sub_sub_stmt = nested(select([item]).where(item.c.order_id == order.c.id)).label('i') sub_stmt = nested(select([sub_sub_stmt]).where(order.c.customer_id == customer.c.id)).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) self.assert_compile( stmt, 'SELECT (SELECT (SELECT item.id, item.order_id, item.price, ' 'item.quantity FROM item WHERE item.order_id = "order".id) AS i ' 'FROM "order" WHERE "order".customer_id = customer.id) AS o ' 'FROM customer WHERE customer.id = %(id_1)s' )
def test_render_core_nested_alone(self): Customer = self.classes.Customer Order = self.classes.Order n = nested(Order).where(Customer.orders) self.assert_compile( n, '(SELECT "order".id, "order".customer_id, "order".order_info ' 'FROM "order", customer WHERE customer.id = "order".customer_id)' )
def test_double_nested_row(self): customer = self.tables.customer order = self.tables.order item = self.tables.item sub_sub_stmt = nested( select([item]).where(item.c.order_id == order.c.id)).label('i') sub_stmt = nested( select([sub_sub_stmt ]).where(order.c.customer_id == customer.c.id)).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) r = config.db.execute(stmt) row = r.fetchone() sub_result = row['o'] sub_sub_result = sub_result.fetchone()['i'] eq_(list(sub_sub_result), [(1001, 101, Decimal('9.99'), 1), (1002, 101, Decimal('19.99'), 2)]) sub_sub_result = sub_result.fetchone()['i'] eq_(list(sub_sub_result), [(1003, 102, Decimal('9.99'), 1)])
def test_render_core_basic_nested(self): Customer = self.classes.Customer Order = self.classes.Order s = Session() n = nested(Order).where(Customer.orders) q = s.query(Customer, n) self.assert_compile( q, 'SELECT customer.id AS customer_id, ' 'customer.name AS customer_name, (SELECT "order".id, ' '"order".customer_id, "order".order_info FROM "order" ' 'WHERE customer.id = "order".customer_id) AS anon_1 ' 'FROM customer')
def test_str_via_default_compiler(self): customer = self.tables.customer order = self.tables.order sub_stmt = nested(select([order]).where(order.c.customer_id == customer.c.id)).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) eq_( str(stmt).replace("\n", ""), 'SELECT (SELECT "order".id, "order".customer_id, "order".order_info ' 'FROM "order" WHERE "order".customer_id = customer.id) AS o ' 'FROM customer WHERE customer.id = :id_1' )
def test_render_core_basic_nested(self): Customer = self.classes.Customer Order = self.classes.Order s = Session() n = nested(Order).where(Customer.orders) q = s.query(Customer, n) self.assert_compile( q, 'SELECT customer.id AS customer_id, ' 'customer.name AS customer_name, (SELECT "order".id, ' '"order".customer_id, "order".order_info FROM "order" ' 'WHERE customer.id = "order".customer_id) AS anon_1 ' 'FROM customer' )
def test_nested_type_trans(self): customer = self.tables.customer order = self.tables.order item = self.tables.item class SpecialType(TypeDecorator): impl = Integer def process_result_value(self, value, dialect): return str(value) + "_processed" sub_sub_stmt = nested(select([type_coerce(item.c.price, SpecialType)]).\ where(item.c.order_id == order.c.id)).label('i') sub_stmt = nested( select([sub_sub_stmt ]).where(order.c.customer_id == customer.c.id)).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) r = config.db.execute(stmt) row = r.fetchone() sub_result = row['o'] sub_sub_result = sub_result.fetchone()['i'] eq_(list(sub_sub_result), [('9.99_processed', ), ('19.99_processed', )])
def test_nested_row(self): customer = self.tables.customer order = self.tables.order sub_stmt = nested( select([order ]).where(order.c.customer_id == customer.c.id)).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) r = config.db.execute(stmt) row = r.fetchone() sub_result = row['o'] eq_(list(sub_result), [(101, 1, 'apple related'), (102, 1, 'apple related'), (103, 1, 'apple related')])
def test_load_collection_raw(self): Customer = self.classes.Customer Order = self.classes.Order s = Session() n = nested(Order).where(Customer.orders) q = s.query(Customer, n).filter(Customer.id == 1) with self.assert_statement_count(1): row = q.all()[0] eq_(row[0], self._orm_fixture()[0]) eq_(row[1].fetchall(), [(101, 1, u'apple related'), (102, 1, u'apple related'), (103, 1, u'apple related')])
def test_load_collection_raw(self): Customer = self.classes.Customer Order = self.classes.Order s = Session() n = nested(Order).where(Customer.orders) q = s.query(Customer, n).filter(Customer.id == 1) with self.assert_statement_count(1): row = q.all()[0] eq_(row[0], self._orm_fixture()[0]) eq_(row[1].fetchall(), [ (101, 1, u'apple related'), (102, 1, u'apple related'), (103, 1, u'apple related') ])