def test_function_alias(self): User.create(name='jack', email='*****@*****.**') User.create(name='jack', email='*****@*****.**') User.create(name='amy', email='*****@*****.**') query = User.groupby(User.name).having(sql('count') >= 2).select( User.name, fn.count(User.id).alias('count') ) result = query.execute() assert result.tuples()[0][1] == 2 assert result.one().name == 'jack'
def test_groupby(self): for x in range(2): User.create(name='jack', email='*****@*****.**') for x in range(3): User.create(name='tom', email='*****@*****.**') query = User.groupby(User.name).orderby(sql('count')).select( fn.count(User.id).alias('count'), User.name) result = query.execute() assert result.tuples() == ((2, 'jack'), (3, 'tom'))
def test_having(self): for x in range(2): User.create(name='jack', email='*****@*****.**') for x in range(3): User.create(name='tom', email='*****@*****.**') query = User.groupby(User.name).having(sql('count') > 2).select( fn.count(User.id).alias('count'), User.name) result = query.execute() user = result.one() count = result.tuples()[0][0] assert count == 3 and user.name == 'tom'
def test_having(self): for x in range(2): User.create(name='jack', email='*****@*****.**') for x in range(3): User.create(name='tom', email='*****@*****.**') query = User.groupby(User.name).having(sql('count') > 2).select( fn.count(User.id).alias('count'), User.name) results = query.execute() assert results.count == 1 user, func = results.one() assert func.count == 3 and user.name == 'tom'
def test_field_alias(self): User.create(name='jack', email='*****@*****.**') User.create(name='jack', email='*****@*****.**') User.create(name='amy', email='*****@*****.**') query = User.groupby(User.name).having( sql('em').like('*****@*****.**')).orderby(User.id).select( User.name, User.email.alias('em')) assert [(user.name, user.em) for user in query] == [ ('jack', '*****@*****.**'), ('amy', '*****@*****.**') ]
def test_groupby(self): for x in range(2): User.create(name='jack', email='*****@*****.**') for x in range(3): User.create(name='tom', email='*****@*****.**') query = User.groupby(User.name).orderby(sql('count')).select( fn.count(User.id).alias('count'), User.name) result = query.execute() assert result.tuples() == ( (2, 'jack'), (3, 'tom') )
def test_bitwise_operator(self): assert User.create(name='jack', email='*****@*****.**') if db_type != 'sqlite': # sqlite3 dosen't know operator '^' query = User.select(User.id.op('^')(1)) result = query.execute() assert result.tuples()[0][0] == 0 query = User.select(User.id.op('&')(0)) result = query.execute() assert result.tuples()[0][0] == 0 query = User.select(sql('').op('~-')(User.id)) result = query.execute() assert result.tuples()[0][0] == 0
def test_examples(self): User.create(name='jack', email='*****@*****.**') User.create(name='jack', email='*****@*****.**') User.create(name='amy', email='*****@*****.**') # distinct query = User.select(distinct(User.name)) results = query.execute() assert results.one().name == 'jack' assert results.one().name == 'amy' # groupby & orderby query = User.groupby(User.name).orderby(sql('count')).select( User.name, fn.count(User.id).alias('count')) results = query.execute() user, func = results.one() assert user.name == 'amy' and func.count == 1 user, func = results.one() assert user.name == 'jack' and func.count == 2
def test_operators(self): def eq(expr, string, data): string = string.replace('?', database.dbapi.placeholder) sq1 = compiler.sql(expr) sq2 = sql(string, *data) return sq1.literal == sq2.literal and sq1.params == sq2.params assert eq(User.id < 1, 't_user.id < ?', (1,)) assert eq(User.id <= 1, 't_user.id <= ?', (1,)) assert eq(User.id > 1, 't_user.id > ?', (1,)) assert eq(User.id >= 1, 't_user.id >= ?', (1,)) assert eq(User.id == 1, 't_user.id = ?', (1,)) assert eq(User.id != 1, 't_user.id <> ?', (1,)) assert eq(User.id + 1, 't_user.id + ?', (1,)) assert eq(User.id - 1, 't_user.id - ?', (1,)) assert eq(User.id * 1, 't_user.id * ?', (1,)) assert eq(User.id / 1, 't_user.id / ?', (1,)) assert eq(User.id % 1, 't_user.id % ?', (1,)) assert eq((User.id > 1) & (User.id < 4), '(t_user.id > ? and t_user.id < ?)', (1, 4)) assert eq((User.id > 1) | (User.id < 4), '(t_user.id > ? or t_user.id < ?)', (1, 4)) assert eq(1 + User.id, '? + t_user.id', (1,)) assert eq(1 - User.id, '? - t_user.id', (1,)) assert eq(1 * User.id, '? * t_user.id', (1,)) assert eq(1 / User.id, '? / t_user.id', (1,)) assert eq(1 % User.id, '? % t_user.id', (1,)) assert eq(1 & User.id, '(? and t_user.id)', (1,)) assert eq(1 | User.id, '(? or t_user.id)', (1,)) assert eq(User.name.like('%a'), 't_user.name like ?', ('%a',)) assert eq(User.id._in(1, 2), 't_user.id in (?, ?)', (1, 2)) assert eq(User.id.not_in(1, 2), 't_user.id not in (?, ?)', (1, 2)) assert eq(User.id.between(1, 2), 't_user.id between ? and ?', (1, 2)) # custom bitwise ops assert eq(User.id.op('^')(1), 't_user.id ^ ?', (1,)) assert eq(User.id.op('&')(1), 't_user.id & ?', (1,)) assert eq(sql('').op('~')(User.id), ' ~ t_user.id', tuple())
from models import User from skylark import fn, sql # create data.. User.create(name='jack') User.create(name='jack') User.create(name='foo') # select count(user.id) as count_id, user.name from user group by user.name having count_id >= '2' query = User.groupby(User.name).having(sql('count_id') >= 2).select( fn.count(User.id).alias('count_id'), User.name) result = query.execute() for row in result.tuples(): print row[0] # count of id print row[1] # user's name
def test_alias(self): self.create_data(3, table=1) query = User.having(sql('d') >= 3).select(User.id.alias('d')) results = query.execute() user = results.one() assert user.d == 3
def eq(expr, string, data): string = string.replace('?', database.dbapi.placeholder) sq1 = compiler.sql(expr) sq2 = sql(string, *data) return sq1.literal == sq2.literal and sq1.params == sq2.params
from models import User from skylark import fn, sql # create data.. User.create(name='jack') User.create(name='jack') User.create(name='foo') # select count(user.id) as count_id, user.name from user group by user.name having count_id >= '2' query = User.groupby(User.name).having( sql('count_id') >= 2 ).select(fn.count(User.id).alias('count_id'), User.name) result = query.execute() for row in result.tuples(): print row[0] # count of id print row[1] # user's name