flaskSQLALchemy外键及约束
from flask import Flask,session
from flask_sqlalchemy import SQLAlchemy
import config
app = Flask(__name__)
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表⽰禁⽌⾃动查询
@ute('/')
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.sessionmit()
#查询数据---常规⽅法
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=w) 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=w) 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=w) 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=w) 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=w) 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=w)
def__repr__(self):
return'<content %r>' % t
#电影收藏
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=w)
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=w)
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=w)
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=w)
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=w) 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=w) def__repr__(self):
return'<oplog %r>' % self.id
sql约束条件大于0if__name__ == '__main__':
# 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.sessionmit()
sqlalchemy 查询过滤器
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论