Web后端学习笔记 Flask (5) 数据库

MySql数据库安装：省略

SQLAlchemy介绍和基本使用：

数据库是开发网站的基础，，在Flask中，支持的数据库有：MySql，PostgreSql，SQLite，Redis，MongoDB来写原生的语言实现功能，也可以使用高级别的数据库抽象方式，例如SQLAlchemy或者MongoEngine这样的ORM.

除此之外，还需要在python中安装pymsql,以及SQLAlchemy

SQLAlchemy可以独立于Flask使用，对数据库进行操作，可以避免写sql语句。

连接的格式：dialect+driver://username:password@host:port/database

SQLAlchemy连接数据库实例代码：

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine

HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)

conn = engine.connect()

result = conn.execute("select 1")
print(result.fetchone())

数据库ORM介绍：

使用SQLAlchemy可以写原生的sql语句，也可以使用数据库ORM对数据库进行操作，ORM(Object Relation MAP)，及对象关系映射，即数据库表与模型对象之间的映射。将数据库中的表与python中的类映射起来。

这样主要是为了解决：

1. SQL语句重复利用率不高，且越复杂的sql语句条件越多，代码越长，会出现很多相近的SQL语句。

2. 很多SQL语句是在业务逻辑中拼出来的，如果有数据库需要更改，就要去修改大量的sql语句，容易造成错误及遗漏

3. 写sql时容易忽略web安全问题，给未来造成隐患。

ORM优点：

1. 易用性：使用ORM做数据库开发，可以有效减少重复SQL语句，写出来的模型更加的直观，清晰

2. 性能损耗小：ORM转换成底层数据库指令会有一些开销，但是通过不断的优化，这种性能开销已经非常小，且综合考虑易用性，这种损耗是值得的。（数据：性能损耗不足5%）

3. 设计灵活：可以轻松写出复杂的查询

4. 可移植性：SQLAlchemy封装了底层的数据库实现，支持多个关系数据库引擎，可以非常容易的切换数据库。

映射的步骤：

1. 用declarative_base根据engine创建一个ORM基类Base

2. 用Base作为基类创建自定义的ORM类，定义__tablename__属性来指定创建好的表名，

3. 创建属性来映射到表中的字段，所有的属性都应该为Column类型

4. 使用Base.metadata.create_all()来将模型映射到数据库中

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类


# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中

class Person(Base):
    __tablename__ = "person"     # 定义表的名字
    id = Column(Integer, primary_key=True, autoincrement=True)      # 创建字段
    name = Column(String(50))
    age = Column(Integer)


Base.metadata.create_all()

运行脚本，在cms数据库中创建表person:

将模型映射到数据库之后，即使改变了类中的属性重新运行代码，也不会重新映射模型。

SQLALchemy属性常用数据类型：

1. Column常用参数

default：默认值

nullable：是否可空

primary_key：是否为主键

unique：是否唯一

autoincrement：是否自动增长

name：该属性在数据库中的映射字段

onupdate: 如果数据库中某个字段被更新了，则onupdate=True的字段的值也会随着更新。（例如，一篇文章的更新时间的属性可以设置位onupdate=True, 当文章的标题被修改后，对应的更新时间也会发生变化）

2. 常用数据类型

Integer：整形，映射到数据库中是int类型

Float：浮点类型，32位，会出现精度丢失

Double：浮点类型，64位，会出现精度丢失

Boolean：传递True/False进去

DECIMAL：定点类型，不会出现精度丢失，有参数，DECIMAL(10,4), 存储10位长度的数字，精确到小数点后4位

enum：枚举类型， Enum('a', 'b', 'c')

Date：传递datetime.date()参数    (年月日)

DateTime：传递datetime.datetime()进去      （年月日时间）

Time：传递datetime.time()进去          （只有时间）

String：字符类型，使用时需要指定长度，区别于Text类型

Text：文本类型

LONGTEXT：长文本类型

# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    view_cnt = Column(Integer, default=0)
    title = Column(String(50), nullable=False)
    create_time = Column(DateTime, default=datetime.now)   # 创建时间


Base.metadata.drop_all()
Base.metadata.create_all()


artice = Article(title="Gone With Wind", view_cnt=11)
db_session.add(artice)
db_session.commit()      # 提交

可以查询数据库中的数据：

还可以使用一个方法：

artice1 = Article(title="Gone With Wind", view_cnt=11)
artice2 = Article(title="Gone With Wind", view_cnt=11)
db_session.add_all([artice1, artice2])
db_session.commit()      # 提交

# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    view_cnt = Column(Integer, default=0)
    title = Column(String(50), nullable=False)
    create_time = Column(DateTime, default=datetime.now)   # 创建时间
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    # create_time必须同时指定default值和onupdate值,在第一次插入数据的时候会有时间，
    # 在修改数据的时候也会更新时间


Base.metadata.drop_all()
Base.metadata.create_all()


artice1 = Article(title="Gone With Wind", view_cnt=11)
artice2 = Article(title="Jane Austing")
db_session.add_all([artice1, artice2])
db_session.commit()      # 提交


# article = db_session.query(Article).first()
# article.title = "Jane Aye"
# db_session.commit()   # 提交

数据展示：

现在对数据进行修改：

# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    view_cnt = Column(Integer, default=0)
    title = Column(String(50), nullable=False)
    create_time = Column(DateTime, default=datetime.now)   # 创建时间
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    # create_time必须同时指定default值和onupdate值,在第一次插入数据的时候会有时间，
    # 在修改数据的时候也会更新时间


# Base.metadata.drop_all()
# Base.metadata.create_all()
# 
# 
# artice1 = Article(title="Gone With Wind", view_cnt=11)
# artice2 = Article(title="Jane Austing")
# db_session.add_all([artice1, artice2])
# db_session.commit()      # 提交


article = db_session.query(Article).first()   # 查询数据
article.title = "Jane Aye"                    # 修改数据
db_session.commit()   # 提交

数据展示：

可以看到，被修改的数据，title字段发生了变化，同时，update_time字段的值也随着更新了,但是update_time字段必须指定默认值，在第一次插入数据的时候使用。

query函数可查询的数据：

1. query可用参数：

/模型对象，指定查找这个模型中的所有的对象

/模型中的属性，可以指定查找某个模型的其中几个属性

/聚合函数，func.count, func.avg, func.max, func.min，func.sum

首先，在数据库中插入一些测试数据

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import random


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    price = Column(Float, nullable=False)

    def __repr__(self):
        return "<article>" + str(self.id) + " | " + self.title + " | " + str(self.price)


# Base.metadata.drop_all()
# Base.metadata.create_all()

# 数据库中插入测试数据
# for cnt in range(10):
#     article = Article(title="article" + str(cnt), price=random.randint(25, 100))
#     db_session.add(article)
# db_session.commit()      # 提交


# 查询数据
articles = db_session.query(Article).all()   # 查询数据   # 返回article对象
for article in articles:
    print(article)

print("-"*50)
articles_title_price = db_session.query(Article.title, Article.price).all()   # 返回元组
for data in articles_title_price:
    print(data)


print("-"*50)
# 聚合函数
count = db_session.query(func.count(Article.id)).all()
print("共有{}条数据".format(count[0]))

average_price = db_session.query(func.avg(Article.price)).all()
print("平均值{}".format(average_price[0]))

filter函数实现条件过滤：

过滤是数据提取中的一个重要的功能，常用的过滤功能有：

1. equals

2. not equals

3. like    做匹配   ilike 不区分大小写

4. in

5. not in

6. is null

7. is not null

8. and

9. or

示例代码：

# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func
from sqlalchemy import and_, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import random


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    price = Column(Float, nullable=False)

    def __repr__(self):
        return "<article>" + str(self.id) + " | " + self.title + " | " + str(self.price)


# Base.metadata.drop_all()
# Base.metadata.create_all()

# 数据库中插入测试数据
# for cnt in range(10):
#     article = Article(title="article" + str(cnt), price=random.randint(25, 100))
#     db_session.add(article)
# db_session.commit()      # 提交


# equal
articles = db_session.query(Article).filter(Article.id == 1).all()   # 查询数据   # 返回article对象
for article in articles:
    print(article)


# not equal
print("-"*50)
articles = db_session.query(Article).filter(Article.title != "title2").all()   # 查询数据   # 返回article对象
for article in articles:
    print(article)


# like
print("-"*50)
articles_title_price = db_session.query(Article).filter(Article.title.like("shel%")).all()   # 返回元组
for data in articles_title_price:
    print(data)


# in_
print("-"*50)
articles = db_session.query(Article).filter(Article.title.in_(["shell", "article3", "article1"])).all()
for article in articles:
    print(article)

# not in
print("-"*50)
articles = db_session.query(Article).filter(~Article.title.in_(["shell", "article3", "article1"])).all()
for article in articles:
    print(article)

# is null
print("-"*50)
articles = db_session.query(Article).filter(Article.title is None).all()
for article in articles:
    print(article)


# is not null
print("-"*50)
articles = db_session.query(Article).filter(Article.title is not None).all()
for article in articles:
    print(article)

# and 用于多个条件连接   and_也可以用
print("-"*50)
articles = db_session.query(Article).filter(Article.title == "article8",
                                            Article.id == 9).all()
for article in articles:
    print(article)


# or 用于多个条件连接
print("-"*50)
articles = db_session.query(Article).filter(or_(Article.title == "article8",
                                            Article.id == 2)).all()
for article in articles:
    print(article)

如果想查看原生的SQL语句，可以不在后面加all()或者first()

外键及其四种约束

1. 外键：是多表之间进行关联的基础，SQLAlchemy同样也支持外键，同构foreignKey来实现，并且可以指定表的外键约束。

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func
from sqlalchemy import Text, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import random


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(Text, nullable=False)
    uid = Column(Integer, ForeignKey("user.id"), nullable=False)  # 创建外键

    def __repr__(self):
        return "<article>" + str(self.id) + " | " + self.title + " | " + " | " + str(self.uid)


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)


Base.metadata.drop_all()
Base.metadata.create_all()

使用SQLAlchemy创建外键，需要在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段即可，从表中外键的字段，必须和父表的主键字段类型保持一致。

外键约束： 由于从表会引用主表中的字段，从表中的总段记录删除，主表中的数据应该如何变化呢？

外键约束有以下几项：
RESTRICT：父表数据被删除，会阻止删除

NO ACTION：在mysql中，同RESTRICT

CASCADE：级联删除，父表中的数据删除，子表中的数据也跟随删除

SET NULL：父表数据被删除，子表数据相应的字段会被设置位null

class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(Text, nullable=False)
    uid = Column(Integer, ForeignKey("user.id", ondelete="RESTRICT"), nullable=False)  # 创建外键
    # ondelete表示的是外键的约束

    def __repr__(self):
        return "<article>" + str(self.id) + " | " + self.title + " | " + " | " + str(self.uid)

ORM层面外键的优化使用，以及一对多的关系

在两个表中查询数据：

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func
from sqlalchemy import Text, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import random


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(Text, nullable=False)
    uid = Column(Integer, ForeignKey("user.id", ondelete="RESTRICT"), nullable=False)  # 创建外键
    # ondelete表示的是外键的约束

    def __repr__(self):
        return "<article>" + str(self.id) + " | " + self.title + " | " + " | " + str(self.uid)


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)

    def __repr__(self):
        return str(self.id) + " | " + self.username


article = db_session.query(Article).first()
uid = article.uid
user = db_session.query(User).filter(User.id == uid).first()
print(article.title, article.content, user.username)

上述查找数据的方法过于繁琐，sqlAlchemy提供了更加简便和强大的方法：

一对多与多对一：

SQLAlchemy提供的relationship可以定义属性，在以后访问相关联的表的时候，就可以直接通过属性访问的方式进行访问

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func
from sqlalchemy import Text, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import random


HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"

# dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)

# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类

db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中


class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(Text, nullable=False)
    uid = Column(Integer, ForeignKey("user.id", ondelete="RESTRICT"), nullable=False)  # 创建外键
    # ondelete表示的是外键的约束

    author = relationship("User", backref="articles")    # relationship 一个Article对应一个author

    def __repr__(self):
        return "<article>" + str(self.id) + " | " + self.title + " | " + " | " + str(self.uid)


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)

    # articles也可以通过在Article中赋值backref参数实现
    # articles = relationship("Article")  # relationship 一个author可能对应多个Article

    def __repr__(self):
        return str(self.id) + " | " + self.username


article = db_session.query(Article).first()
print(article.author.username)

print("-"*50)
user = db_session.query(User).filter(User.id == article.uid).first()
print(user.articles)

----------------------------------------------------------------------------------------------------------------