Model:
Class Users(db.model); __tablename__ = "users" id = db.Column(db.Integer,primary_key=True) username = db.Column(db.String(80),unique=True) age = db.Column(db.Integer) email = db.Column(db.String(120),unique=True) def __init__(self,username,age,email): self.username = username self.age = age self.email = email def __repr__(self): return "<Users %r>" % self.usernameCopy the code
insert
@app.route('/insert') def insert_views(): db.drop_all() user = Users('Paris',18,'[email protected]') db.session.add(user) return "Insert OK"Copy the code
FlASK – SQLAlchmey query:
Query based on db.session
db.session.query()
all()
first()
first_or_404()
count()
filter()
filter_by()
limit()
order_by()
group_by()
db.session.query(Users).filter(Users.age>30).all()
db.session.query(Users).filter(or_(Users.age>30, Users.id>5)).all()
db.session.query(Users).filter(Users.age>30, Users.age<50).all()
db.session.query(Users).filter(Users.id==5).all()
db.session.query(Users).filter(Users.username.like(“%w%”)).all()
db.session.query(Users).filter(Users.id.in_([1, 2, 3])).all()
db.session.query(Users).filter_by(id=5).first()
db.session.query(Users).limit(5).all()
db.session.query(Users).limit(5).offset(2).all()
db.session.query(Users).order_by(“id desc”).all()
db.session.query(Users).order_by(“id desc, age asc”).all()
Users.query.filter(Users.id>3).all()
@app.route('/query') def query_views(): # print test query (db) session) query (Users) print (db) session) query (Users. The username, Users. Email)) Print (db.session.query(Users,Course)) print(db.session.query(Users,Course)) Users = db.session.query(users).all() for u in users: print(u.server name,u.age, u.mail) # first() : To get the first result the query the user = db. The session. The query (Users). The first () print (user. The username, user. The age, the user email) course = Db.session.query (Course).first() print(Course Db.session.query (Users).filter(users.age >30).all() print(Users) # Db.session.query (Users).filter(users.age >30, users.id > 5).all() print(Users) # Db.session.query (Users).filter(or_(users.age >30, users.id > 5)).all() print(Users) # Db.session.query (Users).filter(users.email.like ('%w%')).all() print(Users) # Db.session.query (Users).filter(users.id.in_ ([1,2,3])).all() print(Users) # db.session.query(Users).limit(3).all() users = db.session.query(Users).limit(3).offset(1).all() # Users = db.session.query(Users). Order_by ('id desc,age asc') print(Users) # Users = db.session.query(users).group_by('id').all() print(users) # Users = users.query.filter (users.id >3). All () print(users) return "query OK"Copy the code
FLASK-SQLAlchemy deleted and modified
Delete:
1. Query
user = db.session.query(Users).filter_by(id=5).first()
2. Delete information according to the deletion method provided
db.session.delete(user)
@app.route('/delete_user') def delete_user(): Filter_by (id=5).first() # delete db.session.delete(user) return 'delete OK'Copy the code
Modification:
1. Check
user = Users.query.filter_by(id=1).first()
2. Change
User. username = “Paris”
3. Save
db.session.add(user)
@app.route('/update_user') def update_user(): Username = users.query.filter_by (id=1).first( Db.session. add(user) return "Update OK"Copy the code
FLASK-SQLAlchemy – Relational mapping
A couple more:
Added in “many” entities
ForeignKey class name = db.column (db.Integer, db.foreignkey (primary table. The primary key))
In the entity of “one” the positive backreference relationship
Attribute name = db.relationship(” corresponding “multi-entity class name “, backref=” attribute name “, lazy=”dynamic”)
class Course(db.Model): __tablename__ = "course" ID = db.Column(db.Integer,primary_key=True) cname = db.Column(db.string (30)) # # return the list of teachers associated with the current course #backref: # Define the reverse relationship, essentially adding a # course attribute to the teacher entity. This property can replace course_id to access the # Course model. Teacher =db. Relationship ('Teacher',backref='course') def __init__(self,cname): self.cname = cname class Teacher(db.Model): __tablename__='teacher' id = db.Column(db.Integer,primary_key=True) tname = db.Column(db.String(30)) tage = Db.column (db.integer) # add a Column: Course_id, ForeignKey Column, primary key Column(id) to reference the independent key table (course) course_id = db.column (db.integer, db.foreignkey ('course.id')) def __init__(self,tname,tage): self.tname = tname self.tage = tage def __repr__(self): return "<Teacher %r>" % self.tnameCopy the code