Flask - SQLAlchemy | Flask-SQLAlchemy

SQLAlchemy

SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

• 安装

>: pip3 install sqlalchemy

• 创建表

import datetime
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 Users(Base):
    __tablename__ = 'users'  # 数据库表名称
    id = Column(Integer, primary_key=True)  # id 主键
    name = Column(String(32), index=True, nullable=False)  # name列，索引，不可为空
    #email = Column(String(32), unique=True)
    age = Column(Integer,default=0)
    #datetime.datetime.now不能加括号，加了括号，以后永远是当前时间
    ctime = Column(DateTime, default=datetime.datetime.now)
    #extra = Column(Text, nullable=True)

    # __table_args__ = (
    #     UniqueConstraint('id', 'name', name='uix_id_name'), #联合唯一
    #     Index('ix_id_name', 'name', 'email'), #索引
    # )
    def __repr__(self):
        return self.name

class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)  # 主键
    caption = Column(String(50), default='篮球')

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    # hobby指的是tablename而不是类名，
    hobby_id = Column(Integer, ForeignKey("hobby.id"))  # 外键
    # relationship跟数据库无关，不会新增字段，只用于快速链表操作
    # 类名，backref用于反向查询,uselist=False
    hobby = relationship('Hobby', backref='pers')
    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'))

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


class Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)

    # 与生成表结构无关，仅用于查询方便,放在哪个单表中都可以
    girl = relationship('Girl', secondary='boy2girl', backref='boys')

# 根据类创建数据库表
def init_db():
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.create_all(engine)

# 根据类删除数据库表
def drop_db():
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/库名?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    #drop_db()  # 删除表
    init_db()  # 生成表
    
'''
1.Base = declarative_base()  实例化对象Base
2.model类继承Base
3.model类中用 __tablename__ = '库名' 声明表所在库
4.表关系 ForeignKey :
    - 一对多查询字段: hobby = relationship('被关联表名', backref='pers')
            hobby用于正向, pers用于反向
    - 要手动创建第三张表, girl = relationship('关系表名', secondary='第三张表名', backref='boys')
            girl用于正向, boys用于反向
5.创建生成表与删除表的两个函数
'''

• 增删改查

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

from models import Users
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/库名", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

session = Session()

# ################ 增加 ################
# 单增
obj1 = Users(name="xionger",age=40)
session.add(obj1)
# 群增
session.add_all([
    Users(name="hanmm"),
    Users(name="lilei"),
    #Hosts(name="c1.com"),
])
session.commit()


# ################ 删除 ################

session.query(Users).filter(Users.id == 5).delete()
session.commit()

# ################ 修改 ################

#传字典
session.query(Users).filter(Users.id ==4 ).update({"age" : 30,})
#类似于django的F查询
session.query(Users).filter(Users.id == 3).update({Users.name: Users.name + "sb"}, synchronize_session=False)
session.query(Users).filter(Users.id == 7).update({"age": Users.age + 16}, synchronize_session="evaluate")
session.commit()

# ################ 查询 ################

r1 = session.query(Users).all()
#只取age列，把name重命名为xx
r2 = session.query(Users.name.label('sb'), Users.age).filter(Users.id==8).first()
#filter传的是表达式，filter_by传的是参数
r3 = session.query(Users).filter(Users.name == "jason").all()
r4 = session.query(Users).filter_by(name='jason').all()
r5 = session.query(Users).filter_by(name='lqz').first()
#:value 和:name 相当于占位符，用params传参数
r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()
#自定义查询sql
r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()


#增，删，改都要commit()
session.close()

• 单表查询

import time
import threading
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text

from models import Users
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

session = Session()
#　条件
ret = session.query(Users).filter_by(name='xionger').all()
#表达式，and条件连接
ret = session.query(Users).filter(Users.id > 7, Users.name == 'hanmm').all()
print(ret)
ret = session.query(Users).filter(Users.id.between(3, 5), Users.name == 'hanmm').all()
print(ret)
#注意下划线
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
# print(ret)
#~非，除。。外
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
# print(ret)

# #二次筛选
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='jason'))).all()
# print(ret)
from sqlalchemy import and_, or_
#or_包裹的都是or条件，and_包裹的都是and条件
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id >6 , Users.name == 'jason')).all()
print(ret)
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.age != ""
    )).all()


# 通配符，以e开头，不以e开头
ret = session.query(Users).filter(Users.name.like('_q%')).all()
print(ret)
ret = session.query(Users).filter(~Users.name.like('j%')).all()
print(ret)

# 限制，用于分页，区间
ret = session.query(Users)[1:9]
print(ret)

# # 排序，根据name降序排列（从大到小）
ret = session.query(Users).order_by(Users.name.desc())
print(ret)


#第一个条件重复后，再按第二个条件升序排
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc())
print(ret)

# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.name).all()
print(ret)
#分组之后取最大id，id之和，最小id
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()
print(ret)
#haviing筛选
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2)
print(ret)

• 一对多查询

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

from models import Users, Hobby, Person

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 添加

session.add_all([
    Hobby(caption='乒乓球'),
    Hobby(caption='羽毛球'),
    Person(name='张三', hobby_id=1),
    Person(name='李四', hobby_id=2),
])

person = Person(name='张九', hobby=Hobby(caption='姑娘'))
session.add(person)
#添加二
hb = Hobby(caption='人妖')
hb.pers = [Person(name='文飞'), Person(name='博雅')]
session.add(hb)

session.commit()
"""

# 使用relationship正向查询
"""
v = session.query(Person).first()
print(v.name)
print(v.hobby.caption)
"""

# 使用relationship反向查询
"""
v = session.query(Hobby).first()
print(v.caption)
print(v.pers)

#方式一，自己链表
person_list=session.query(Person).join(Hobby,isouter=True)
print(person_list)
person_list=session.query(Person,Hobby).join(Hobby,isouter=True)
print(person_list)
for row in person_list:
    # print(row.name,row.caption)
    print(row[0].name,row[1].caption)
#
# #方式二：通过relationship
#
person_list=session.query(Person).all()
for row in person_list:
    print(row.name,row.hobby.caption)
#查询喜欢Hobby.id==1的所有人
obj=session.query(Hobby).filter(Hobby.id==1).first()
persons=obj.pers
print(persons)
session.close()

#如果没有建立外键
ret = session.query(Person).join(Hobby,Person.nid==Hobby.id, isouter=True)
# print(ret)

• 多对多查询

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

from models import Girl, Boy2Girl, Boy

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 添加

session.add_all([
    Girl(name='c1.com'),
    Girl(name='c2.com'),
    Boy(hostname='A组'),
    Boy(hostname='B组'),
])
session.commit()

s2g = Boy2Girl(girl_id=1,boy_id=1)  # girl_id, boy_id 是外键字段
session.add(s2g)
session.commit()

gp = Girl(name='C组')
gp.boys = [Boy(hostname='c3.com'),Boy(hostname='c4.com')]
session.add(gp)
session.commit()

ser = Boy(hostname='c6.com')
ser.girl = [Girl(name='F组'),Girl(name='G组')]
session.add(ser)
session.commit()

# 使用relationship正向查询
v = session.query(Boy).first()
print(v.hostname)
print(v.girl)

# 使用relationship反向查询
v = session.query(Girl).first()
print(v.name)
print(v.boys)

session.close()

 

scoped_session实现线程安全

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

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

"""
# 线程安全，基于本地线程实现每个线程用同一个session
# 特殊的：scoped_session中有原来方法的Session中的一下方法：

public_methods = (
    '__contains__', '__iter__', 'add', 'add_all', 'begin', 'begin_nested',
    'close', 'commit', 'connection', 'delete', 'execute', 'expire',
    'expire_all', 'expunge', 'expunge_all', 'flush', 'get_bind',
    'is_modified', 'bulk_save_objects', 'bulk_insert_mappings',
    'bulk_update_mappings',
    'merge', 'query', 'refresh', 'rollback',
    'scalar'
)
"""
#scoped_session类并没有继承Session,但是却又它的所有方法
session = scoped_session(Session)
# ############# 执行ORM操作 #############
obj1 = Users(name="alex1")
session.add(obj1)

# 提交事务
session.commit()
# 关闭session
session.close()

 

Flask-SQLAlchemy

• 安装

>: pip install flask-migrate

python3 manage.py db init 初始化：只执行一次

python3 manage.py db migrate 等同于 makemigartions 数据库迁移记录
python3 manage.py db upgrade 等同于migrate 数据库迁移

结构目录  t1ku