Flask框架的学习——08—(数据库过滤条件、外键及其四种约束、表关系、反向查询模型属性)
程序员文章站
2022-06-02 16:28:48
...
1、数据库过滤条件
过滤是数据提取的一个很重要的功能,以下对一些常用的过滤条件进行解释,并且这些过滤条件都是只能通过filter方法实现的。
- equals
- not equals
- like
- in
- not in
- is null
- is not null
- and
- or
# -*- encoding: utf-8 -*-
from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 127.0.0.1
HOSTNAME = "localhost"
DATABASE = "python_01"
PORT = 3306
USERNAME = "root"
PASSWORD = "root"
DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
# 创建引擎并生成Base类
engine = create_engine(DB_URL)
Base = declarative_base(engine)
# 必须继承Base类 Article属于模型
class Article(Base):
__tablename__ = "article2" # 在python_01数据库中创建article2这个表
id = Column(Integer, autoincrement=True, primary_key=True)
title = Column(String(50), nullable=False)
price = Column(Float, nullable=Float)
# __repr__(self) 和__str__类似功能
def __str__(self):
return "Article(title:{},price:{})".format(self.title, self.price)
# 模型映射到数据库中
Base.metadata.create_all()
Session = sessionmaker(bind=engine)
seeeion = Session()
# for i in range(6):
# article = Article(title="title%s" % i, price=random.randint(1, 50))
# seeeion.add(article)
# seeeion.commit()
# articles = seeeion.query(Article).all()
# print(articles)
# for article in articles:
# print(article.title)
# equals 等于
results = seeeion.query(Article).filter(Article.title == 'title0').all()
print(results) # [<__main__.Article object at 0x000001E2C7EAFC18>]
for result in results:
print(result) # Article(title:title0,price:29.0)
# not equals 不等于
results = seeeion.query(Article).filter(Article.title != 'title0').all()
# print(results)
for result in results:
print(result)
print('*'*40)
# like 模糊查询 %代表任意字符 %title%代表包含有title字符的数据,无论前后各有多少字符
results = seeeion.query(Article).filter(Article.title.like('%title%')).all()
# print(results)
for result in results:
print(result)
print('#' * 40)
# .in_(['title0', 'title4'])代表查找具有'title0'和'title4'数据的记录 传入进去的是列表类型
results = seeeion.query(Article).filter(Article.title.in_(['title0', 'title4'])).all()
# print(results)
for result in results:
print(result)
# not in _in/__in代表私有方法
results = seeeion.query(Article).filter(Article.title.notin_(['title0', 'title4'])).all() # not in 主要采用方式
# results = seeeion.query(Article).filter(~Article.title.in_(['title0', 'title4'])).all() # not in的另一种方式
# print(results)
for result in results:
print(result)
print('@' * 40)
# None和空不是一样的 None没有开辟内存空间,空开辟了内存空间 数据库中是Null Flask中是None
# results = seeeion.query(Article).filter(Article.title != None).all()
results = seeeion.query(Article).filter(Article.price == None).all()
# print(results)
for result in results:
print(result)
print('*' * 40)
# and 交集
# results = seeeion.query(Article).filter(Article.title == 'title1', Article.price == 11).all() # 这样的方便简洁
from sqlalchemy import and_
results = seeeion.query(Article).filter(and_(Article.title == 'title1', Article.price == 11)).all()
# results = seeeion.query(Article).filter(Article.title == 'title1').filter(Article.price == 11).all() # 另一种形式
# print(results)
for result in results:
print(result)
print('¥' * 40)
# or 并集
from sqlalchemy import or_
results = seeeion.query(Article).filter(or_(Article.title == 'title1', Article.price == 29)).all()
for result in results:
print(result)
2、外键及其四种约束
外键
在Mysql中,外键可以让表之间的关系更加紧密。而SQLAlchemy同样也支持外键。通过ForeignKey类来实现,并且可以指定表的外键约束。
数据库引擎中MyISAM不支持外键,引擎Innodb支持外键
from sqlalchemy import create_engine, Column, Integer, String, Float, TEXT, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 127.0.0.1
HOSTNAME = "localhost"
DATABASE = "demo0423"
PORT = 3306
USERNAME = "root"
PASSWORD = "root"
DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
# 创建引擎并生成Base类
engine = create_engine(DB_URL)
Base = declarative_base(engine)
# user
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50))
# 必须继承Base类 Article属于模型
class Article(Base):
__tablename__ = "article" # 在python_01数据库中创建article2这个表
id = Column(Integer, autoincrement=True, primary_key=True)
title = Column(String(50))
content = Column(TEXT, nullable=False)
# 外键 数据类型Integer根据外键的数据类型来写,这里是id的数据类型 ForeignKey('user.id')里面参数是表中的字段
# 数据库引擎中MyISAM不支持外键,引擎Innodb支持外键
uid = Column(Integer, ForeignKey('user.id'))
# 模型映射到数据库中
Base.metadata.drop_all()
Base.metadata.create_all()
# 简写
# Session = sessionmaker(bind=engine)
# seeeion = Session()
session = sessionmaker(bind=engine)()
user = User(username='abc')
session.add(user)
session.commit()
article = Article(title='python', content='xxx', uid=1) # 这里的uid如果传入一个不存在的外键,会报错,如果没有保错说明数据库引擎不是InnoDB
session.add(article)
session.commit()
外键约束有以下几项:
- RESTRICT:父表数据被删除,会阻止删除。默认就是这一项。(默认不允许删除)
- NO ACTION:在MySQL中,同RESTRICT。
- CASCADE:级联删除。(相关数据一起删除)
- SET NULL:父表数据被删除,子表数据会设置为NULL。(外键会变为NULL)
# 默认是RESTRICT
uid = Column(Integer, ForeignKey('user.id', ondelete="RESTRICT"))
# ondelete="CASCADE"外键设置为CASCADE 默认是RESTRICT
uid = Column(Integer, ForeignKey('user.id', ondelete="CASCADE"))
uid = Column(Integer, ForeignKey('user.id', ondelete="SET NULL"))
ORM 删除user表中的数据 ORM同样遵守外键约束
# ORM 删除user表中的数据 ORM同样遵守外键约束
user = session.query(User).first()
session.delete(user)
session.commit()
外键查询
# user
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50))
def __str__(self):
return "User{username:%s}" % self.username
# 普通查询
article = session.query(Article).first()
uid = article.uid
print(uid)
user = session.query(User).get(uid)
print(user)
# 外键查询可以简化成下面的语句
user = session.query(User).filter(User.id == Article.uid).first()
print(user)
3、表关系
表之间的关系存在三种:一对一、一对多、多对多。而SQLAlchemy中的ORM也可以模拟这三种关系。因为一对一其实在SQLAlchemy中底层是通过一对多的方式模拟的。
一对多
拿之前的User表为例,假如现在要添加一个功能,要保存用户的邮箱帐号,并且邮箱帐号可以有多个,这时候就必须创建一个新的表,用来存储用户的邮箱,然后通过user.id来作为外键进行引用。
from sqlalchemy import create_engine, Column, Integer, String, Float, TEXT, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship # 一对多关系用到relationship
# 127.0.0.1
HOSTNAME = "localhost"
DATABASE = "demo0423"
PORT = 3306
USERNAME = "root"
PASSWORD = "root"
DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
# 创建引擎并生成Base类
engine = create_engine(DB_URL)
Base = declarative_base(engine)
# 一个人对应多篇文章
class Article(Base):
__tablename__ = "article" # 在python_01数据库中创建article2这个表
id = Column(Integer, autoincrement=True, primary_key=True)
title = Column(String(50))
content = Column(TEXT, nullable=False)
uid = Column(Integer, ForeignKey('user.id', ondelete="SET NULL"))
author = relationship("User") # 建立author与User模型的关系 且不映射到数据库中
def __str__(self):
return "author{title:%s}" % self.title
# 一对多 一个人对应多篇文章
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50))
articles = relationship("Article") # 对应了Article模型 建立与Article模型联系 不映射到数据库中
def __str__(self):
return "User{username:%s}" % self.username
# Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(bind=engine)()
# 查询 根据文章查询作者
article = session.query(Article).first()
print(article) # <__main__.Article object at 0x000002495A456C88>
print(article.uid) # 1
print(article.author) # 关联了author和articles
article = session.query(Article)
print(article)
print("0"*50)
# 根据作者查询文章
user = session.query(User).first()
print(user.articles) # 关联了author和articles
for data in user.articles:
print(data)
# 添加单条数据
user = User(username="xxxx007")
article = Article(title='java', content='xxxxxabc')
article.author = user # 要将添加的article.author与user相互匹配,uid外键才能建立联系
session.add(article) # 提交建立起联系的article 外键在表article中
session.commit()
# 添加多条记录
article1 = Article(title='java1', content='1111xxxabc')
article2 = Article(title='java2', content='22222xxxabc')
article3 = Article(title='java3', content='33333xxxabc')
user1 = User(username="yyyyyyy")
article1.author = user
article2.author = user
article3.author = user1
session.add(article1)
session.add(article2)
session.add(article3)
session.commit()
反向查询模型属性
# 一个人对应多篇文章
class Article(Base):
__tablename__ = "article" # 在python_01数据库中创建article2这个表
id = Column(Integer, autoincrement=True, primary_key=True)
title = Column(String(50))
content = Column(TEXT, nullable=False)
uid = Column(Integer, ForeignKey('user.id', ondelete="SET NULL"))
# 反向查询属性的时候,查询author属性
# author = relationship("User") # 建立author与User模型的关系 且不映射到数据库中
def __str__(self):
return "author{title:%s}" % self.title
# 一对多 一个人对应多篇文章
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50))
# backref='author'反向查询属性
articles = relationship("Article", backref='author') # 对应了Article模型 建立与Article模型联系 不映射到数据库中
def __str__(self):
return "User{username:%s}" % self.username
# 反向查询
article = session.query(Article).all()
for i in article:
print(i)