• 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 查询过滤器

  • 相关阅读:
    STL源码剖析之_allocate函数
    PAT 1018. Public Bike Management
    PAT 1016. Phone Bills
    PAT 1012. The Best Rank
    PAT 1014. Waiting in Line
    PAT 1026. Table Tennis
    PAT 1017. Queueing at Bank
    STL源码剖析之list的sort函数实现
    吃到鸡蛋好吃,看看是哪只母鸡下的蛋:好用的Sqlite3
    cJSON
  • 原文地址:https://www.cnblogs.com/FRESHMANS/p/8459716.html
Copyright © 2020-2023  润新知