day06

内容概要

  • sqlalchemy快速插入数据
  • scoped_session线程安全
  • 基本增删查改
  • 一对多
  • 多对多
  • 连表查询
  • sqlalchemy自己集成flask
  • flask-sqlalchemy使用
  • flask-migrate使用

SQLAlchemy快速插入数据

models

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Index, Text, ForeignKey, DateTime, UniqueConstraint
from sqlalchemy.orm import relationship

# 第二步:执行declarative_base,得到一个类
Base = declarative_base()


class Book(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    __tablename__ = "books"

    def __str__(self):
        return self.name  # 打印的时候触发

    def __repr__(self):
        return self.name  # 打印的时候在容器里面触发


class User(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    __tablename__ = "users"

    def __str__(self):
        return self.name  # 打印的时候触发

    def __repr__(self):
        return self.name  # 打印的时候在容器里面触发


engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test2")
# 把表同步到数据(把被Base股那里的所有表,都创建到数据库)
Base.metadata.create_all(engine)

# 把所有表删除
# Base.metadata.create_all(engine)

py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Book

# 第一步: 生成engin对象
engine = create_engine(
    "mysql+pymysql://root:123@127.0.0.1:3306/test2",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
    pool_recycle=1  # 多久之后对线程池中线程进行一次连接的回收(重置)
)

# 第二步: 拿到一个Session类, 传入engine
Session = sessionmaker(bind=engine)

# 第三步: 拿到session对象,相当于连接对象(会话)
session = Session()

# 第四步,增加数据
from models import Book

book = Book(name="红楼梦")
session.add(book)
session.commit()

session.close()

image-20230411145237878

scoped_session线程安全

from sqlalchemy.orm import scoped_session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

"""
pool_recycle = -1 连接回收时间 -1，永不回收（推荐设置3600即1h）
注意： MySQL连接的默认断开时间是 8小时
"""
engine = create_engine(
    "mysql+pymysql://root:123@127.0.0.1:3306/test2",
    max_overflow=0,  # 超过连接池大小外最多创建的连接个数
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
    pool_recycle=-1  # 连接回收的时间
)
Session = sessionmaker(bind=engine)


# 线程不安全
# session = Session()

# 因为如果使用全局的session 会有并发安全问题
# scoped_session采用 local的方式,
# 把session复制多份每个都使用自己的这就解决的并发安全问题

# 做成线程安全的：如何做的？
# 内部使用了local对象，取当前线程的session，如果当前线程有，就直接返回用，如果没有，创建一个，放到local中
# session 是  scoped_session 的对象
session = scoped_session(Session)


# 以后全局使用session即可，它线程安全

加在类上的装饰器

类装饰器

  1. 加在类上的装饰器

    def speak():
    print("说话了")


def wrapper(func):
    def inner(*args, **kwargs):
        func.NAME = "huwu"  # 这是类属性
        res = func(*args, **kwargs)
        res.name = 'lqz'  # 这是对对象的属性
        res.speak = speak
        return res
    return inner

@wrapper
class A:
    pass


aa = A()

print(aa.name)
aa.speak()

  2. 类当一个装饰器

    # 类当装饰器

class A:
    def __init__(self, func, *args, **kwargs):
        self.func = func

    def __call__(self, *args, **kwargs):
        print("执行前")
        res = self.func(*args, **kwargs)
        print("执行后")
        return res


@A  # 相当于 aa = A(aa)
def aa():
    print("我是aa")


aa()  # 现在的 aa是 A的对象
print(aa.func)

基本的增删查改

  1. add 或 add_all
from models import Book, User
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.sql import text

engine = create_engine(
    "mysql+pymysql://root:123@127.0.0.1:3306/test2",
    pool_timeout=30,
    pool_size=5,
    pool_recycle=-1,  # 重置连接的时间, -1永不重置
    max_overflow=0,  # 超过连接池大小最多创建的个数
)

Session = sessionmaker(bind=engine)
session = scoped_session(Session)

# 1 增加:add   add_all
user = User(name="lqz")
user1 = User(name="jason")
book = Book(name="西游记")

session.add_all([user, user1, book])
session.commit()
session.close()

  1. 查 filter filter_by filter:写条件, filter_by:等于值

    all:普通列表 first 单个对象

    # 2. 查 filter filter_by  filter:写条件, filter_by:等于值

# res = session.query(User)  # query里面写表模型, 可以写多个表模型
# print(res)  # SELECT users.id AS users_id, users.name AS users_name FROM users

# res = session.query(User).filter(User.name == "lqz")
# print(res)  # SELECT users.id AS users_id, users.name AS users_name FROM users  WHERE users.name = %(name_1)s

# res = session.query(User).filter(User.name == "lqz").all()
# print(res)  # [lqz] 列表套对象
# print(res[0].name)  # lqz


# res = session.query(User).filter_by(name='lqz')
# print(res)  # SELECT users.id AS users_id, users.name AS users_name FROM users WHERE users.name = %(name_1)s

res = session.query(User).filter_by(name='lqz').all()
res1 = session.query(User).filter_by(name='lqz').first()
print(res, type(res))  # [lqz] <class 'list'>
print(res1, type(res1))  # lqz <class 'models.User'>

  2. 删除(查到才能删除) filter或filter_by查询结果 不要all或者first出来

    # 删除 deleter
res = session.query(User).filter(User.name == "lqz").delete()
session.commit()
print(res)  # 影响的行数  1

  3. 修改(查到才能修改)

    方式一：update修改

    # 修改
res = session.query(User).filter(User.name == "jason").update({"name": "彭于晏"})
session.commit()
session.close()

res = session.query(User).all()
print(res)  # [彭于晏]

    方式二：使用对象修改

    # 方式二
res = session.query(User).filter(User.name == "彭于晏").first()
res.name = "lqz"
session.add(res)
session.commit()
session.close()
print(session.query(User).all())  # [lqz]

    add 如果有主键就修改，没有主键就新增

高级查询

  1. 只查询某几个字段

    # select addr as xx from users;
# res = session.query(User.addr.label("xx"), User.name)
# print(res)  # SELECT users.addr AS xx, users.name AS users_name FROM users
# print(res.all())  # [('三大', 'jason'), ('撒大大撒', 'lqz')]
# print(res.all()[0].name)  # jason

  2. 查询所有，使用占位符

    # 查询所有，使用占位符
# select * from user where id<2 or name=lqz;
res = session.query(User).filter(text("id<:value or name=:name")).params(value=2, name="lqz")
# print(res)  # SELECT users.id AS users_id, users.name AS users_name, users.addr AS users_addr FROM users WHERE id<%(value)s or name=%(name)s
print(res.all())  # [jason, lqz]

  3. 自定查询

    # 自定义查询
# res = session.query(User).from_statement(text("select * from users where name=:name")).params(name="lqz").all()
# print(res, type(res))  # [lqz] <class 'list'>

res = session.query(User).from_statement(text("select * from books")).all()
print(res, type(res))
print(res[0], type(res[0]))
print(res[0].addr)  # 用的还是原来, 不要这么写

  4. and连接

    # res = session.query(User).filter(User.id>1, User.name=='lqz').all()
# print(res)  # [lqz]

  5. in

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

  6. between什么和什么之间

    # between
res = session.query(User).filter(User.id.between(1, 3)).all()
print(res)

  7. ~非,除...外

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

  8. 二次筛选

    # 二次筛选
res = session.query(User).filter(~User.id.in_(session.query(User.id).filter(User.name == 'lqz'))).all()
print(res)  # [jason]

  9. and，or条件

    from sqlalchemy import and_, or_
# and or 条件
# or_包裹的都是or条件, and_包裹的都是and条件
# res = session.query(User).filter(and_(User.id) >=3, User.name == "lqz").all()
# print(res)  # []

res = session.query(User).filter(or_(User.id >=3, User.name == "lqz")).all()
print(res)  # [lqz]
# res = session.query(User).filter(
#     or_(
#         User.id < 2,
#         and_(User.name == 'lqz099', User.id > 3),
#         User.extra != ""
#     )).all()

  10. 通配符，以e开头，不以e开头

    res = session.query(User).filter(User.name.like("%q%")).all()
print(res)  # [lqz]

  11. 分页

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

  12. 排序

    # 排序
res = session.query(User).order_by(User.id.desc()).all()
print(res)  # [lqz, jason]

  13. 分组

# 分组查询 5个 聚合函数
from sqlalchemy.sql import func

# res = session.query(User).group_by(User.extra)  # 如果是严格模式，就报错
from sqlalchemy.sql import func
# 分组之后取最大id, id之和, 最小id 和分组的字段
# res = session.query(User.addr, func.max(User.id), func.sum(User.id), func.min(User.id)).group_by(User.addr).all()
#
# print(res)  # [('三大', 3, Decimal('4'), 1), ('撒大大撒', 4, Decimal('6'), 2)]

res = session.query(func.max(User.id), func.sum(User.id)).group_by(User.addr).having(func.max(User.id) > 3).all()
print(res)  # [(4, Decimal('6'))]

原生sql

方式一：

# from sqlalchemy import create_engine
# engine = create_engine(
#     "mysql+pymysql://root:123@127.0.0.1:3306/test2",
#     max_overflow=0,  # 超过连接池大小外最多创建的连接
#     pool_size=5,  # 连接池大小
#     pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
#     pool_recycle=-1,  # 多久之后对线程池中的线程进行一次连接的回收
# )
# conn = engine.raw_connection()
# cursor = conn.cursor()
# cursor.execute("select * from users")
# print(cursor.fetchall())

方式二：

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

engine = create_engine(
    "mysql+pymysql://root:123@127.0.0.1:3306/test2"
)

Session = sessionmaker(bind=engine)
session = scoped_session(Session)

"""
2.0.9 版本需要使用text包裹一下,原来版本不需要
cursor = session.execute(text('select * from users'))
result = cursor.fetchall()
print(result)
"""
cursor = session.execute(text("insert into books(name) values(:name)"), params={"name": "三国演义"})
session.commit()
print(cursor.lastrowid)
session.close()

django执行原生sql

# 选择的查询基表Book.objects.raw ，只是一个傀儡，正常查询出哪些字段，都能打印出来

def index(request):
    # books = Book.objects.raw('select * from app01_book where id=1')  # RawQuerySet  用起来跟列表一样
    # books = Publish.objects.raw('select * from app01_book where id=1')  # RawQuerySet  用起来跟列表一样
    # print(books[0])
    # print(type(books[0]))
    # # for book in books:
    # #     print(book.name)
    # # print(books[0].name)
    # print(books[0].addr)  #也能拿出来，但是是不合理的

    res = Book.objects.raw('select * from app01_publish where id=1')  # RawQuerySet  用起来跟列表一样
    print(res[0])
    print(type(res[0]))
    print(res[0].name)
    # book 没有addr，但是也打印出来了
    print(res[0].addr)

    return HttpResponse('ok')

一对多

一对一:本身是一个表,拆成两个表，做一对一的关联 本质就是一对多，只不过关联字段唯一
    
一对多：管关联字段写在多的一方

多对多：需要建立中间表，本质也是一对多

本质就只有一种外键关系

models

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

Base = declarative_base()


class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    # publish指的是 tablename名不是类名
    # 关联字段写在多的一方,写在Book中 跟 publish_id做外键关联
    publish_id = Column(Integer, ForeignKey("publish.id"))
    publish = relationship("Publish", backref='books')

    # 跟数据库无关, 不会新增字段,只用于快速连表操作
    # 基于对象的跨表查询:就要加这个字段,取对象book.publish
    # book.publish_id
    # 类名 backref用于反向查询
    def __repr__(self):
        return self.name


class Publish(Base):
    __tablename__ = 'publish'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    addr = Column(String(64))

    def __repr__(self):
        return self.name


# engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test3")

# Base.metadata.create_all(engine)
#

新增

方式一

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from models1 import Book, Publish

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test3")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

# 一对多新增

# book = Book(name="三国演义")
# session.add(book)
# publish = Publish(name="北京出版社", addr="北京")
# session.add(publish)
#
# session.commit()
# session.close()

publish = session.query(Publish).filter(Publish.name=="北京出版社").first()
book = session.query(Book).filter(Book.name=='三国演义').first()
book.publish_id = publish.id
session.add(book)
session.commit()
session.close()

方式二

publish = Publish(name="河南出版社", addr="河南")
book = Book(name="水浒传", publish=publish)
session.add_all([publish, book])
session.commit()

正反向查询

# 基于对象的跨表查询
# 反向查询
publish = session.query(Publish).filter(Publish.name == "河南出版社").first()
print(publish.books)

# 正向查询
book = session.query(Book).filter(Book.name=="水浒传").first()
print(book.publish)

多对多

models

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

Base = declarative_base()


class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    # publish指的是 tablename名不是类名
    # 关联字段写在多的一方,写在Book中 跟 publish_id做外键关联
    publish_id = Column(Integer, ForeignKey("publish.id"))
    publish = relationship("Publish", backref='books')

    # 跟数据库无关, 不会新增字段,只用于快速连表操作
    # 基于对象的跨表查询:就要加这个字段,取对象book.publish
    # book.publish_id
    # 类名 backref用于反向查询

    author = relationship("Author", secondary="author2book", backref='books')

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name


class Publish(Base):
    __tablename__ = 'publish'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    addr = Column(String(64))

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name


class Author2Book(Base):
    __tablename__ = "author2book"
    id = Column(Integer, primary_key=True)
    book_id = Column(Integer, ForeignKey("books.id"))
    publish_id = Column(Integer, ForeignKey("publish.id"))


class Author(Base):
    __tablename__ = "author"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name


engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test3")

Base.metadata.create_all(engine)

# Base.metadata.drop_all(engine)

py

方式一

# 
from models1 import Author, Author2Book

book = Book(name="红楼梦")
author = Author(name="jason")
session.add_all([book, author])  # 先执行 被关联表
# session.add(Author2Book(book_id=1, author_id=1))  # 然后在执行关联表
session.commit()
# session.close()

方式二

# 方式二
book = Book(name="西游记")
author = Author(name="jason")
book.author = [author, ]  # 因为是多 所以用列表包裹
session.add(book)
session.commit()

正反向查询

# 跨表查询
# 正向
book = session.query(Book).filter(Book.name == "西游记").first()
print(book.author)
# 反向
author = session.query(Author).filter(Author.name == "jason").first()
print(author.books)

连表操作

一对多

关联关系，基于连表的跨表查询

# 连表操作
# select * from books, publish where books.publish_id = publish.id
res = session.query(Book, Publish).filter(Book.publish_id == Publish.id).all()
print(res)

自己连表查询

# 自己连表操作 join表, 默认是 inner join 自动外键关联
# res = session.query(Book).join(Publish).all()
# print(res)
# print(res[0].name)
# print(res[0].publish.name)

# isouter=True 外连, 表示 book left join publish 没有右连接
# select * from books left join publish on books.publish_id = publish.id
# res = session.query(Book).join(Publish, isouter=True)
# print(res)
# print(res.all())


# 自己指定on条件(连表条件),第二个参数,支持on多个条件用and_,同时
# select * from books left join publish on books.publish_id = publish.id
res = session.query(Book).join(Publish, Book.publish_id == Publish.id, isouter=True)
print(res)
print(res.all())

多对多

# 多对多
# 方式一:直连
# res = session.query(Book, Author2Book, Author).filter(Book.id == Author2Book.book_id, Author.id == Author2Book.author_id).all()
# print(res)

# 方式二 join连接

res = session.query(Book).join(Author2Book).join(Author).filter(Book.id > 1).all()
print(res)

sqlalchemy自己集成flask

集成到flask中，直接使用sqlalchemy

models

###使用原生sqlalchemy


# 第一步：导入
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index

Base = declarative_base()


class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))


if __name__ == '__main__':
    engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test1")
    # 把表同步到数据库  （把被Base管理的所有表，都创建到数据库）
    Base.metadata.create_all(engine)

py

from flask import Flask
from sqlalchemy import create_engine
from sqlalchemy.orm import session, sessionmaker
from sqlalchemy.orm import scoped_session

engin = create_engine(
    "mysql+pymysql://root:123@127.0.0.1:3306/test1",
    pool_recycle=-1,
    pool_timeout=30,
    pool_size=5,
    max_overflow=0,
)

Session = sessionmaker(engin)

session_db = scoped_session(Session)

app = Flask(__name__)


@app.route("/")
def home():
    from models import Book
    session_db.add(Book(name="西游记"))
    session_db.commit()
    session_db.close()
    return "增加"


if __name__ == '__main__':
    app.run()

flask-sqlalchemy使用

下载pip3 install flask-sqlalchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123@127.0.0.1:3306/test1'
db = SQLAlchemy()
db.init_app(app)


# class Publish(db.Model):
#     id = db.Column(db.Integer, primary_key=True)
#     name = db.Column(db.String(32))

# class Book(db.Model):
#     __tablename__ = 'books'
#     id = db.Column(db.Integer, primary_key=True)
#     name = db.Column(db.String(32))

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))
    # db.Column(db.Integer)


if __name__ == '__main__':
    with app.app_context():
        db.create_all()

"""

从FlASK-SQLAlChemy 3.0开始，对db.engine(和db.session)的所有访问都需要活动的FlaskTM应用程序上下文.db.create_all使用db.engine，因此它需要应用程序上下文.
复制代码
with app.app_context():
db.create_all()
"""

flask-migrate使用

表发生变化，都会右记录，自动同步到数据库中

原生的sqlalchemy，不支持修改表的

flask-migrate可以实行类似与django的

python38 manage.py db init  只执行一次,初始化的时候时候使用
python38 manage.py db migrate  写入迁移文件
python38 manage.py db upgrade  同步数据库


from apps import create_app
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from apps import db
from apps.models import User
app = create_app()

# flask-script的使用
# 第一步，初始化出flask_script的 manage
manager = Manager(app)
Migrate(app, db)
# 第二步 使用flask_migrate的Migrate 包裹一下app 和db(sqlalchemy对象)
manager.add_command("db", MigrateCommand)

if __name__ == '__main__':
    manager.run()
    # app.run()


"""
python38 manage.py db init  只执行一次,初始化的时候时候使用
python38 manage.py db migrate  写入迁移文件
python38 manage.py db upgrade  同步数据库


"""

image-20230411234623798

image-20230411234713104

image-20230411234738085

image-20230411234752250

posted @ 2023-04-11 20:45  可否  阅读(22)  评论(0)    收藏  举报