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_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_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_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_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_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_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_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())
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
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
