Flask数据库操作 #

from app import db
from models import User

user = User(username='zhangsan', email='zhangsan@example.com')
db.session.add(user)
db.session.commit()

users = [
    User(username='user1', email='user1@example.com'),
    User(username='user2', email='user2@example.com'),
    User(username='user3', email='user3@example.com')
]
db.session.add_all(users)
db.session.commit()

# 查询所有
users = User.query.all()

# 根据主键查询
user = User.query.get(1)
user = User.query.get_or_404(1)

# 根据条件查询
user = User.query.filter_by(username='zhangsan').first()
users = User.query.filter(User.username == 'zhangsan').all()

# 等于
User.query.filter(User.username == 'zhangsan')

# 不等于
User.query.filter(User.username != 'zhangsan')

# LIKE
User.query.filter(User.username.like('%zhang%'))

# IN
User.query.filter(User.username.in_(['zhangsan', 'lisi']))

# IS NULL
User.query.filter(User.email == None)
User.query.filter(User.email.is_(None))

# IS NOT NULL
User.query.filter(User.email != None)
User.query.filter(User.email.isnot(None))

# 范围
User.query.filter(User.age.between(18, 30))

from sqlalchemy import and_, or_, not_

# AND
User.query.filter(and_(User.age > 18, User.active == True))
User.query.filter(User.age > 18, User.active == True)

# OR
User.query.filter(or_(User.age < 18, User.age > 60))

# NOT
User.query.filter(not_(User.active))

# 排序
User.query.order_by(User.created_at.desc())
User.query.order_by(User.age.asc(), User.username)

# 限制数量
User.query.limit(10).all()
User.query.offset(10).limit(10).all()

from sqlalchemy import func

# 计数
count = User.query.count()
count = db.session.query(func.count(User.id)).scalar()

# 求和
total = db.session.query(func.sum(User.age)).scalar()

# 平均值
avg = db.session.query(func.avg(User.age)).scalar()

# 最大值
max_age = db.session.query(func.max(User.age)).scalar()

# 最小值
min_age = db.session.query(func.min(User.age)).scalar()

user = User.query.get(1)
user.username = 'newname'
db.session.commit()

User.query.filter(User.active == False).update({'active': True})
db.session.commit()

user = User.query.get(1)
db.session.delete(user)
db.session.commit()

User.query.filter(User.active == False).delete()
db.session.commit()

page = request.args.get('page', 1, type=int)
per_page = request.args.get('per_page', 10, type=int)

pagination = User.query.paginate(page=page, per_page=per_page)
users = pagination.items

# 分页属性
pagination.total      # 总记录数
pagination.pages      # 总页数
pagination.page       # 当前页
pagination.per_page   # 每页数量
pagination.has_prev   # 是否有上一页
pagination.has_next   # 是否有下一页
pagination.prev_num   # 上一页页码
pagination.next_num   # 下一页页码

{% for user in users %}
    <p>{{ user.username }}</p>
{% endfor %}

{% if pagination.has_prev %}
    <a href="{{ url_for('users', page=pagination.prev_num) }}">上一页</a>
{% endif %}

<span>{{ pagination.page }} / {{ pagination.pages }}</span>

{% if pagination.has_next %}
    <a href="{{ url_for('users', page=pagination.next_num) }}">下一页</a>
{% endif %}