def test_limit_with_offset(self):
table = self.create_table()
query = session.query(table.c.x).limit(10).offset(5)
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT x AS t1_x FROM t1 LIMIT 5, 10')
query = session.query(table.c.x).offset(5).limit(10)
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT x AS t1_x FROM t1 LIMIT 5, 10')
def test_escape_binary_mod(self):
query = session.query(literal(1) % literal(2))
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT 1 %% 2 AS anon_1')
table = Table('t', self.metadata(),
Column('x', types.Int32, primary_key=True),
engines.Memory())
query = session.query(table.c.x % table.c.x)
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT x %% x AS anon_1 FROM t')
def test_group_by_query(self):
table = self.create_table()
query = session.query(table.c.x).group_by(table.c.x)
self.assertEqual(self.compile(query),
'SELECT x AS t1_x FROM t1 GROUP BY x')
query = session.query(table.c.x).group_by(table.c.x).with_totals()
self.assertEqual(self.compile(query),
'SELECT x AS t1_x FROM t1 GROUP BY x WITH TOTALS')
with self.assertRaises(exc.InvalidRequestError) as ex:
session.query(table.c.x).with_totals()
self.assertIn('with_totals', str(ex.exception))
def test_select(self):
table = self.create_table()
query = session.query(table.c.x).filter(table.c.x.in_([1, 2]))
self.assertEqual(
self.compile(query),
'SELECT x AS t1_x FROM t1 WHERE x IN (%(x_1)s, %(x_2)s)')
def test_joins(self):
t1, t2 = self.create_tables(2)
query = session.query(t1.c.x, t2.c.x) \
.join(t2, tuple_(t1.c.x, t1.c.y), any=True)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"ANY INNER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.join(t2, tuple_(t1.c.x, t1.c.y), all=True)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"ALL INNER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.join(t2, tuple_(t1.c.x, t1.c.y), all=True, global_=True)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"GLOBAL ALL INNER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.outerjoin(t2, tuple_(t1.c.x, t1.c.y), all=True, global_=True)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"GLOBAL ALL LEFT OUTER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.outerjoin(t2, tuple_(t1.c.x, t1.c.y), all=True, global_=True)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"GLOBAL ALL LEFT OUTER JOIN t1 USING x, y"
)
def test_unsupported_expressoin(self):
t1, t2 = self.create_tables(2)
query = session.query(t1.c.x).join(t2, literal(True), any=True)
with self.assertRaises(exc.CompileError) as ex:
self.compile(query)
self.assertIn('Only tuple elements are supported', str(ex.exception))
def test_parse_func_count(self):
mock.add(mock.POST, self.url, status=200, body='count_1\nUInt64\n42\n')
table = Table('t1', self.metadata(),
Column('x', types.Int32, primary_key=True))
rv = session.query(func.count()).select_from(table).scalar()
self.assertEqual(rv, 42)
def test_sample(self):
table = self.create_table()
query = session.query(table.c.x).sample(0.1).group_by(table.c.x)
self.assertEqual(
self.compile(query),
'SELECT x AS t1_x FROM t1 SAMPLE %(param_1)s GROUP BY x')
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT x AS t1_x FROM t1 SAMPLE 0.1 GROUP BY x')
def test_offset_without_limit(self):
table = self.create_table()
with self.assertRaises(exc.CompileError) as ex:
query = session.query(table.c.x).offset(5)
self.compile(query, literal_binds=True)
self.assertEqual(str(ex.exception),
'OFFSET without LIMIT is not supported')
def test_parse_nullable_type(self):
mock.add(mock.POST,
self.url,
status=200,
body=('a\n' + 'String\n' + '\\N\n' + '\\\\N\n' + '\n'))
table = Table('t1', self.metadata(), Column('a', types.String))
rv = session.query(*table.c).all()
self.assertEqual(rv, [(None, ), ('\\N', ), ('', )])
def test_parse_date_types(self, patched_server_info):
mock.add(mock.POST,
self.url,
status=200,
body=('a\n' + 'Date\n' + '2012-10-25\n'))
table = Table('t1', self.metadata(), Column('a', types.Date))
rv = session.query(*table.c).first()
self.assertEqual(rv, (date(2012, 10, 25), ))
def test_lambda_functions(self):
query = session.query(
func.arrayFilter(
Lambda(lambda x: x.like('%World%')),
literal(['Hello', 'abc World'],
types.Array(types.String))).label('test'))
self.assertEqual(
self.compile(query, literal_binds=True), "SELECT arrayFilter("
"x -> x LIKE '%%World%%', ['Hello', 'abc World']"
") AS test")
def test_parse_date_types(self):
mock.add(mock.POST,
'http://localhost:8123',
status=200,
body=('a\n' + 'Date\n' + '2012-10-25\n'))
table = Table('t1', self.metadata(), Column('a', types.Date))
rv = session.query(*table.c).first()
self.assertEqual(rv, (date(2012, 10, 25), ))
def test_select(self):
table = self.create_table()
query = session.query(table.c.x)\
.filter(table.c.x.in_([1, 2]))\
.having(func.count() > 0)\
.order_by(table.c.x.desc())
self.assertEqual(
self.compile(query), 'SELECT x AS t1_x '
'FROM t1 '
'WHERE x IN (%(x_1)s, %(x_2)s) '
'HAVING count(*) > %(count_1)s '
'ORDER BY x DESC')
def test_insert_from_select_no_format_clause(self):
metadata = self.metadata()
bind = session.bind
bind.cursor = lambda: None
t1 = Table('t1', metadata, Column('x', types.Int32, primary_key=True))
t2 = Table('t2', metadata, Column('x', types.Int32, primary_key=True))
query = t2.insert() \
.from_select(['x'], session.query(t1.c.x).subquery())
statement = self.compile(query, bind=bind)
self.assertEqual(statement, 'INSERT INTO t2 (x) SELECT x FROM t1')
def test_select_format_clause(self):
metadata = self.metadata()
bind = session.bind
bind.cursor = lambda: None
table = Table('t1', metadata, Column('x',
types.Int32,
primary_key=True))
statement = self.compile(session.query(table.c.x), bind=bind)
self.assertEqual(
statement,
'SELECT x AS t1_x FROM t1 FORMAT TabSeparatedWithNamesAndTypes')
def test_parse_float_types(self):
types_ = ['Float32', 'Float64']
columns = ['a', 'b']
mock.add(mock.POST,
self.url,
status=200,
body=('\t'.join(columns) + '\n' + '\t'.join(types_) + '\n' +
'\t'.join(['42'] * len(types_)) + '\n'))
table = Table('t1', self.metadata(),
*[Column(col, types.Float) for col in columns])
rv = session.query(*table.c).first()
self.assertEqual(rv, tuple([42.0] * len(columns)))
def test_array_join(self):
table = self.create_table(
Column('nested.array_column', types.Array(types.Int8)),
Column('nested.another_array_column', types.Array(types.Int8)))
first_label = table.c['nested.array_column'].label('from_array')
second_not_label = table.c['nested.another_array_column']
query = session.query(first_label, second_not_label)\
.array_join(first_label, second_not_label)
self.assertEqual(
self.compile(query), 'SELECT '
'"nested.array_column" AS from_array, '
'"nested.another_array_column" '
'AS "t1_nested.another_array_column" '
'FROM t1 '
'ARRAY JOIN "nested.array_column" AS from_array, '
'"nested.another_array_column"')
def test_parse_int_types(self):
types_ = [
'Int8', 'UInt8', 'Int16', 'UInt16', 'Int32', 'UInt32', 'Int64',
'UInt64'
]
columns = [chr(i + ord('a')) for i in range(len(types_))]
mock.add(mock.POST,
self.url,
status=200,
body=('\t'.join(columns) + '\n' + '\t'.join(types_) + '\n' +
'\t'.join(['42'] * len(types_)) + '\n'))
table = Table('t1', self.metadata(),
*[Column(col, types.Int) for col in columns])
rv = session.query(*table.c).first()
self.assertEqual(rv, tuple([42] * len(columns)))
def test_count_no_column_specified(self):
table = self.create_table()
self.assertEqual(
self.compile(session.query(func.count()).select_from(table)),
'SELECT count(*) AS count_1 FROM t1')
def test_final(self):
table = self.create_table()
query = session.query(table.c.x).final().group_by(table.c.x)
self.assertEqual(self.compile(query),
'SELECT x AS t1_x FROM t1 FINAL GROUP BY x')
def test_select_escaping(self):
query = session.query(literal('\t'))
self.assertEqual(
self.escaped_compile(query, literal_binds=True),
"SELECT '\t' AS param_1"
)
def test_count_distinct(self):
table = self.create_table()
self.assertEqual(
self.compile(session.query(func.count(func.distinct(table.c.x)))),
'SELECT count(distinct(x)) AS count_1 FROM t1')
def test_joins(self):
t1, t2 = self.create_tables(2)
query = session.query(t1.c.x, t2.c.x) \
.join(
t2,
t1.c.x == t1.c.y,
strictness='any')
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"ANY INNER JOIN t1 ON x = y"
)
query = session.query(t1.c.x, t2.c.x) \
.join(
t2,
t1.c.x == t1.c.y,
type='inner',
strictness='any')
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"ANY INNER JOIN t1 ON x = y"
)
query = session.query(t1.c.x, t2.c.x) \
.join(
t2,
tuple_(t1.c.x, t1.c.y),
type='inner',
strictness='all'
)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"ALL INNER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.join(t2,
tuple_(t1.c.x, t1.c.y),
type='inner',
strictness='all',
distribution='global')
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"GLOBAL ALL INNER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.outerjoin(
t2,
tuple_(t1.c.x, t1.c.y),
type='left outer',
strictness='all',
distribution='global'
)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"GLOBAL ALL LEFT OUTER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.outerjoin(
t2,
tuple_(t1.c.x, t1.c.y),
type='LEFT OUTER',
strictness='ALL',
distribution='GLOBAL'
)
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"GLOBAL ALL LEFT OUTER JOIN t1 USING x, y"
)
query = session.query(t1.c.x, t2.c.x) \
.outerjoin(t2,
tuple_(t1.c.x, t1.c.y),
strictness='ALL',
type='FULL OUTER')
self.assertEqual(
self.compile(query),
"SELECT x AS t0_x, x AS t1_x FROM t0 "
"ALL FULL OUTER JOIN t1 USING x, y"
)
