Python学习日记--Flask的数据库操作(二)

知识点

1. 常用的过滤条件
2. 添加外键
3. 外键的约束
4. 表关系

过滤条件

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Float
from sqlalchemy import and_, or_
import random

# 数据库配置
HOSTNAME = "localhost"
DATANAME = "demo0417"
PORT = 3306
USERNAME = "root"
PASSOWRD = "root"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSOWRD, HOSTNAME, PORT, DATANAME)
engin = create_engine(DB_URL)

Base = declarative_base(engin)


class Article(Base):
    __tablename__ = "article2"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    price = Column(Float, nullable=False)

    def __str__(self):
        return "Article(title:{}, price:{})".format(self.title, self.price)


# Base.metadata.create_all()
Session = sessionmaker(bind=engin)
session = Session()


# for i in range(6):
#     article = Article(title="titile%d" % i, price=random.randint(1, 50))
#     session.add(article)
# session.commit()

# eq
# result = session.query(Article).filter(Article.title == "title1").all()

# not eq
# result = session.query(Article).filter(Article.title != "title1").all()

# like
# result = session.query(Article).filter(Article.title.like("%it%"))

# in, 要传入列表
# result = session.query(Article).filter(Article.title.in_(["title0", "title3"])).all()

# not in, 要传入列表
# result = session.query(Article).filter(Article.title.notin_(["title0", "title3"])).all()
# 还有另一种写法,在前面在~,取反的意思
# result = session.query(Article).filter(~Article.title.in_(["title0", "title3"])).all()

# is null
# result = session.query(Article).filter(Article.title == None).all()
# 另一种写法
# result = session.query(Article).filter(Article.title.is_(None)).all()

# not is null
# result = session.query(Article).filter(Article.title != None).all()
# 另一种写法
# result = session.query(Article).filter(Article.title.isnot(None)).all()

# and
# result = session.query(Article).filter(Article.title == "title1", Article.id == "1").all()
# 另一种写法, 这种方法需要导入and_模块
# result = session.query(Article).filter(and_(Article.title == "title1", Article.id == "1")).all()
# 还有一种写法,写多个filter
# result = session.query(Article).filter(Article.title == "title1").filter(Article.id == "1").all()

# or 需要导入or_模块
result = session.query(Article).filter(or_(Article.title == "title1", Article.title == "title2")).all()

for data in result:
    print(data)

外键

在Mysql中，外键可以让表之间的关系更加紧密。而SQLAlchemy同样也支持外键。通过ForeignKey类来实现，并且可以指定表的外键约束

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Float, TEXT, ForeignKey
from sqlalchemy import and_, or_
import random


# 数据库配置
HOSTNAME = "localhost"
DATANAME = "demo0424"
PORT = 3306
USERNAME = "root"
PASSOWRD = "root"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSOWRD, HOSTNAME, PORT, DATANAME)
engin = create_engine(DB_URL)

Base = declarative_base(engin)

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50))


class Article(Base):
    __tablename__ = "article"

    id = Column(Integer, primary_key=True, autoincrement=True)
    titile = Column(String(50))
    content = Column(TEXT, nullable=False)

    uid = Column(Integer, ForeignKey("user.id"))


# Base.metadata.drop_all()  # 删除表
Base.metadata.create_all()  # 创建表

Session = sessionmaker(bind=engin)
session = Session()

• 创建外键时，外键的数据类型一定要与关联字段的数据类型一致。
• 有些时候Mysql的引擎可能会默认为MyISAM，但因为MyISAM不支持外键，要修改为InnoDB，可在配置文件中修改。
  • 修改Mysql目录的my.ini
  • 将default-storage-engine= MyISAM 改为 default-storage-engine=Innodb，然后重启Mysql

外键的约束

外键约束有以下几项：

1. RESTRICT：父表数据被删除，会阻止删除。默认就是这一项。
2. NO ACTION：在MySQL中，同RESTRICT。
3. CASCADE：级联删除。
4. SET NULL：父表数据被删除，子表数据会设置为NULL。

在代码中，添加外键约束可以使用ondelete参数

class Article(Base):
    __tablename__ = "article"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    content = Column(TEXT, nullable=False)
    # 添加外键约束，ondelete
    uid = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"))

外键的查询

# 外键的查询
user = session.query(Article).filter(User.id == Article.id).first()
print(user)

表关系

表之间的关系存在三种：一对一、一对多、多对多。而SQLAlchemy中的ORM也可以模拟这三种关系。因为一对一其实在SQLAlchemy中底层是通过一对多的方式模拟的。

一对多

两个表建立关系需要用到relationship模块,然后在两个类中加上relationship

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, TEXT, ForeignKey
from sqlalchemy.orm import relationship  # 建立一对多的关系


# 数据库配置
HOSTNAME = "localhost"
DATANAME = "demo0422"
PORT = 3306
USERNAME = "root"
PASSOWRD = "root"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSOWRD, HOSTNAME, PORT, DATANAME)
engin = create_engine(DB_URL)

Base = declarative_base(engin)

class Article(Base):
    __tablename__ = "article"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    content = Column(TEXT, nullable=False)

    uid = Column(Integer, ForeignKey("user.id", ondelete="SET NULL"))
    #与User表建立关系
    author = relationship("User")

    def __str__(self):
        return "Article(id:{}, title:{}, content:{})".format(self.id, self.title, self.content)

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50))
	#与Article表建立关系
    articles = relationship("Article")

# Base.metadata.drop_all()  # 删除表
Base.metadata.create_all()  # 创建表

Session = sessionmaker(bind=engin)
session = Session()

建立关系之后，就可以通过Article表查询User，反之亦然。
例如，通过文章查询作者

# 根据文章查询作者
article = session.query(Article).first()
print(article.author)

反过来：

# 根据作者查询文章
user = session.query(User).first()
article = user.articles
for data in article:
    print(data)

• 如果两个表没有外键关系，不能用关联查询
• 另外relationship 关联关系不会映射到数据库中，只在代码层面

反向查询(backref)

在上面的代码，两个模型类中都写了relationship，但只在一个类上写relationship然后加上backref参数，就可以实现方向查询。
下面的代码注释了Article的relationship，在User类中的relationship加上了backref参数。

class Article(Base):
    __tablename__ = "article"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    content = Column(TEXT, nullable=False)

    uid = Column(Integer, ForeignKey("user.id", ondelete="SET NULL"))

    # author = relationship("User")

    def __str__(self):
        return "Article(id:{}, title:{}, content:{})".format(self.id, self.title, self.content)

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50))

    articles = relationship("Article", backref="author")  # 添加backref,实现反向查询

    def __str__(self):
        return "User(id:{}, username:{})".format(self.id, self.username)

• 这里的backref="author"相当于在Article模型类中写入一句author = relationship(“User”)，因此Article模型类可以将author作为自己的属性来调用

两个表联合添加数据

添加单挑数据

user = User(username="connor2")
article = Article(title="python", content="AAAA")
article.author = user  # 相当于article的外键 = user的id

session.add(article)
session.commit()

• 这时的session.add只能传入article，如果传入user那就只会在user表上插入
• 并且也不能反过来user.articles = article, 会抛出异常

添加多条数据

# 两个表联合添加数据,多条数据
user = User(username="conn3")
article1 = Article(title="py2", content="BBBB")
article2 = Article(title="py3", content="cccc0")

article1.author = user
article2.author = user  # 两个文章绑定同一个作业

session.add(article1)
session.add(article2)
session.commit()