flask_sqlalchemy执行原生sql flask sqlalchemy query

转载

mob64ca13f27a4c 2024-03-26 14:40:50

文章标签 SQLAlchemy操作 User sqlalchemy flask 文章分类 机器学习人工智能

SQLAlchemy操作

新增

user = User(mobile="15270496981", name="xlz")
# 生成sql语句
db.session.add(user)
# 执行sql语句
db.session.commit()

对于批量添加也可使用如下语法：

user1 = User(mobile="15270496981", name="xlz1")
user2 = User(mobile="15270496982", name="xlz2")
# 生成sql语句
db.session.add_all([user1, user2])
# 执行sql语句
db.session.commit()

查询

all()：查询所有，返回列表

User.query.all()

first()：查询第一个，返回对象

User.query.first()

get()：根据主键ID获取对象，若主键不存在返回None

User.query.get(2)

其它

db.session.query(User).all()
db.session.query(User).first()
db.session.query(User).get(2)

过虑

filter_by

user1 = User.query.filter_by(mobile="18516952650").first()
# and关系
user2 = User.query.filter_by(mobile="18516952650", id=1).first()
print(user1)
print(user2)

filter

User.query.filter(User.mobile=='18516952650').first()

逻辑或：or_

导入

from sqlalchemy import or_

使用

# 逻辑或
users = User.query.filter(or_(User.mobile=="18516952650", User.mobile.startswith("185"))).all()
    for user in users:
        print(user)

逻辑与：and_

导入

from sqlalchemy import and_

使用

# 逻辑与
users = User.query.filter(and_(User.mobile.startswith("182"), User.name.startswith("我是"))).all()
    for user in users:
        print(user.name, user.mobile)

逻辑非：not_

导入

from sqlalchemy import not_

使用

# 逻辑非
users = User.query.filter(not_(User.mobile.startswith("182"))).all()
for user in users:
    print(user.name, user.mobile)

offset：起始位置

# offset 偏移，起始位置
users = User.query.offset(3).all()
for user in users:
    print(user.id, user.mobile)

limit：获取限制数据

# limit
users = User.query.limit(3).all()
for user in users:
    print(user.name, user.mobile)

order_by

# order_by 排序
 User.query.order_by(User.id).all() # 正序
 User.query.order_by(User.id.desc()).all() # 倒序

复合查询

User.query.filter(User.name.startswith('13')).order_by(User.id.desc()).offset(2).limit(5).all()

query = User.query.filter(User.name.startswith('13'))
query = query.order_by(User.id.desc())
query = query.offset(2).limit(5)
ret = query.all()

优化查询

user = User.query.filter_by(id=1).first()  # 查询所有字段
select user_id, mobile......

select * from   # 程序不要使用
select user_id, mobile,.... # 查询指定字段

from sqlalchemy.orm import load_only
User.query.options(load_only(User.name, User.mobile)).filter_by(id=1).first() # 查询特定字段

聚合查询

from sqlalchemy import func

db.session.query(Relation.user_id, func.count(Relation.target_user_id)).filter(Relation.relation == Relation.RELATION.FOLLOW).group_by(Relation.user_id).all()

综合练习

SQL语句：

# 1.查询手机号码为18516952650的用户
sql: select * from user_basic where mobile='18516952650').first();
# 2.查询手机号以13开始的, 并且发布文章的数量大于10篇的用户
sql: select * from user_basic where mobile like '13%' and article_count > 10;
# 3.查询手机号以13结尾的, 或者发布文章的数量大于10篇的用户
sql: select * from user_basic where mobile like '%13' or article_count > 10;
# 4.查询手机号不是以13结尾的用户
sql: select * from user_basic where mobile not like '%13';
# 5.查询手机号不是以13结尾的用户,获取前面的第3条开始的5条数据
sql: select * from user_basic where mobile not like '%13' limit 3, 5;
# 6.查询手机号不是以13结尾的用户,以发布文章数量排序(正/降),获取前面的第3条开始的5条数据
sql: select user_id, user_name from user_basic where mobile not like '%13' order by article_count limit 3, 5;
# 7.查询关系表,以user_id分组, 查出每个用户当前的关注数量.
sql: select user_id, count(target_user_id) from user_relation group by user_id;

from datetime import datetime
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import and_, or_, not_, func
from sqlalchemy.orm import load_only

app = Flask(__name__)

# 1.配置mysql
class Config(object):
    # 配置数据库
    SQLALCHEMY_DATABASE_URI = 'mysql://root:mysql@127.0.0.1:3306/toutiao'
    # 是否追踪数据的改变，设置为False即可，后面会废弃。
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    # 是否显示sqlalchemy执行的sql语句
    # SQLALCHEMY_ECHO = True

app.config.from_object(Config)

# 2.创建db对象
db = SQLAlchemy(app)

# 3.定义模型类
class User(db.Model):
    # 指定映射数据库中的哪张表
    __tablename__ = "user_basic"

    # 此类是业务自己用的，跟SQLAlchemy没关系
    class STATUS:
        ENABLE = 1
        DISABLE = 0

    #  db.Column的第一个参数映射对应的字段
    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')
    password = db.Column(db.String, doc='密码')
    name = db.Column('user_name', db.String, doc='昵称')
    profile_photo = db.Column(db.String, doc='头像')
    last_login = db.Column(db.DateTime, doc='最后登录时间')
    is_media = db.Column(db.Boolean, default=False, doc='是否是自媒体')
    is_verified = db.Column(db.Boolean, default=False, doc='是否实名认证')
    introduction = db.Column(db.String, doc='简介')
    certificate = db.Column(db.String, doc='认证')
    article_count = db.Column(db.Integer, default=0, doc='发帖数')
    following_count = db.Column(db.Integer, default=0, doc='关注的人数')
    fans_count = db.Column(db.Integer, default=0, doc='被关注的人数（粉丝数）')
    like_count = db.Column(db.Integer, default=0, doc='累计点赞人数')
    read_count = db.Column(db.Integer, default=0, doc='累计阅读人数')
    account = db.Column(db.String, doc='账号')
    email = db.Column(db.String, doc='邮箱')
    status = db.Column(db.Integer, default=1, doc='状态，是否可用')

class Relation(db.Model):
    """
    用户关系表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        # 代表关注
        FOLLOW = 1
        # 拉黑
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID')
    user_id = db.Column(db.Integer, doc='用户ID')
    target_user_id = db.Column(db.Integer, doc='目标用户ID')
    relation = db.Column(db.Integer, doc='关系')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')

if __name__ == '__main__':
    # 1.查询手机号码为18516952650的用户
    # 方式一
    # user = User.query.options(load_only(User.id, User.name, User.mobile)).filter(User.mobile == "18516952650").first()
    # print(user)
    # 方式二
    # user = User.query.filter_by(mobile="18516952650").first()
    # print(user)
    # 2.查询手机号以13开始的, 并且发布文章的数量大于10篇的用户
    # users = User.query.filter(and_(User.mobile.startswith("13"), User.article_count > 10)).all()
    # for user in users:
    #     print(user)
    # 3.查询手机号以13结尾的, 或者发布文章的数量大于10篇的用户
    # users = User.query.filter(or_(User.mobile.endswith("13"), User.article_count > 10)).all()
    # for user in users:
    #     print(user)
    # 4.查询手机号不是以13结尾的用户
    # users = User.query.filter(not_(User.mobile.endswith("13"))).all()
    # for user in users:
    #     print(user)
    # 5.查询手机号不是以13结尾的用户, 获取前面的第3条开始的5条数据
    # users = User.query.filter(not_(User.mobile.endswith("13"))).offset(2).limit(5)
    # for user in users:
    #     print(user.id, user.name, user.mobile)
    # 6.查询手机号不是以13结尾的用户, 以发布文章数量排序(正 / 降), 获取前面的第3条开始的5条数据
    # users = User.query.filter(not_(User.mobile.endswith("13"))).order_by(User.article_count).offset(2).limit(5)
    # for user in users:
    #     print(user.id, user.name, user.mobile, user.article_count)
    # 7.查询关系表, 以user_id分组, 查出每个用户当前的关注数量.
    relations = db.session.query(Relation.user_id, func.count(Relation.target_user_id)).\
        filter(Relation.relation==1).group_by(Relation.user_id).all()
    for relation in relations:
        print("id:{}, conut:{}".format(relation[0], relation[1]))

关联查询

原生写法：

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    # 指定映射数据库中的哪张表
    __tablename__ = "user_basic"

    # 此类是业务自己用的，跟SQLAlchemy没关系
    class STATUS:
        ENABLE = 1
        DISABLE = 0

    #  db.Column的第一个参数映射对应的字段
    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')
     # 使用property属性，可以将方法当属性使用
    @property
    def followings(self):
        relations = Relation.query.filter(Relation.user_id == self.id).all()
        return relations

class Relation(db.Model):
    """
    用户关系表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        # 代表关注
        FOLLOW = 1
        # 拉黑
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID')
    user_id = db.Column(db.Integer, doc='用户ID')
    target_user_id = db.Column(db.Integer, doc='目标用户ID')
    relation = db.Column(db.Integer, doc='关系')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')

使用查询：

from flask import Flask
from flask_foreign_key.config import Config
from flask_foreign_key.models import db, User, Relation

app = Flask(__name__)
app.config.from_object(Config)
db.init_app(app)

if __name__ == '__main__':
    with app.app_context():
        # 获取用户id为1的用户对象
        user = User.query.get(1)
        # 获取关注列表
        relations = user.followings
        print(relations)
        for relation in relations:
            print(relation.id, relation.user_id,relation.target_user_id, relation.relation)

使用ForeignKey

在模型中使用db.relationship创建关系属性

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    # 指定映射数据库中的哪张表
    __tablename__ = "user_basic"

    # 此类是业务自己用的，跟SQLAlchemy没关系
    class STATUS:
        ENABLE = 1
        DISABLE = 0

    #  db.Column的第一个参数映射对应的字段
    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')
    # 1.使用db.relationship创建关系属性
    # 此属性只是为了方便查询，在数据库表中并没有对应的字段。
    # 参数: 关联的类名称(字符串的形式)
    followings = db.relationship('Relation', foreign_keys='Relation.user_id')

class Relation(db.Model):
    """
    用户关系表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        # 代表关注
        FOLLOW = 1
        # 拉黑
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID')
    # 2.1.使用类.属性
    # user_id = db.Column(db.Integer, db.ForeignKey(User.id), doc='用户ID')
    # 2.2.使用表.字段名
    user_id = db.Column(db.Integer, db.ForeignKey('user_basic.user_id'), doc='用户ID')
    target_user_id = db.Column(db.Integer, doc='目标用户ID')
    relation = db.Column(db.Integer, doc='关系')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')

使用外键查询

from flask import Flask

from flask_foreign_key.config import Config
from flask_foreign_key.models import db, User

app = Flask(__name__)
app.config.from_object(Config)

db.init_app(app)

if __name__ == '__main__':
    with app.app_context():
        # 获取用户id为1的用户对象
        user = User.query.get(1)
        # 获取关注列表
        print(user.followings)
        for relation in relations:
            print(relation.id, relation.user_id,relation.target_user_id, relation.relation)

使用primaryjoin

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    # 指定映射数据库中的哪张表
    __tablename__ = "user_basic"

    # 此类是业务自己用的，跟SQLAlchemy没关系
    class STATUS:
        ENABLE = 1
        DISABLE = 0

    #  db.Column的第一个参数映射对应的字段
    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')

    # 使用primaryjoin
    fans = db.relationship("Relation", primaryjoin="Relation.target_user_id==foreign(User.id)")

class Relation(db.Model):
    """
    用户关系表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        # 代表关注
        FOLLOW = 1
        # 拉黑
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID')
    user_id = db.Column(db.Integer, doc='用户ID')
    target_user_id = db.Column(db.Integer, doc='目标用户ID')
    relation = db.Column(db.Integer, doc='关系')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')

from flask import Flask

from flask_foreign_key.config import Config
from flask_foreign_key.models import db, User

app = Flask(__name__)
app.config.from_object(Config)

db.init_app(app)

if __name__ == '__main__':
    with app.app_context():
        # 获取用户id为1的用户对象
        user = User.query.get(1)
        # 获取粉丝列表
        fans = user.fans
        print(fans)

使用primaryjoin，代码看起来更加直观，所以建议使用primaryjoin

join查询的使用

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    # 指定映射数据库中的哪张表
    __tablename__ = "user_basic"

    # 此类是业务自己用的，跟SQLAlchemy没关系
    class STATUS:
        ENABLE = 1
        DISABLE = 0

    #  db.Column的第一个参数映射对应的字段
    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')

class Relation(db.Model):
    """
    用户关系表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        # 代表关注
        FOLLOW = 1
        # 拉黑
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID')
    user_id = db.Column(db.Integer, doc='用户ID')
    target_user_id = db.Column(db.Integer, doc='目标用户ID')
     # 方法四：指定字段
    target_user = db.relationship('User', primaryjoin='Relation.target_user_id==foreign(User.id)', uselist=False)
    relation = db.Column(db.Integer, doc='关系')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')

join查询的使用：

from flask import Flask

from flask_foreign_key.config import Config
from flask_foreign_key.models import db, User, Relation
from sqlalchemy.orm import load_only, contains_eager

app = Flask(__name__)
app.config.from_object(Config)

db.init_app(app)

if __name__ == '__main__':
    with app.app_context():
        fans = Relation.query.join(Relation.target_user).
        options(load_only(Relation.target_user_id),
                contains_eager(Relation.target_user).load_only(User.name)).all()
        print(fans)

小结：
因为使用子查询，会生成多条sql语句进行查询，效率低下。所以我们要使用inner join提高查询效率。

面试问题：
有没有使用过sqlalchemy的一些高级用法，解决性能问题？
上面的使用就是答案。

relathionship其他参数说明

uselist: 是否返回对象列表， True 返回对象列表 False返回第一个对象
lazy='dynamic' 惰性查询，返回一个query对象，可以进一步过滤
lazy="dynamic"只可以用在一对多和多对多关系中，不可以用在一对一和多对一中

小结：

在sqlalchemy设置的外键属性跟数据库表中的外键没有任何关系。

更新

方式一

user = User.query.get(1)
  user.name = 'Python'
  db.session.add(user)
  db.session.commit()

方式二

User.query.filter_by(id=1).update({'name':'python'})
  db.session.commit()

删除

方式一

user = User.query.order_by(User.id.desc()).first()
  db.session.delete(user)
  db.session.commit()

方式二

User.query.filter(User.mobile='18512345678').delete()
  db.session.commit()

事务

flask的sqlalchmey自动开启事务
查询的时候，自动开启和回滚。
db.session.commit() 提交事务
db.session.rollback() 回滚
事务是在请求上下文中开启。

environ = {'wsgi.version':(1,0), 'wsgi.input': '', 'REQUEST_METHOD': 'GET', 'PATH_INFO': '/', 'SERVER_NAME': 'itcast server', 'wsgi.url_scheme': 'http', 'SERVER_PORT': '80'}
with app.request_context(environ):
    try:
        user = User(mobile='13021074747', name='张三')
        db.session.add(user)
        db.session.flush() # 将db.session记录的sql传到数据库中执行
        profile = UserProfile(id=user.id)
        db.session.add(profile)
        db.session.commit()
    except:
        db.session.rollback()

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：cpu mesh 维度优先 cpu mesh是什么

下一篇：linux pg数据安装postgresgis linux安装苹果软件

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

flask_sqlalchemy执行原生sql flask sqlalchemy query

flask_sqlalchemy执行原生sql flask sqlalchemy query

SQLAlchemy操作

新增

查询

过虑

filter_by

filter

逻辑或：or_

逻辑与：and_

逻辑非：not_

offset：起始位置

limit：获取限制数据

order_by

复合查询

优化查询

聚合查询

综合练习

关联查询

使用ForeignKey

在模型中使用db.relationship创建关系属性

使用primaryjoin

使用primaryjoin，代码看起来更加直观，所以建议使用primaryjoin

join查询的使用

relathionship其他参数说明

小结：

更新

删除

事务

51CTO博客