users = User.query.all()
users = User.query.first()
users = User.query.get(1)
多个主键
users = User.query.get((1,5))
users = User.query.get({"id":1,"project_id":3})
只能处理相等
admin = User.query.filter_by(username='admin').first()
User.query.filter(User.email.endswith("@qq.com")).all()
from sqlalchemy.sql.operators import ColumnOperators
支持的字段操作:ColumnOperators
:/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/operators.py.ColumnOperators
支持的操作
User.query.order_by(User.username.desc()).all()
User.query.order_by(User.username.desc()).limit(1).all()
使用first_or_404
没找到就用abort()
和异常处理机制配合
db.session.delete(me)
db.session.commit()
user = User.query.get(1)
user.username = "abc"
db.session.add(user)
db.session.commit()
例子:
多个学生 一个班级
class Project(db.Model):
# __tablename__ = 'project'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
modules = db.relationship('Module', backref='module_project')
class Module(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
project_id = db.Column(db.Integer, db.ForeignKey('project.id'))
class Project(db.Model):
# __tablename__ = 'project'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
modules = db.relationship('Module', back_populates='module_project') # 数据库中不会有新字段
class Module(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80))
project_id = db.Column(db.Integer, db.ForeignKey('project.id'))
module_project = db.relationship('Project', back_populates='modules')
对应关系
例子:
一个人一个身份证号
class Project(db.Model):
modules = db.relationship("Module", back_populates='project', uselist=False)
class Module(db.Model):
project = db.relationship("Module", back_populates='modules', uselist=False)
例子:
1个学生 多门课程 1门课程 多个学生
xuanke = db.Table('xuanke',
db.Column('user_id'),
db.Integer,
db.ForeignKey('user.id'),
db.Column('subject_id'),
db.Integer,
db.ForeignKey('subject.id')
)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
subject = db.relationship('Subject', secondary=xuanke, backref=db.backref('users', lazy='dynamic'))
class Subject(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
插入数据
@app.route('/insert')
def insert():
user1 = User(name='张三')
user2 = User(name='李四')
user3 = User(name='王五')
subject1 = Subject(name='自动化测试')
subject2 = Subject(name='测试开发')
subject3 = Subject(name='Java')
user1.subject.append(subject1)
user1.subject.append(subject2)
db.session.add_all([user1, user2, user3, subject1, subject2, subject3])
db.session.commit()
return 'OK'
查找数据
@app.route('/select')
def select():
user = User.query.get(1)
s = user.subject
print(s)
return 'OK'
class Follower(db.Model):
# 粉丝ID
fensi_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
# 关注者ID
guanzhuzhe_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), nullable=False)
sujects = db.relationship('Xuanke', back_populates='student')
fensis = db.relationship('Follower', foreign_keys=[Follower.guanzhuzhe_id], backref='guanzhuzhe')
guanzhuzhes = db.relationship('Follower', foreign_keys=[Follower.fensi_id], backref='fensi')