def test_count(self):
self.create_data(4)
assert User.count() == 4
query = (Post & User).select(
fn.count(User.id).alias('count_user_id'),
fn.count(Post.post_id).alias('count_post_id'))
results = query.execute()
assert results.count == 1
func = results.one()
assert func.count_user_id == 4
assert func.count_post_id == 4
def test_having(self):
query = self.models.groupby(
User.name).having(fn.count(User.id) >= 1).select()
results = query.execute()
assert results.count == 4
query = self.models.groupby(
User.name).having(fn.count(User.id) > 4).select()
results = query.execute()
assert results.count == 0
query = self.models.groupby(
User.name).having(fn.count(User.id) == 4).select()
results = query.execute()
assert results.count == 4 # 16 / 4 =4
def test_having(self):
query = self.models.groupby(
User.name).having(fn.count(User.id) >= 1).select()
results = query.execute()
assert results.count == 4
query = self.models.groupby(
User.name).having(fn.count(User.id) > 4).select()
results = query.execute()
assert results.count == 0
query = self.models.groupby(
User.name).having(fn.count(User.id) == 4).select()
results = query.execute()
assert results.count == 4 # 16 / 4 =4
def test_distinct(self):
assert User.create(name='jack', email='*****@*****.**')
assert User.create(name='jack', email='*****@*****.**')
assert User.create(name='wangchao', email='*****@*****.**')
assert User.create(name='hit9', email='*****@*****.**')
query = User.select(fn.count(distinct(User.name)))
result = query.execute()
count = result.tuples()[0][0]
assert count == 3
query = User.orderby(User.id).select(distinct(User.email))
result = query.execute()
assert result.tuples() == (
('*****@*****.**',), ('*****@*****.**',), ('*****@*****.**', )
)
emails = [user.email for user in result.all()]
assert len(emails) == len(set(emails))
query = User.orderby(User.id).select(distinct(User.name, User.email))
try:
result = query.execute()
except Exception: # should raise an error
pass
else:
raise Exception
query = User.orderby(User.id).select(distinct(User.name), User.email)
result = query.execute()
assert result.count == 4
def test_distinct(self):
assert User.create(name='jack', email='*****@*****.**')
assert User.create(name='jack', email='*****@*****.**')
assert User.create(name='wangchao', email='*****@*****.**')
assert User.create(name='hit9', email='*****@*****.**')
query = User.select(fn.count(distinct(User.name)))
result = query.execute()
count = result.tuples()[0][0]
assert count == 3
query = User.orderby(User.id).select(distinct(User.email))
result = query.execute()
assert result.tuples() == (
('*****@*****.**',), ('*****@*****.**',), ('*****@*****.**', )
)
emails = [user.email for user in result.all()]
assert len(emails) == len(set(emails))
query = User.orderby(User.id).select(distinct(User.name, User.email))
try:
result = query.execute()
except Exception: # should raise an error
pass
else:
raise Exception
query = User.orderby(User.id).select(distinct(User.name), User.email)
result = query.execute()
assert result.count == 4
def page(page_number):
if page_number <= 0:
abort(404)
n = 9
query = Post.where(published=True).orderby(
Post.datetime, desc=True).limit(n, offset=n * (page_number - 1)
).select()
results = query.execute()
count = results.count
if count < 0: # no posts
abort(404)
query = Post.where(published=True).select(fn.count(Post.id))
result = query.execute()
func = result.one()
total_count = func.count
is_first_page = True if page_number == 1 else False
is_last_page = True if n * page_number >= total_count else False
posts = tuple(results.all())
page = dict(
number=page_number,
posts=posts,
first=is_first_page,
last=is_last_page
)
return render_public('page.html', page=page)
def test_count(self):
self.create_data(4)
assert User.count() == 4
assert Post.count() == 4
query = User.select(fn.count(User.id))
result = query.execute()
assert result.count == 1
assert result.tuples()[0][0] == 4
def test_count(self):
self.create_data(4)
assert User.count() == 4
assert Post.count() == 4
query = User.select(fn.count(User.id))
result = query.execute()
assert result.count == 1
assert result.tuples()[0][0] == 4
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_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_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)
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_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).select(fn.count(User.id), User.name)
for user, func in query:
if user.name == 'jack':
assert func.count == 2
elif user.name == 'tom':
assert func.count == 3
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_func_only(self):
self.create_data(3, table=1)
query = User.select(fn.count(User.id))
results = query.execute()
func = results.one()
assert func.count == 3
assert results.count == 1
query = User.at(2).select(fn.ucase(User.name).alias('uname'))
results = query.execute()
func = results.one()
assert func.uname == 'NAME2'
query = User.at(3).select(fn.concat(User.name, ' + ', 'hello'))
results = query.execute()
func = results.one()
assert func.concat == 'name3 + hello'
def test_distinct(self):
assert User.create(name='jack', email='*****@*****.**')
assert User.create(name='jack', email='*****@*****.**')
assert User.create(name='wangchao', email='*****@*****.**')
assert User.create(name='hit9', email='*****@*****.**')
query = User.select(fn.count(distinct(User.name)))
results = query.execute()
func = results.one()
assert func.count == 3
query = User.orderby(User.id).select(distinct(User.email))
results = query.execute()
assert tuple(results.tuples()) == (
('*****@*****.**', ), ('*****@*****.**', ), ('*****@*****.**', )
)
query = User.orderby(User.id).select(distinct(User.name), User.id)
results = query.execute()
assert tuple(results.dicts()) == (
{'name': 'jack', 'id': 1},
{'name': 'jack', 'id': 2},
{'name': 'wangchao', 'id': 3},
{'name': 'hit9', 'id': 4},
)
from models import User
from skylark import fn
query = User.at(1).select(User.name.alias('un'))
result = query.execute()
user = result.one()
user.un # retrieve `name` by user.un
query = User.select(fn.count(User.name))
result = query.execute()
result.tuples()[0][0] # retrieve count result by result.tuples()
from skylark import fn, distinct from models import User # select count(distinct(user.name)) from user query = User.select(fn.count(distinct(User.name))) result = query.execute() return result.tuples()[0][0]
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_fullname(self):
assert fn.max(User.id).fullname == 'max(user.id)'
assert fn.count(User.id).fullname == 'count(user.id)'
assert fn.concat(User.name, 'hello') == "concat(user.name, 'hello')"
def test_name(self):
assert fn.count(User.id).name == 'count'
assert fn.concat(User.name, 'hello').name == 'concat'
assert fn.ucase(User.name).alias(
'upper_cased_name').name == 'upper_cased_name'
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
from skylark import fn, distinct from models import User # select count(distinct(user.name)) from user query = User.select(fn.count(distinct(User.name))) result = query.execute() func = result.one() return func.count