今日内容概要

• 更多查询方式
• 连表查询
• 原生sql
• flask-sqlalchemy使用

今日内容详细

更多查询方式

查询

1.查询： filer:写条件     filter_by：等于的值
	.all() 查询所有  是list对象
  
  res = session.query(User).all()  # 是个普通列表
  print(type(res))
  print(len(res))
 

只查询某几个字段

  # select name as xx,email from user;
  res = session.query(User.name.label('name1'), User.email)
  print(res)  # SELECT users.name AS name1, users.email AS users_email FROM users
  print(res.all())  # [('nanaok', '@qq.com'), ('nanaok', '22@qq.com'), ('nanaok', '222@qq.com'), ('yyyy', '11@qq.com')]
  for item in res.all():
      print(item[0])  # nanaok...

filter、filter_by

# filter传的是表达式，filter_by传的是参数
res = session.query(User).filter(User.name == "nana").all()
res = session.query(User).filter(User.name != "nana").all()
res = session.query(User).filter(User.name != "nana", User.email == '3@qq.com').all()   # 且

and条件

# django 中使用 Q (与  或   非)
res = session.query(User).filter_by(name='nana').all()
res = session.query(User).filter_by(name='nana',email='47@qq.com').all()

.all() 查询所有

res = session.query(User).first()

res = session.query(User).all()
print(type(res))  # <class 'list'>  取一个 all了后是list，list 没有first方法

查询所有，使用占位符(了解)

:value :name

select * from user where id <20 or name=lqz
res = session.query(User).filter(text("id<:value or name=:name")).params(value=20, name='nana').all()

自定义查询（了解）

from_statement 写纯原生sql

res=session.query(User).from_statement(text("SELECT * FROM users where email=:email")).params(email='11@qq.com').all()
print(type(res[0]))  # <class 'models.User'>
print(res[0].name)  # yyyy

高级查询

表达式，and条件连接

res = session.query(User).filter(User.id > 1, User.name == 'nana').all() # and条件

between

res = session.query(User).filter(User.id.between(1, 9), User.name == 'nana').all()
res = session.query(User).filter(User.id.between(1, 9)).all()

in

res = session.query(User).filter(User.id.in_([1,2,3])).all()
print(res)  # [yyyy, nanaok]
res = session.query(User).filter(User.email.in_(['11@qq.com','r@qq.com'])).all()
print(res)  # [yyyy]

~非，除...外

res = session.query(User).filter(~User.id.in_([1,3,4])).all()
print(res)

二次筛选

res = session.query(User).filter(~User.id.in_(session.query(User.id).filter_by(name='nana'))).all()
print(res)

"""
SELECT *
FROM users 
WHERE (users.id NOT IN (SELECT users.id 
FROM users 
WHERE users.name = %(name_1)s))
"""

and or条件

from sqlalchemy import and_, or_

# or_包裹的都是or条件，and_包裹的都是and条件
res = session.query(User).filter(and_(User.id >= 3, User.name == 'nana')).all()  #  and条件
res = session.query(User).filter(User.id < 3, User.name == 'nana').all()  #  等同于上面
res = session.query(User).filter(or_(User.id < 2, User.name == 'nana')).all()
res = session.query(User).filter(or_(User.id < 2,and_(User.name == 'nana', User.id > 3),User.extra != ""))

通配符

res = session.query(User).filter(User.email.like('%@%')).all()
# select user.id from user where  user.name not like e%;
# 以e开头，不以e开头
res = session.query(User.id).filter(~User.name.like('e%'))
res = session.query(User).filter(~User.name.like('e%')).all()

分页

# 一页2条，查第5页
res = session.query(User)[2*5:2*5+2]

排序

res = session.query(User).order_by(User.email.desc()).all()
res = session.query(Book).order_by(Book.price.desc()).all()
res = session.query(Book).order_by(Book.price.asc()).all()
# 第一个条件重复后，再按第二个条件升序排
res = session.query(User).order_by(User.name.desc(), User.id.asc())

分组查询

from sqlalchemy.sql import func
# 分组后，只能拿分组字段和聚合函数字典，如果拿别的，是严格模式，会报错
res = session.query(User).group_by(User.extra)  # 如果是严格模式，就报错
# 分组之后取最大id，id之和，最小id  和分组的字段
from sqlalchemy.sql import func
res = session.query(
    User.name,
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id),
    func.avg(User.id)).group_by(User.name).all()
for item in res:
    print(item)

# 分组后having
# select name,max(id),sum(id),min(id) from  user group by  user.name   having id_max>2;
from sqlalchemy.sql import func
res = session.query(
    User.name,
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).having(func.max(User.id) > 2).all()

print(res)

连表查询

笛卡尔积后过滤

# select * from Person, Hobby where Person.hobby_id == Hobby.id
res = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id).all()
print(res)  # [(nana, 篮球), (cx, 羽毛球)]
for item in  res:
    print(type(item))
    print(item[0])

内连接

# select * from Person inner join Hobby on Person.hobby_id=Hobby.id;  # 内连接
res = session.query(Person).join(Hobby)   # 没有写on谁---》自动关联外键  默认inner join
print(res)  
res = session.query(Person).join(Hobby,Person.id == Hobby.id)   # 自己自定on---》自动关联外键
print(res)  
res = session.query(Person).join(Hobby,Person.hobby_id == Hobby.id)   # 自己自定on---》正确的
res = session.query(Person).join(Hobby,Person.id == Hobby.id)   # 自己自定on---》sql本身错误

左连接

isouter=True 外连，表示Person left join Favor，没有右连接，反过来即可

# select * from Person left join Hobby on Person.hobby_id=Hobby.id;  # 内连接
res = session.query(Person).join(Hobby,Person.hobby_id == Hobby.id,isouter=True)

右连接

# select * from Hobby left join Person on Person.hobby_id=Hobby.id;
res = session.query(Hobby).join(Person,isouter=True)
print(res)

多表连接

方式一：直接连
#select * FROM boy, girl, boy2girl WHERE boy.id = boy2girl.boy_id AND girl.id = boy2girl.girl_id
res = session.query(Boy, Girl,Boy2Girl).filter(Boy.id == Boy2Girl.boy_id,Girl.id == Boy2Girl.girl_id).all()
print(res)

方式二：join连
# SELECT* FROM boy INNER JOIN boy2girl ON boy.id = boy2girl.boy_id INNER JOIN girl ON girl.id = boy2girl.girl_id WHERE boy.id >= %(id_1)s
res = session.query(Boy).join(Boy2Girl).join(Girl).filter(Boy.id>=2)
print(res)

原生sql(django-orm如何执行原声sql)

1.有的复杂sql 用orm写不出来---》用原生sql查询
2.原生sql查询，查出的结果是对象
3.原生sql查询，查询结果列表套元组

sqlalchemy执行原生sql

from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine,text

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/db001", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

方式一

查询

sql = text('select * from users')
cursor  = session.execute(sql)
result  = cursor.fetchall()
print(result)  # 列表套元组

添加

sql = text('insert into users(name,email) values(:name,:email)')
cursor = session.execute(sql,params={"name": 'lqz', 'email': '3333@qq.com'})
session.commit()
print(cursor.lastrowid)  # 9
session.close()

方式二

以后都用session操作--->socpe_session线程安全）一般不用

conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
    "select * from users"
)
result = cursor.fetchall()
print(result)

方式三

res = session.query(User).from_statement(text("SELECT * FROM boy where name=:name")).params(name='nana').all()

Django执行原生sql

# 执行完的结果映射到对象中---》上面讲的  方式三：
from model import Book
books_obj_list = Book.objects.raw('select distinct id, book_name from test_book')
for book_obj in books_obj_list:
	print(book_obj.id, book_obj.book_name)

  
  
# 纯原生sql 
from django.db import connection
cur=connection.cursor() 
cur.execute('select distinct id, book_name from test_book')
print(cur.fetch_all())
cur.close()

with connection.cursor() as cur:
    cur.execute('select distinct id, book_name from test_book')

Flask_sqlalchemy使用

1.sqlalchemy 集成到flask中
	第三方: flask-sqlalchemy 封装了用起来，更简洁
	使用flask-sqlalchemy集成
2.步骤
	0. pip install flask-sqlalchemy
	1.导入 from flask_sqlalchemy import SQLAlchemy
  2.实例化得到对象
        db = SQLAlchemy()
  3.将db注册到app中
        db.init_app(app)
  4.视图函数中使用session
        全局的db.session  # 线程安全的
  5.models.py 中继承Model
        db.Model
  6.写字段 
        username = db.Column(db.String(80), unique=True, nullable=False)
  7.配置文件中加入
      SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root@127.0.0.1:3306/ddd?charset=utf8"
      SQLALCHEMY_POOL_SIZE = 5
      SQLALCHEMY_POOL_TIMEOUT = 30
      SQLALCHEMY_POOL_RECYCLE = -1
      # 追踪对象的修改并且发送信号
      SQLALCHEMY_TRACK_MODIFICATIONS = False

文件

flask_sqlalchemy
  -src
    --__init__.py
    --models.py
    --settings.py
    --views.py
  -manage.py

init.py

from flask import Flask
# 1.导入：
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

#  第二步：实例化得到对象
db  = SQLAlchemy()

app.config.from_pyfile('settings.py')

# 第三步：注册到app中
db.init_app(app)

from .views import user

app.register_blueprint(user)

models.py

# 第四步：在models中，找所有东西 都从db中拿
from src import db


class User(db.Model):
    # 以__开头的是配置
    __tablename__ = 'users'  # 数据库表名称，如果不写，以类名作为表名

    id = db.Column(db.Integer, primary_key=True)  # 主键索引，聚簇索引
    name = db.Column(db.String(64), index=True, nullable=False)  # name字段加辅助索引
    email = db.Column(db.String(32), unique=True)


    __table_args__ = (
        db.UniqueConstraint('id', 'name', name='uix_id_name'),  # 联合唯一
        db.Index('ix_id_name', 'name', 'email'),  # 索引
    )

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name

settings.py

DEBUG=True

SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:ln1998151125@127.0.0.1:3306/db001?charset=utf8"
SQLALCHEMY_POOL_SIZE = 5
SQLALCHEMY_POOL_TIMEOUT = 30
SQLALCHEMY_POOL_RECYCLE = -1
# 追踪对象的修改并且发送信号
SQLALCHEMY_TRACK_MODIFICATIONS = False

views.py

from flask import Blueprint
from .models import User

user = Blueprint('user', __name__)

from src import db

@user.route('/')
def index():
    res = db.session.query(User).filter_by(name='lqz').all()
    print(res)
    return 'asdfasdf'

manage.py

from src import app
if __name__ == '__main__':
    app.run()

项目--源码

flask-migrate使用

1.表,字段发生变化，都会有记录，自动同步到数据库中-->django支持这种操作
2.原生的sqlalchemy，不支持修改表的
3.flask-migrate可以实现类似于django的
	python manage.py makemigrations #记录
  python manage.py migrate        #真正的同步到数据库
    
4.使用步骤
	0.flask：2.2.2   flask-script：2.0.3   flask-migrate==2.7.0
	1.第一步：安装，依赖于flask-script
    	pip3.8 install flask-migrate==2.7.0
	2.在app所在的py(启动)文件中
    from flask_script import Manager
		from flask_migrate import Migrate, MigrateCommand
        manager = Manager(app)
        # flask-script可以自定义命令---》
        # flask-migrate本质是它借助于flask-script增加了几个命令来对数据库表和字段进行管理
        Migrate(app, db) # sqlalchemy的db对象
        manager.add_command('db', MigrateCommand) 
        
        manager.run() # 以后使用python manage.py runserver 启动项目
        
	3.以后第一次执行一下
    	python manage.py db init  # 生成一个migrations文件夹，里面以后不要动，记录迁移的编号
        
	4.以后在models.py 写表，加字段，删字段，改参数
    
	5.只需要执行
    	python manage.py db migrate  # 记录
        python manage.py db upgrade  # 真正的同步进去