Python query Examples

Example #1

from select_sqlalchemy import session, Users
from math import ceil

# 常量，每页显示的条数
PAGE_SIZE = 100

total = session.query(Users).count()
print(total)

total_page = ceil(total / PAGE_SIZE)
print(total_page)

CURRENT_PAGE = 10
start = (CURRENT_PAGE - 1) * PAGE_SIZE

res = session.query(Users).offset(start).limit(PAGE_SIZE).all()
for item in res:
    print(item.user_id, item.username)

Example #2

from select_sqlalchemy import session, Users
from sqlalchemy import func

res = session.query(Users, Users.user_id, Users.username, Users.city).order_by(
    Users.user_id.desc()).limit(100).offset(10).all()
for item in res:
    print(item)

Example #3

def get_user():
    objs = session.query(Users).filter(Users.user_id > 485, Users.user_id < 495).all()
    return objs

Example #4

from select_sqlalchemy import session, Users
from sqlalchemy import or_

obj1 = session.query(Users).filter_by(user_id=444).one()
print(obj1)
obj2 = session.query(Users).filter(Users.user_id > 30, Users.user_id < 50).all()
print(obj2)
obj3 = session.query(Users).filter(or_(Users.user_id == 55, Users.user_id == 77)).all()
print(obj3)


def get_user():
    objs = session.query(Users).filter(Users.user_id > 485, Users.user_id < 495).all()
    return objs


for obj in get_user():
    print(obj.username)

# 删除
session.delete(get_user()[0])
session.commit()

print('*' * 50)
for obj in get_user():
    print(obj.username)

Example #5

File: count_groupby.py Project: articuly/operation_practice

from select_sqlalchemy import session, Users
from sqlalchemy import func

res = session.query(Users).count()
print(res)

# TODO count
res = session.query(func.count(1), Users.city).group_by(Users.city).all()
print('count', res)

res = session.query(func.avg(Users.age), Users.city).group_by(Users.city).all()
print('avg', res)

res = session.query(func.sum(Users.age), Users.city).group_by(Users.city).all()
print('sum', res)

res = session.query(func.max(Users.age), Users.city).group_by(Users.city).all()
print('max', res)

Example #6

from select_sqlalchemy import session, Users
from sqlalchemy import or_

obj1 = session.query(Users).filter_by(user_id=20).one()
print(obj1)
# obj2 = session.query(Users).filter(Users.user_id > 30, Users.user_id < 50).all()
# print(obj2)
# obj3 = session.query(Users).filter(or_(Users.user_id == 55, Users.user_id == 77)).all()
# print(obj3)

print(dir(obj1))
print(obj1.username, obj1.realname, obj1.password, obj1.age)

obj1.age = 98
session.commit()

obj = session.query(Users).filter_by(user_id=20).one()
print(obj.username, obj.age)