flask-sqlalchemy

1.sqlalchemy快速使用

flask中没有orm框架。我们需要使用一个对象关系映射来操作数据库。sqlalchemy就是其中之一。
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件
安装：

pip3.10 install sqlalchemy

补充：SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件

mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

# 更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html

1.1 使用原生sql连接数据库

sqlalchemy自带连接池，所以不需要自己创建连接池

# 1.导入模块
import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine

# 生成引擎对象,sqlalchemy自带连接池
engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/book?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置），-1是不回收
)

conn = engine.raw_connection()
cursor=conn.cursor()
cursor.execute('select * from book')
print(cursor.fetchall())

1.2 创建数据表

# 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
import datetime

# 2.执行declarative_base，得到一个类
Base = declarative_base()


# 3.继承Base类
class User(Base):
    # 4.写字段
    id = Column(Integer, primary_key=True)  # 整形类型、主键
    name = Column(String(32), index=True, nullable=False)  # varchar类型,最大长度为32，主键索引，不可为空
    email = Column(String(32), unique=True)
    ctime = Column(DateTime, default=datetime.datetime.now)  # 这个字段可以不用传，自动生成当前时间
    extra = Column(Text, nullable=True)

    # 5.写表名
    __tablename__ = 'users'  # 不写表名默认以类名为表名

    # 6.建立联合索引，联合唯一
    __table_args__ = (
        UniqueConstraint('id', 'name', name='uix_id_name'),  # 联合唯一
        Index('ix_id_name', 'name', 'email'),  # 索引
    )
	'''控制单个数据返回数据的name字段'''
	def __str__(self):
        return self.name
	'''控制容器(列表)中的数据返回每个数据中的name字段'''
    def __repr__(self):
        return self.name


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


# 7.将表同步到数据库中，不会创建库,库需要自己创建，只会创建表
engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/user",  # 需要手动创建一个名为name的库
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

# 8.将所有被Base管理的表都同步到数据库
Base.metadata.create_all(engine)

# 9.删除掉Base管理的表
# Base.metadata.drop_all(engine)

2.sqlalchemy快速插入数据

使用orm插入：

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Book,User
# 1.生成engine对象
engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置），-1是不回收
)

# 2.传入engine，拿到Session类
Session = sessionmaker(bind=engine)
# 3.拿到session对象
session = Session()
# 4.新增数据,主键是id可以不写
user = User(name='henry',email='2@qq.com')
session.add(user)
print(user)  # 是新产生的数据对象
#  5.一定要commit才能保存到数据库
session.commit()
session.close()

3.scoped_session线程安全

3.1 不使用scoped_session情况

再添加之前需要使用session对象，如果我们添加在全局，python多线程(实际上是同一条线程在程序之间切换)会造成数据的错乱，全局都用一个session会产生并发安全的问题，所以需要写在函数内：

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Book, User
from flask import Flask

engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置），-1是不回收
)
app = Flask(__name__)
Session = sessionmaker(bind=engine)


@app.route('/index')
def index():
    book_obj = Book(name='红楼梦')
    '''为了防止数据错乱，将session对象放在函数内产生'''
    session = Session()
    session.add(book_obj)

    session.commit()
    session.close()
    return '添加成功'

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

3.2 使用scoped_session情况

之前我们是将每个session对象放在每个函数内，现在使用scoped_session可以将其放在全局

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Book, User
from flask import Flask
from sqlalchemy.orm import scoped_session
from threading import Thread

engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置），-1是不回收
)
app = Flask(__name__)
Session = sessionmaker(bind=engine)

session = scoped_session(Session)

def task():
    book_obj = Book(name='西游记')
    session.add(book_obj)
    session.commit()
    session.close()
    return '增加成功'

for i in range(3):
    t = Thread(target=task)
    t.start()

那么scoped_session底层如何实现的呢？首先在代码

session = scoped_session(Session)

中，session是一个类scoped_session的对象，而不是原来的Session类对象了。这个类使用了一个类装饰器来讲属性注册在了这个类上，所以我们拿到了session对象可以有类Session的所有方法：

3.3 类装饰器

在类scoped_session中没有相应的方法，是通过类装饰器对其加上了方法。类装饰器是通过在装饰器中给类添加了属性和方法，对象也可以调用。和函数装饰器不同的是传入的参数是一个类，所以func就是类名Student，func()就是类Student的对象，通过对象点的方式给对象设置值：

def index():
    print('index执行了')

def wrapper(func):
    def inner(*args,**kwargs):
        res = func()
        res.age=22
        res.index=index
        return res
    return inner

@wrapper
class Student:
    name = 'max'

stu1 = Student()
print(stu1.name)
print(stu1.age)
stu1.index()

4.基本增删查改

1.增加有两个，add()和add_all()
add()括号内的参数是对象
add_all()括号内的参数是一个列表，里面可以放多个对象

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Book, User
from sqlalchemy.orm import scoped_session
'''engine其他参数有默认值，直接传地址用户名密码就可以'''
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8",)

Session = sessionmaker(bind=engine)
session = scoped_session(Session)
user_obj = User(name='marry',email='123@qq.com')
user_obj1 = User(name='qqq',email='222@qq.com')
user_obj2 = User(name='www',email='111@qq.com')
# session.add(user_obj)
session.add_all([user_obj1,user_obj2])

session.commit()
session.close()

注：add_all()参数的列表中可以放多个模型表的对象，会把数据增加到不同的表里：

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Book, User
from sqlalchemy.orm import scoped_session

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8",)

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

user_obj = User(name='eee',email='333@qq.com')
book_obj = Book(name='水浒传')
session.add_all([user_obj,book_obj])
session.commit()
session.close()

2.基本查询：
session.query()括号内写表名，可以写多个表名

filter()括号内必须写表达式：也就是含有==、>、<、>=、<=、!=的式子
filter_by()括号内必须要写等式，只写一个等于号

all()和first()：如果有多个结果需要加all()，返回结果是个列表，支持索引。只有一个结果时需要加first()

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Book, User
from sqlalchemy.orm import scoped_session

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8",)

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

user_obj = session.query(User).filter(User.name=='eee').first()
'''由于在表模型中定义了__str__，所以结果返回了用户的name'''
print(user_obj)  # eee

'''查询用户id大于等于9的用户'''
user_obj = session.query(User).filter(User.id >= 9).all()
'''由在表模型在定义了__repr__，所以列表中直接返回了每个用户的name'''
print(user_obj)  # [www, eee]
'''查询name不是eee的用户'''
user_obj = session.query(User).filter(User.name != 'eee').all()
print(user_obj)  # [max, henry, 小丽, 小红, marry, qqq, www]

'''filter_by需要跟等式'''
user_obj = session.query(User).filter_by(name='www').first()
print(user_obj)  # www


#  查询不需要做commit和close操作

3.删除：删除必须首先查到数据，才能改。删除只需要将查出来的数据直接点delete()即可，不需要点all()或者first()，返回值是影响的行数。增加、删除、修改都需要加session.commit()，只有查询不需要

res = session.query(User).filter(User.name=='eee').delete()
print(res)  # 1
session.commit()
session.close()

4.修改：修改有两种方式
方式一：直接查出数据对象，不点all()或者first()的情况下.update()中穿一个字典，会改掉相应的字段：

res = session.query(User).filter(User.name=='www').update({'name':'www1'})
session.commit()
session.close()

方式二：先查找到数据对象，在通过对象点字段名的方式修改，再用session.add()修改。session.add()内的对象如果能点到id的话，就执行修改操作；点不到则执行新增操作

user_obj = session.query(User).filter(User.name=='www1').first()
user_obj.name = 'www'
session.add(user_obj)

修改还可以直接在字段上进行修改：
在字符串类型的字段上进行拼接：

'''在id>6的用户对象中的name字段后面拼接字符串222'''
session.query(User).filter(User.id > 6).update({User.name:User.name+'222'},synchronize_session=False)

对整形字段上进行加减：
session.query(User).filter(User.age > 20).update({User.age:User.age+10},synchronize_session="evaluate")

5.高级查询(单表查询)

1.查询指定字段：在query参数中指定查询字段，label相当于原生sql中的起别名：

res = session.query(User.name.label('xxx'),User.email).all()
print(res)  # [('henry', '2@qq.com'), ('marry222', '123@qq.com')]

2.查询语句最后只要不点all()或者first()，那么执行的结果就是原生sql：

res = session.query(User.name.label('xxx'),User.email)
print(res)  # SELECT users.name AS xxx, users.email AS users_email FROM users

3.filter中可以跟多个条件，需要用逗号隔开。filter括号内跟的是表达式，filter_by括号内跟的是参数：

res = session.query(User).filter(User.id>2,User.name=='qqq222').first()
print(res)  # qqq222
res = session.query(User).filter_by(name='www222',email='111@qq.com').first()
print(res)  # www222

4.查询条件使用占位符：

from sqlalchemy.sql import text
res = session.query(User).filter(text("id>:value or name=:name")).params(value=7,name='henry').all()
print(res)  # [henry, qqq222, www222]

5.在sqlalchemy中用from_statement写原生sql，注意原生sql中表名需要写成models中用__tablename__定义的表名：

from sqlalchemy.sql import text
res = session.query(User).from_statement(text('select * from users where id=:id')).params(id=7).all()
print(res)  # [marry222]
"""
django执行原生sql：用raw(),在括号内编写sql语句:
res = models.User.objects.raw('select * from app01_user')
print(list(res))  # [<User: User object (1)>, <User: User object (2)>]
"""

6.筛选条件中加入范围：

res = session.query(User).filter(User.id.between(7,9)).all()
print(res)  # [marry222, qqq222, www222]

7.查询字段的值在指定列表中的数据：

# print(res)  # [marry222, qqq222, www222]
res = session.query(User).filter(User.id.in_([3,4,6])).all()
print(res)  # [henry, 小丽, 小红]

8.与或非：~代表非

res = session.query(User).filter(~User.id.in_([2,3,4,5,6])).all()
print(res)  # WHERE (users.id NOT IN (__[POSTCOMPILE_id_1]))  不加all()时打印原生sql
print(res)  #[max, marry222, qqq222, www222]

6.sqlalchemy写原生sql

方式一：由于多线程可能影响到数据安全，conn对象应该写在函数内部

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8", )

conn = engine.raw_connection()
cursor=conn.cursor()
cursor.execute('select * from books')
print(cursor.fetchall())  # ((1, 'max'), (2, '红楼梦'), (3, '西游记'), (4, '西游记'), (5, '西游记'), (6, '水浒传'))

方式二：

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

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8")

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

# 查询
cursor = session.execute(text('select * from users'))
print(cursor.fetchall())

# 新增
cursor = session.execute(text('insert into books(name) values (:name)'),params={'name':'骆驼祥子'})
session.commit()
print(cursor.lastrowid)  # 7  新增这条数据的id

7.django写原生sql

利用fbv来举例：
views.py:

def index(request):
    students = Student.objects.raw('select * from app01_student where id=1')
	'''拿到的结果是rawqueryset，也可以通过索引取值'''
    print(students)  # <RawQuerySet: select * from app01_student where id=1>
    print(students[0].name)  # marry
    return HttpResponse('ddd')

urls.py:

urlpatterns = [
    path('admin/', admin.site.urls),
    path('index',views.index)
]

8.一对多关系创建

一对一：本身是一个表，拆成两个表，做一对一的关联，只不过关联字段唯一
一对多：关联字段写在多的一方
多对多：需要建立中间表，本质也是一对多

一对一表关系创建：

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 Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer,primary_key=True)
    caption = Column(String(32),default='篮球')


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),index=True,nullable=True)
    '''关联字段写在多的一方，跟hobby表中的id做关联'''
    hobby_id = Column(Integer,ForeignKey('hobby.id'))
    '''backref做反向查询'''
    hobby = relationship('Hobby',backref='pers')

    def __repr__(self):
        return self.name

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user",)  # 需要手动创建一个名为name的库
Base.metadata.create_all(engine)

一对多新增：
方式一：首先需要在表模型中写入relationship字段，该字段和数据库无关，不会在表中显示该字段，作用是用来做反向查询，和django中的related_name类似。同时新增hobby数据和person数据：
models.py:

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 Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer,primary_key=True)
    caption = Column(String(32),default='篮球')


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),index=True,nullable=True)
    '''关联字段写在多的一方，跟hobby表中的id做关联'''
    hobby_id = Column(Integer,ForeignKey('hobby.id'))
    '''backref做反向查询，这个字段和数据库无关'''
    hobby = relationship('Hobby',backref='pers')

    def __repr__(self):
        return self.name

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user",)  # 需要手动创建一个名为name的库
Base.metadata.create_all(engine)

flask.py:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from models1 import Hobby, Person

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

Hobby(caption='soccer')
hobby_obj = Hobby(caption='soccer')
person=Person(name='小红',hobby=hobby_obj)
session.add_all([hobby_obj,person])
session.commit()
session.close()

方式二：如果外键字段已经存在于表中，首先查出来，用hobby_id新增：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from models1 import Hobby, Person

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user?charset=utf8")
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

Hobby(caption='soccer')
hobby_obj = session.query(Hobby).filter(Hobby.caption=='soccer').first()
person=Person(name='小红',hobby_id=hobby_obj.id)
session.add_all([hobby_obj,person])
session.commit()
session.close()

9.一对多关系的查询(基于对象的跨表查)

flask中也有正反向查询。从外键字段开始查询为正向查询，对象点外键字段拿到是一个对象。反向查询点backref指定的值拿到该字段所在表的对象，类似于django中的related_name：

'''正向查询'''
person_obj = session.query(Person).filter(Person.id==6).first()
print(person_obj.hobby)  # soccer 因为Hobby表中重写了__str__，所以名字是caption的值，其实它是个对象
print(person_obj.hobby.id)  # 6

'''反向查询'''
hobby_obj = session.query(Hobby).filter(Hobby.id==6).first()
print(hobby_obj.pers)  # [小红, 小红]

10.多对多关系表建立

10.1 创建表

多对多关系需要手动创建中间表，relationship是为了方便方向查询

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 Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)
    '''这个字段不会在表中显示，只是为了方便查询，只需要写在一个表中即可，写在哪个表中从哪个表开始查，就是正向查询'''
    girls = relationship('Girl',secondary='boy2girl',backref='boys')

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name


class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name

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'))

if __name__ == '__main__':
    engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user")
    Base.metadata.create_all(engine)

10.2 基于对象插入数据

'''正向插入'''
'''relationship字段在表Boy中，相当于是正向查询，直接点外键字段添加数据'''
boy_obj = Boy(name='彭于晏')
boy_obj.girls = [Girl(name='qqq'),Girl(name='www')]
session.add(boy_obj)
'''反向插入'''
girl_obj = Girl(name='迪丽热巴')
'''从Girl表到Boy表是反向查询，直接点Boy表中的relationship字段中的backref属性添加多对多数据'''
girl_obj.boys = [Boy(name='aaa'),Boy(name='sss')]
session.add(girl_obj)
session.commit()

11.多对多关系的查询(基于对象的跨表查)

'''基于对象的正向查询，直接点外键，得到的结果是一个列表'''
boy_obj = session.query(Boy).filter(Boy.id==2).first()
print(boy_obj.girls)  # [qqq, www]

'''基于对象的反向查询，直接点backref对应的值，得到的结果是一个列表'''
girl_obj = session.query(Girl).filter(Girl.id==4).first()
print(girl_obj.boys)  # [aaa, sss]

12.基于连表的跨表查询

django中不支持left join 和right join。没有外键关系也可以连表
models.py

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 Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer,primary_key=True)
    caption = Column(String(32),default='篮球')

    def __str__(self):
        return self.caption

    def __repr__(self):
        return self.caption


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),index=True,nullable=True)
    '''关联字段写在多的一方，跟hobby表中的id做关联'''
    hobby_id = Column(Integer,ForeignKey('hobby.id'))
    '''backref做反向查询，这个字段和数据库无关'''
    hobby = relationship('Hobby',backref='pers')

    def __str__(self):
        return self.caption

    def __repr__(self):
        return self.name

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/user",)  # 需要手动创建一个名为name的库
Base.metadata.create_all(engine)

flask.py：

''''''
res = session.query(Person,Hobby).filter(Person.hobby_id==Hobby.id).all()
print(res)  # [(max, 篮球), (小红, soccer), (小红, soccer)]

'''连表查询内连接'''
res = session.query(Person.name,Hobby.caption).join(Hobby).all()
print(res)  # [('max', '篮球'), ('小红', 'soccer'), ('小红', 'soccer')]

'''连表查询左连接(flask中没有右连接，实现右连接只要将.join前后两个表换位置即可)'''
res = session.query(Person.name,Hobby.caption).join(Hobby,Person.hobby_id==Hobby.id,isouter=True).all()
print(res)  # [('max', '篮球'), ('小红', 'soccer'), ('小红', 'soccer')]