目录
flask之sqlalchemy快速插入数据-scoped_session线程安全-基本增删改查-表模型一对多-多对多-连表查询
今日内容
1 sqlalchemy快速插入数据
# sqlalchemy是什么 orm框架 跟其他web框架没有必然联系 可以独立使用
# 安装 快速使用 执行原生sql
# 创建表和删除表
不能创建数据库
不能修改字段(增加 删除)
# 使用orm插入
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Book
# 第一步：生成engine对象
engine = create_engine(
"mysql+pymysql://[email protected]:3306/aaa",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 第二步：拿到一个Session类,传入engine
Session=sessionmaker(bind=engine)
# 第三步：拿到session对象,相当于连接对象（会话）
session=Session()
# 第四步，增加数据
book=Book(name='红楼梦',)
session.add(book)
session.commit()
# 第五步：关闭session对象
session.close()
# 第一步：导入
from sqlalchemy import create_engine
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
# 第二步：执行declarative_base，得到一个类
Base = declarative_base()
# 第三步：继承生成的Base类
class User(Base):
# 第四步 写字段
id = Column(Integer, primary_key=True) # 生成一列 类型是Integer 主键
name = Column(String(32), index=True, nullable=False) # name列varchar32 普通索引 不可为空
email = Column(String(32), unique=True)
# datetime.datetime.now不能加括号 加了括号 以后永远是当前时间
ctime = Column(DateTime, default=datetime.datetime.now)
extra = Column(Text, nullable=True)
# 第五步：写表名 如果不写以类名为表名
__tablename__ = 'users' # 数据库表名称
# 第六步：建立联合索引 联合唯一
__table_args__ = (
UniqueConstraint('id', 'name', name='uix_id_name'), # 联合唯一
Index('ix_id_name', 'name', 'email'), # 索引
)
def __str__(self): # 打印对象时 触发
return self.name
def __repr__(self): # 如果对象在容器中 显示的中文
return self.name
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
name = Column(String(32))
# 第七步：把表同步到数据库中
# 不会创建库 只会创建表
engine = create_engine("mysql+pymysql://root:[email protected]:3306/aaa",)
# 把表同步到数据库 (把Base管理的所有表 都创建到数据库)
Base.metadata.create_all(engine)
# 把所有表删除---> 不要轻易使用
# Base.metadata.drop_all(engine)
2 scoped_session线程安全
2.1 基本使用
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
# 第一步：生成engine对象
engine = create_engine(
"mysql+pymysql://root:[email protected]:3306/aaa",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 第二步：拿到一个Session类 传入engine
Session = sessionmaker(bind=engine)
# 线程不安全
# 原本是session = Session()
# 做成线程安全的：如何做的？
# 内部使用了local对象 取当前线程的session 如果当前线程有 就直接返回用 如果没有 创建一个 放到local中
# session 是 scoped_session 的对象
session = scoped_session(Session)
# 以后全局使用session即可 它现在线程安全
2.2 加在类上的装饰器
# session 是 scoped_session 的对象 类上没有属性和方法 但是 用的时候 确实用
session = scoped_session(Session)
def speak():
print('说话了')
def wrapper(func):
def inner(*args, **kwargs):
res = func()
res.name = 'lqz'
res.speak = speak
return res
return inner
@wrapper # 语法糖会把Person当参数传入到装饰器中 Person=wrapper(Person)
class Person:
pass
p = Person()
print(p.name)
p.speak()
3 基本增删改查
# 增 删 改
# 查 基本查询和高级查询
from models import User, Book
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.sql import text
engine = create_engine("mysql+pymysql://root:[email protected]:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
# 1 增加：add add_all
# user = User(name='pyy', email='[email protected]', extra='单点能')
# user1 = User(name='yyy', email='[email protected]', extra='的那仨')
# user1 = User(name='lqz', email='[email protected]', extra='666')
# book = Book(name='西游记')
# session.add(user) # 一个一个增加
# session.add_all([user, user1, book]) # 多个对象可以是models中任意表模型的对象
# 2 基本查 filter filter_by filter:写条件 filter_by：等于的值
# filter
# 2.1 session.query(User) 中写表模型 可以写多个表模型(连表操作) select * from User;
# 2.2 filter 过滤条件 必须写表达式 == >= <= != 原生：select * from user where user.id=1
# 2.3 all:普通列表 first
# user = session.query(User).filter(User.name == 'lqz').first()
# print(user) # lqz
# user = session.query(User).filter(User.name == 'lqz').all()
# print(user) # [lqz]
# res = session.query(User).filter(User.id > 1).all()
# print(res) # [yyy, lqz]
# filter_by 直接写等式 不能写成User.name = 'lqz'
# user = session.query(User).filter_by(name='lqz').first()
# print(user) # lqz
# user = session.query(User).filter_by(id=2).first()
# print(user) # yyy
# 3 删除(查到才能删) filter或filter_by查询的结果 不要all或first出来 .delete()即可
# res = session.query(User).filter_by(id=2).delete()
# print(res) # 影响的行数 1
# 4 修改(查到才能改)
# 方式一：update修改
# res = session.query(User).filter_by(id=3).update({"name": "彭于晏"})
# print(res) # 影响的行数 1
# 方式二：使用对象修改
res = session.query(User).filter_by(id=3).first()
# res = session.query(User).filter_by(name='zzz').first()
res.name = '啦啦啦'
print(res.id) # 3
session.add(res) # add 如果有主键 就是修改 如果没有主键就是新增
session.commit()
session.close()
3.1 基本增删改查和高级查询
# 5 只查询某几个字段
# select name as xx, email from user;
# res = session.query(User.name.label('xx'), User.email)
# print(res) # 打出原生sql：SELECT users.name AS xx, users.email AS users_email FROM users
# print(res.all()) # [('pyy', '[email protected]'), ('啦啦啦', '[email protected]')]
# for item in res.all():
# print(item[0]) # pyy 啦啦啦
# 取了一个all之后是list list没有first方法
# 5.2 查询所有 使用占位符(了解) :value :name
# select * from user where id < 20 or name=lqz
# res = session.query(User).filter(text("id<:value or name=:name")).params(value=10, name='lqz').all()
# print(res) # [pyy, 啦啦啦, lqz]
# 5.3 自定义查询(了解)
# from_statement 写纯原生sql
# res = session.query(Book).from_statement(text("SELECT * FROM users where email=:email")).params(email='[email protected]').all()
# print(type(res[0])) # 是book的对象 但是查的是User表 不要这样写
# print(res[0].name) # lqz 这个是user表中的数据
# 不要这样写 会造成数据错乱
# 5.4 高级查询
# 表达式 and条件连接
# res = session.query(User).filter(User.id > 1, User.name == 'lqz').all() # and条件
# print(res) # 只查一条 [lqz]
# between
# res = session.query(User).filter(User.id.between(1, 9), User.name == 'lqz').all()
# print(res) # [lqz]
# res = session.query(User).filter(User.id.between(1, 9)).all()
# print(res) # [pyy, 啦啦啦, lqz]
# in
# res = session.query(User).filter(User.id.in_([1, 3, 4])).all()
# print(res) # [pyy, 啦啦啦, lqz]
# res = session.query(User).filter(User.email.in_(['[email protected]', '[email protected]'])).all()
# print(res) # [啦啦啦, lqz]
# ~非，除了条件外
# res = session.query(User).filter(~User.id.in_([1, 3])).all()
# print(res) # [lqz] lqz的id为4
# 二次筛选
# 意思 第二次筛选了出了lqz的id 当做第一次筛选的条件
# res = session.query(User).filter(~User.id.in_(session.query(User.id).filter_by(name='lqz'))).all()
# print(res) # [pyy, 啦啦啦]
# and or 条件
from sqlalchemy import and_, or_
# or_包裹的都是or条件 and_包裹的都是and条件
# res = session.query(User).filter(and_(User.id >= 3, User.name == 'lqz')).all() # and条件
# print(res) # [lqz]
# res = session.query(User).filter(User.id < 3, User.name == 'lqz099').all() # 不写and_和or_ 默认是and_
# print(res) # [] 条件筛选出空数据
# res = session.query(User).filter(or_(User.id < 2, User.name == 'eric')).all()
# print(res) # [pyy]
# res = session.query(User).filter(
# or_(
# User.id < 2,
# and_(User.name == 'lqz099', User.id > 3),
# User.extra != ""
# )).all()
# print(res) # [pyy, 啦啦啦, lqz]
# 通配符 以e开头 不以e开头
# res = session.query(User).filter(User.email.like('%@%')).all() # 匹配有@的前后无所谓
# print(res) # [pyy, 啦啦啦, lqz]
# # select user.id from user where user.name not like e%;
# res = session.query(User.id).filter(~User.name.like('e%'))
# print(res) # 打印原生sql
# 分页
# 一页2条 查第五页
# res = session.query(User)[2*5:2*5+2]
# print(res) # []
# 排序 desc降序 asc升序
# 根据name降序排列(从大到小)
# res = session.query(User).order_by(User.email.desc()).all()
# print(res)
# 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())
# 分组查询 5个聚合函数
from sqlalchemy.sql import func
# res = session.query(User).group_by(User.extra) # 如果是严格模式 就报错
# 分组之后取最大id id之和 最小id 和分组的字段
# res = session.query(
# User.extra,
# func.max(User.id),
# func.sum(User.id),
# func.min(User.id)
# ).group_by(User.extra).all()
# for item in res:
# print(item[2]) # 3 4 1
# having
# select max(id), sum(id), min(id) from user group by user.extra having id_max>2;
# res = session.query(
# func.max(User.id),
# func.sum(User.id),
# func.min(User.id)
# ).group_by(User.extra).having(func.max(User.id) > 2)
3.2 原生sql
### 方式一：
# 第一步：导入
# from sqlalchemy import create_engine
#
# # 第二步：生成引擎对象
# engine = create_engine(
# "mysql+pymysql://root:[email protected]:3306/aaa",
# 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 models import User, Book
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, scoped_session
engine = create_engine("mysql+pymysql://root:[email protected]:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
# 2.0.9 版本需要使用text包裹一下 原来版本不需要
# cursor = session.execute(text("select * from users"))
# res = cursor.fetchall()
# print(res)
cursor = session.execute(text('insert into books(name) values(:name)'), params={"name": '红楼梦'})
print(cursor.lastrowid) # 最后一个id
session.commit()
session.close()
3.3 django中执行原生sql
# 选择的查询基表Book.object.raw 只是一个傀儡 正常查询出哪些字段 就打印哪些内容
def index(request):
books = Book.object.raw('select * from app01_book where id=1') # RawQuerySet 用起来跟列表一样
books = Publish.object.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')
4 一对多
# 一对一：本身是一个表 拆成两个表 做一对一的关联 本质就是一对多 只不过关联字段唯一
# 一对多：关联字段写在多的一方
# 多对多：需要建立中间表 本质也是一对多
# 本质就只有一种外键关系
4.1 表模型
from sqlalchemy import create_engine
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship
# 执行declarative_base 得到一个类
Base = declarative_base()
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
caption = Column(String(50), default='篮球')
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=True)
# hobby指的是tablename而不是类名
# 关联字段写在多的一方 写在Person中 跟hobby表中id字段做 外键关联
hobby_id = Column(Integer, ForeignKey("hobby.id"))
# 跟数据库无关 不会新增字段 只用于快速链表操作
# 基于对象的跨表查询：就要加这个字段 取对象 person.hobby person.hobby_id
# 类名 backref用于反向查询
hobby = relationship('Hobby', backref='pers') # 如果有hobby对象 拿到所有人 hobby.pers
def __repr__(self):
return self.name
engine = create_engine("mysql+pymysql://root:[email protected]:3306/aaa")
# 把表同步到数据库 (把被Base管理的所有表 都创建到数据库)
Base.metadata.create_all(engine)
# 把所有表删除
# Base.metadata.drop_all(engine)
4.2 新增和基于对象的查询
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from models1 import Hobby, Person
engine = create_engine("mysql+pymysql://root:[email protected]:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
# 一对多新增
# hobby = Hobby(caption='乒乓球')
# session.add(hobby)
# person = Person(name='张三')
# session.add(person)
# 先插入两条数据 并没有关联
# 新建数据并插入 并做关联
# hobby = session.query(Hobby).filter(Hobby.caption=='乒乓球').first()
# person1 = Person(name='李四', hobby_id=hobby.id)
# person2 = Person(name='王五', hobby_id=1)
# session.add_all([person1, person2])
# 支持按对象的增加方式 必须relationship做关联
# 方式一
# hobby = session.query(Hobby).filter(Hobby.caption=='乒乓球').first()
# person = Person(name='赵六', hobby=hobby)
# session.add_all([hobby, person])
# 方式二
# hobby = Hobby(caption='羽毛球') # 表中暂时没有
# person = Person(name='赵六', hobby=hobby)
# session.add_all([person, hobby])
# 基于对象的跨表查询
# 正向查询
person = session.query(Person).filter(Person.name=='王五').first()
print(person.hobby_id) # 1
print(person.hobby) # Hobby的对象 <models1.Hobby object at 0x0000011C34EB9A30>
# 反向查询
hobby = session.query(Hobby).filter(Hobby.id==1).first()
print(hobby.pers) # [张三, 王五, 李四, 王五, 赵六]
session.commit()
session.close()
5 多对多
5.1 表模型
from sqlalchemy import create_engine
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship
# 第二步：执行declarative_base，得到一个类
Base = declarative_base()
# 多对多
# 中间表 手动创建
class Boy2Girl(Base):
__tablename__ = 'boy2girl'
id = Column(Integer, primary_key=True, autoincrement=True)
girl_id = Column(Integer, ForeignKey('girl.id'))
boy_id = Column(Integer, ForeignKey('boy.id'))
class Girl(Base):
__tablename__ = 'girl'
id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True, nullable=False)
def __str__(self):
return self.name
def __repr__(self):
return self.name
class Boy(Base):
__tablename__ = 'boy'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), unique=True, nullable=False)
# 与生成表结构无关 仅用于查询方便 放在哪个单表中都可以
# 方便快速查询 写了这个字段 相当于django的manytomany 快速使用基于对象的跨表查询
girls = relationship('Girl', secondary='boy2girl', backref='boys')
def __str__(self):
return self.name
def __repr__(self):
return self.name
engine = create_engine("mysql+pymysql://root:[email protected]:3306/aaa")
# 把表同步到数据库 (把被Base管理的所有表，都创建到数据库
Base.metadata.create_all(engine)
# 把所有表删除
# Base.metadata.drop_all(engine)
5.2 增加和基于对象的跨表查询
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from models2 import Girl, Boy, Boy2Girl
engine = create_engine("mysql+pymysql://root:[email protected]:3306/aaa")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
# 新增
# 1 笨办法新增
# girl = Girl(name='刘亦菲')
# boy = Boy(name='彭于晏')
# session.add_all([girl, boy])
# session.add(Boy2Girl(girl_id=1, boy_id=1))
# 2 使用relationship
# boy = Boy(name='lqz')
# boy.girls = [Girl(name='迪丽热巴'), Girl(name='景田')]
# session.add(boy)
# 基于对象的跨表查询
# 正向
boy = session.query(Boy).filter(Boy.id==2).first()
print(boy.girls) # [迪丽热巴, 景田]
# 反向
girl = session.query(Girl).filter(Girl.id==2).first()
print(girl.boys) # [lqz]
session.commit()
session.close()
6 连表查询
# 关联关系 基于连表的跨表查询
# 链表操作
# select * from person,hobby where person.hobby_id=hobby.id;
# res = session.query(Person, Hobby).filter(Person.hobby_id==Hobby.id).all()
# print(res) # 查出所有的对应关系
# 自己连表查询
# join表 默认是 inner join 自动按外键关联
# select * from Person inner join Hobby on Person.hobby_id=Hobby.id;
# res = session.query(Person).join(Hobby).all() # [张三, 王五, 李四, 王五, 赵六, 赵六]
# print(res)
# isouter=True 外连 表示Person left join Favor 没有右连接 反过来即可
# select * from Person left join Hobby on Person.hobby_id=Hobby.id;
# res = session.query(Person).join(Hobby, isouter=True).all()
# 没有right join 通过表换位置实现
# res = session.query(Hobby).join(Person, isouter=True).all()
# 自己指定on条件(连表条件) 第二个参数 支持on多个条件 用and_ 同上
# select * from Person left join Hobby on Person.id=Hobby.id;
res = session.query(Person).join(Hobby, Person.id==Hobby.id, isouter=True)
print(res)
# 多对多关系连表
# 多对多关系 基于链表的跨表查
# 方式一：直接连
res = session.query(Boy, Girl, Boy2Girl).filter(Boy.id == Boy2Girl.boy_id, Girl.id == Boy2Girl.girl_id).all()
# 方式二：join连
res = session.query(Boy).join(Boy2Girl).join(Girl).filter(Boy.id>=2).all()
print(res) # [lqz]