flask SQLALchemy外键及约束
from flask import Flask,session from flask_sqlalchemy import SQLAlchemy import config app = Flask(__name__) app.config.from_object(config) db = SQLAlchemy(app) class User(db.Model): __tablename__ = 'user' id = db.Column(db.Integer,primary_key=True,autoincrement=True) username = db.Column(db.String(100),nullable=False) class Article(db.Model): __tablename__ = 'article' id = db.Column(db.Integer,primary_key=True,autoincrement=True) title = db.Column(db.String(100),nullable=False) content = db.Column(db.Text,nullable=False)
# 关联表,这里要与相关联的表的类型一直, user.id 表示关联到user表下的id字段 author_id = db.Column(db.Integer,db.ForeignKey('user.id'))
#给这个article模型添加一个author属性(关系表),User为要连接的表,backref为定义反向引用 author = db.relationship('User',backref=db.backref('articles'),lazy='dynamic') #lazy表示禁止自动查询 db.create_all() @app.route('/') def index():
#添加数据 user1 = User(username="test_flask2") db.session.add(user1) article = Article(title='eeee',content='cfffff',author_id=1) #注意,这里的author_id必须存在,即user表下的id字段的内容必须存在 db.session.add(article) db.session.commit()
#查询数据---常规方法 article = Article.query.filter(Article.title=="eeee").first() author_id = article.author_id user = User.query.filter(User.id==author_id) #获取user表的所有数据 print (user.username) #获取User表下的username字段的内容
#通过sqlalchemy实现查询数据
article = Article.query.filter(Article.title=="eeee").first()
print (article.author.username) #打印username字段的内容,
user = User.query.filter(User.username=="test_flask3").first()
print (user.articles) #查询所有的文章(结果为列表),这里需要注意的是这里的articles要与 article表下author字段里的backref设定的articles 字符串相同
return 'Hello World!' if __name__ == '__main__': app.run(host='192.168.132.130',debug=True)
示例:
#!/usr/bin/env python #-*-coding:utf-8-*- import datetime from movie_project import db #用户 class User(db.Model): __tablename__ ="user" id = db.Column(db.Integer,primary_key=True) name = db.Column(db.String(100),nullable=False,unique=True) #unique代表不能重复,唯一的 pwd = db.Column(db.String(100),nullable=False) email = db.Column(db.String(64),nullable=False,unique=True) phone = db.Column(db.String(11),nullable=False,unique=True) info = db.Column(db.Text) face = db.Column(db.String(100)) #头像 addtime = db.Column(db.DateTime,index=True,default=datetime.datetime.now) uuid = db.Column(db.String(255)) userlogs = db.relationship('UserLog',backref='user') #外键关系关联 comments = db.relationship('Comment',backref='user') movicols = db.relationship('Moviecol', backref='user') def __repr__(self): #定义返回的类型 return '<user %r>' % self.name def check_pwd(self,pwd): from werkzeug.security import check_password_hash return check_password_hash(self.pwd,pwd) #登录日志 class UserLog(db.Model): __tablename__ = 'userlog' id = db.Column(db.Integer,primary_key=True) user_id = db.Column(db.Integer,db.ForeignKey('user.id')) ip = db.Column(db.String(30)) addtime = db.Column(db.DateTime,index=True,default=datetime.datetime.now) def __repr__(self): return '<userlog %r>' % self.id #标签数据模型 class Tag(db.Model): __tablename__ = 'tag' id = db.Column(db.Integer,primary_key=True) name = db.Column(db.String(100),unique=True) addtime = db.Column(db.DateTime,index=True,default=datetime.datetime.now) movies = db.relationship('Movie',backref='tag') def __repr__(self): return '<tag %r>' % self.name #电影模型 class Movie(db.Model): __tablename__ = 'movie' id = db.Column(db.Integer,primary_key=True) title = db.Column(db.String(20),unique=True) url = db.Column(db.String(255),unique=True) #地址 info = db.Column(db.Text) #简介 logo = db.Column(db.String(255),unique=True) #封面 star = db.Column(db.SmallInteger) #星级 playnum = db.Column(db.BigInteger) #播放量 commentnum = db.Column(db.BigInteger) #评论量 tag_id = db.Column(db.Integer,db.ForeignKey('tag.id')) area = db.Column(db.String(255)) #上映地区 replease_time = db.Column(db.Date) #上映时间 length = db.Column(db.String(100)) addtime = db.Column(db.DateTime,index=True,default=datetime.datetime.now) comments = db.relationship('Comment',backref='movie') moviecols = db.relationship('Moviecol', backref='movie') def __repr__(self): return '<movie %r>' % self.title class Preview(db.Model): __tablename__ = 'preview' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(20), unique=True) logo = db.Column(db.String(255), unique=True) addtime = db.Column(db.DateTime, index=True, default=datetime.datetime.now) def __repr__(self): return '<preview %r>' % self.title #评论 class Comment(db.Model): id = db.Column(db.Integer, primary_key=True) content = db.Column(db.Text) movie_id = db.Column(db.Integer,db.ForeignKey('movie.id')) user_id = db.Column(db.Integer,db.ForeignKey('user.id')) addtime = db.Column(db.DateTime,index=True,default=datetime.datetime.now) def __repr__(self): return '<content %r>' % self.content #电影收藏 class Moviecol(db.Model): __tablename__ = 'moviecol' id = db.Column(db.Integer, primary_key=True) movie_id = db.Column(db.Integer, db.ForeignKey('movie.id')) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) addtime = db.Column(db.DateTime, index=True, default=datetime.datetime.now) def __repr__(self): return '<Moviecol %r>' % self.id #权限及角色数据模型 class Auth(db.Model): __tablename__ = 'auth' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20),unique=True) #权限名称 url = db.Column(db.String(255),unique=True) addtime = db.Column(db.DateTime, index=True, default=datetime.datetime.now) def __repr__(self): return '<auth %r>' % self.name #角色模型 class Role(db.Model): __tablename__ = 'role' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20), unique=True) # 权限名称 auths = db.Column(db.String(600)) addtime = db.Column(db.DateTime, index=True, default=datetime.datetime.now) def __repr__(self): return '<Role %r>' % self.name #管理员数据模型 class Admin(db.Model): __tablename= 'admin' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), nullable=False, unique=True) # unique代表不能重复,唯一的 pwd = db.Column(db.String(100), nullable=False) is_super = db.Column(db.SmallInteger) #是否为超级管理员 role_id = db.Column(db.Integer,db.ForeignKey('role.id')) addtime = db.Column(db.DateTime, index=True, default=datetime.datetime.now) adminlogs = db.relationship('Adminlog',backref='admin') adminoption = db.relationship('Oplogs', backref='admin') def __repr__(self): return '<Admin %r>' % self.name def check_pwd(self,pwd): from werkzeug.security import check_password_hash return check_password_hash(self.pwd,pwd) #管理员登录日志 class Adminlog(db.Model): __tablename__ = 'adminlog' id = db.Column(db.Integer, primary_key=True) admin_id = db.Column(db.Integer, db.ForeignKey('admin.id')) ip = db.Column(db.String(30)) addtime = db.Column(db.DateTime, index=True, default=datetime.datetime.now) def __repr__(self): return '<Admin %r>' % self.name #操作日志 class Oplogs(db.Model): __tablename__ = 'oplogs' id = db.Column(db.Integer, primary_key=True) admin_id = db.Column(db.Integer, db.ForeignKey('admin.id')) ip = db.Column(db.String(30)) reason = db.Column(db.String(600)) #操作原因 addtime = db.Column(db.DateTime, index=True, default=datetime.datetime.now) def __repr__(self): return '<oplog %r>' % self.id if __name__ == '__main__': db.create_all() # from werkzeug.security import generate_password_hash # role = Role( # name="超级管理员", # auths = '' # ) # admin = Admin( # name="test", # pwd=generate_password_hash("test"), # is_super=0, # role_id=1 # ) # db.session.add(role) # db.session.add(admin) # db.session.commit()
sqlalchemy 查询过滤器