Web后端学习笔记 Flask（6）数据库

SQLAlchemy一对一关系实现：

在一对多的条件下：给Article表中添加article，只需要将article放入到user的article属性中，再将user添加到user表中即可，此时的article会自动添加到Article表中。

# -*- 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)

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


article1 = Article(title="new art 1", content="ct111")
article2 = Article(title="new art 2", content="ct222")
article3 = Article(title="new art 3", content="ct333")

user = User(username="ghost")
user.articles.extend([article1, article2, article3])

db_session.add(user)
db_session.commit()
# 此时，user被添加到User表中，而所有的文章添加到了Article表中，uid是当前添加的user

在一对一的条件下，也可以进行添加，例如，通过添加article,实现将user添加到User中

article1 = Article(title="the Knight", content="ktkt111")
user = User(username="knight")
# 一对一直接赋值即可
article1.author = user

db_session.add(article1)
db_session.commit()

一对一关系的实现：
       举例： 在后台会存储用户的信息，但是用户信息中有常用的字段（例如，username, id, sex）以及不常用的字段，例如address, email等信息，那么为了提高数据库的查找效率，可以对用户的存储进行优化，将常用的用户信息存放到user表中，将不常用的用户信息存放到user_extend表中，则这两个表的关系就是一对一的关系。

# -*- 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)
    
    # 因为一个User只能对应一个user_extend,必须是一对一的关系
    extend = relationship("UserExtend", uselist=False)  
    # uselist表示使用列表，默认为True, 表示User下的extend是一个列表，则表示extend不唯一
    # 由于extend需要与user一一对应，所以将uselist参数设置为False，此时表示extend是唯一的

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


class UserExtend(Base):
    __tablename__ = "user_extend"
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50), nullable=True)
    address = Column(String(50), nullable=False)
    email = Column(String(50), nullable=False)
    uid = Column(Integer, ForeignKey("user.id", ondelete="RESTRICTION"))
    
    # 在orm层面做关联
    user = relationship("User")


article1 = Article(title="the Knight", content="ktkt111")
user = User(username="knight")

通过这样的设置，user和user_extend则会成为一一对应，但是此时不能再设置backref参数为extend

还有一种方法，即通过backref函数设置backref参数设置。

# -*- 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, backref

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)

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


class UserExtend(Base):
    __tablename__ = "user_extend"
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50), nullable=True)
    address = Column(String(50), nullable=False)
    email = Column(String(50), nullable=False)
    uid = Column(Integer, ForeignKey("user.id", ondelete="RESTRICTION"))

    # 在orm层面做关联
    user = relationship("User", backref=backref("extend", uselist=False))


article1 = Article(title="the Knight", content="ktkt111")
user = User(username="knight")

多对多的关系实现：
     举例:在博客上，一篇文章的标签有多个，一个标签也可能对应多篇文章，这是一个典型的多对多的关系。

如果需要定义一个多对多的关系，那么首先需要将两个做多对多的模型定义出来，使用Table定义一个中间表，中间表一般只包含两个模型的外键字段，让这两个字段的组合作为中间表的复合主键，在多对多的两个模型上，随便选择一个，做一个relationship,同时定义secondary参数为中间表。

# -*- 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
from sqlalchemy import Table


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模型映射到数据库中

article_tag = Table(
    "article_tag",         # 数据库的名字
    Base.metadata,         # 元数据
    Column("article_id", Integer, ForeignKey("article.id"), primary_key=True),
    Column("tag_id", Integer, ForeignKey("tag.id"), primary_key=True),
    # 这个表中没有主键，可以将article_id和tag_id一起作为一个复合主键
    # 假设user表中 article_id/title = 1/"cxcx"
    # tag表中，tag_id / tag_name = 1/"leisure"
    # 则数据表article_tag 中会插入 article_id/tag_id = 1/1
    # 为了减少数据存储的冗余，因该只能插入一次 article_id/tag_id = 1/1，
    # 所以可以将他们的组合作为一个复合主键
)


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

    tags = relationship("Tag", backref="articles", secondary=article_tag)

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


class Tag(Base):
    __tablename__ = "tag"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)

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


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

# 插入数据
# article1 = Article(title="gone to xxx")
# article2 = Article(title="Leave now")
#
# tag1 = Tag(name="scary")
# tag2 = Tag(name="horrible")
# article1.tags.extend([tag1, tag2])
#
# tag3 = Tag(name="funny")
# article2.tags.extend([tag2, tag3])
#
# db_session.add_all([article1, article2])
# db_session.commit()


# 数据查询
articles = db_session.query(Article).all()
for article in articles:
    tag_str = []
    for tag in article.tags:
        tag_str.append(tag.name)
    print(article.title, " ", tag_str)

查询的效果：

ORM层面删除数据注意事项：

       外键以及外键约束是数据库层面，下面介绍以下基于ORM层面的约束。例如，在数据库层面有一个约束叫restrict，代表限制删除，即如果要删除父表的数据，但是由于从表也能用到了父表的数据，所以数据库是拒绝删除的。但是在ORM层面，如果指定了级联删除，那么ORM就会无视数据库的RESTRICT约束，先把从表中的数据删除，然后再将从表中的数据删除。这样就实现了级联删除。

orm层面删除数据会无视MySQL层面的外键约束，直接会将对应的数据删除，然后将从表中的外键设置为空，如果想要避免这种行为，应该将从表中的null设置为False.

# -*- 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
from sqlalchemy import Table


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


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

    uid = Column(Integer, ForeignKey("user.id"), nullable=False)
    author = relationship("User", backref="articles")

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


# Base.metadata.drop_all()
# Base.metadata.create_all()
#
# user1 = User(username="Topic")
# article1 = Article(title="Gone with u")
# article1.author = user1
# db_session.add(article1)
# db_session.commit()


# 在orm层面会直接删除数据，
# orm会先将article的uid设置为null,然后再将user表中的数据删除
# 为了限制这种默认的行为，可以将uid的nullable属性设置为False
user = db_session.query(User).first()
db_session.delete(user)
db_session.commit()

orm层面的CASCADE删除

在SQLAlchemy中，只要将一个数据添加到session中，和它相关联的数据都可以一起存储到数据库中。这是通过relationship的时候，有一个关键字参数cascade可以设置这些属性。cascade的属性值有:

1. save-update：默认值，表示在添加一条数据的时候，会把其他和它相关联的数据都添加到数据库中。

2. delete：表示删掉某一模型中的数据的时候，是否删除掉使用relationship关联的数据。

3. delete-orphan：表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型的relationship中，增加一个single_parent=True的参数。

4. merge：默认选型。当在使用session.merge合并一个对象的时候，会将使用了relationship相关联的对象进行merge操作。

5. expunge: 移除操作的时候，会将相关联的对象也进行移除，这个操作只是从session中移除，并不会真正从数据库中移除。

6. all：是对save-update，merge，refresh-expire, expunge，delete的集中缩写

 # 数据库层面定义
 uid = Column(Integer, ForeignKey("user.id", cascade="save-update, delete"))

如果需要实现，删除文章的时候，将相关的作者删除，则可以设置cascade

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


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

    # 数据库层面定义
    uid = Column(Integer, ForeignKey("user.id"))

    # orm层面定义
    author = relationship("User", backref="articles", cascade="save-update, delete")

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

如果需要实现，删除文章的时候，删除相关的作者，或者删除作者的时候，同时删除相关的文章，则可以在user中也定义一个relationship.

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

    articles = relationship("Article", cascade="save-update,delete")

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


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

    # 数据库层面定义
    uid = Column(Integer, ForeignKey("user.id"))

    # orm层面定义
    author = relationship("User", cascade="save-update,delete")

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

例如，可以再定义一张表，comment：

# -*- 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
from sqlalchemy import Table


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 User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)

    # 这里可以用backref函数在子表中实现
    articles = relationship("Article", cascade="save-update,delete")  # user删除，对应的article会删除
    comments = relationship("Comment")    # user删除，对应的comment不会删除

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


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

    # 数据库层面定义
    uid = Column(Integer, ForeignKey("user.id"))

    # orm层面定义
    author = relationship("User", cascade="save-update,delete")

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


class Comment(Base):
    __tablename__ = "comment"
    id = Column(Integer, primary_key=True, autoincrement=True)
    content = Column(String(50), nullable=False)
    uid = Column(Integer, ForeignKey("user.id"))

    author = relationship("User")

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


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

user1 = User(username="Top")
article1 = Article(title="Gone with wind")
article2 = Article(title="Jane")
comment1 = Comment(content="good")
comment2 = Comment(content="great works")

user1.articles.append(article1)
user1.articles.append(article2)

user1.comments.append(comment1)
user1.comments.append(comment2)
db_session.add(user1)
db_session.commit()

 数据库中的排序：三种排序方法：

1. orderby

在数据库中插入数据：

# -*- 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
from sqlalchemy import Table


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)
    create_time = Column(DateTime, nullable=False, default=datetime.now)

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


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

# article = Article(title="title3")
# db_session.add(article)
# db_session.commit()

articles = db_session.query(Article).order_by(Article.create_time.desc())  # 反向排序
# articles = db_session.query(Article).order_by("~article.create_time")  # 反向排序
for article in articles:
    print(article)

排序结果：

2. 定义模型的时候，传递orderby参数

# -*- 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
from sqlalchemy import Table


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)
    create_time = Column(DateTime, nullable=False, default=datetime.now)

    # 定义排序的方式
    __mapper_args__ = {
        "order_by": create_time.desc()
    }

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


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

# article = Article(title="title3")
# db_session.add(article)
# db_session.commit()

articles = db_session.query(Article).all()  # 反向排序
# articles = db_session.query(Article).order_by("~article.create_time")  # 反向排序
for article in articles:
    print(article)

3. 在定义relationship的时候，可以传递order_by参数，指定排序方式
    在存在主表以及父表的时候，通过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, backref
from datetime import datetime
import random
from sqlalchemy import Table


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)
    create_time = Column(DateTime, nullable=False, default=datetime.now)

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

    # 通过user拿到user下的所有的文章的时候，可以通过时间排序
    author = relationship("User", backref=backref("articles", order_by=create_time.desc()))

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


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


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

# article1 = Article(title="title1")
# article2 = Article(title="title2")

# user = db_session.query(User).filter(User.username == "toc").first()
# user.articles.append(article2)
#
# db_session.add(user)
# db_session.commit()

user = db_session.query(User).first()
articles = user.articles
for article in articles:
    print(article)

查询结果：

排序的默认方式是从小到大，如果需要反过来，可以使用这个字段的desc()方法。

limit，offset，以及切片操作：

limit：可以限制每次查找的时候只查询几条数据

offset：可以限制查询数据的时候过滤掉前面多少条数据

slice：可以对query()对象使用切片操作

# -*- 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, backref
from datetime import datetime
import random
from sqlalchemy import Table
import time


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)
    create_time = Column(DateTime, nullable=False, default=datetime.now)

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


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

# 插入数据
# for x in range(100):
#     title = "title" + str(x+1)
#     article = Article(title=title)
#     time.sleep(1)
#     db_session.add(article)
#     db_session.commit()
#     print("current {}".format(x))

# # 查询limit
articles = db_session.query(Article).limit(10).all()
for article in articles:
    print(article)


# 查询offset
print("-"*50)
articles1 = db_session.query(Article).offset(10).limit(10).all()  # 从第11条开始，查找10条
for article in articles1:
    print(article)

# 查询slice
print("-"*50)
articles2 = db_session.query(Article).slice(20, 30).all()
for article in articles2:
    print(article)

数据查询懒加载技术：
       在一对多，或者多对多的时候，如果想要获取多的这一部分的数据的时候，通常来讲，通过一个属性就可以全部获取，例如上面用到的user.artices就可以获取全部的文章。但是有的时候并不像获取全部的数据，指向获取这个作者今天发布的文章，或者只是想展示少部分的数据。可以通过给relationship传递一个lazy=dynamic，那么以后通过user.articles获取到的就不是一个列表，而是一个AppendQuery对象，可以对这个对象在进行一层过滤和排序操作。

       如果采用将所有的数据从数据库中拿出来再进行过滤的话，就会比较耗费性能。上述的方法称为懒加载。

# -*- 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, backref
from datetime import datetime
import random
from sqlalchemy import Table
import time


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


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

    # 数据库层面定义
    uid = Column(Integer, ForeignKey("user.id"))

    # orm层面定义
    author = relationship("User", backref=backref("articles", lazy="dynamic"))  # 懒加载

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


user = db_session.query(User).first()
print(type(user.articles))
articles = user.articles.filter(Article.id > 50).all()   # user.articles可以直接使用filter进行过滤
for article in articles:
    print(article)

# user.articles 是一个<class 'sqlalchemy.orm.dynamic.AppenderQuery'>
# 所以除了可以使用filter的方法
# 也可以使用append方法添加新的数据，再次commit到数据库中。

article_new = Article(title="new article")
user.articles.append(article_new)
db_session.commit()

lazy的属性值：

1. select, 默认值，例如，如果没有访问user.articles，sqlalchemy就不会从数据库中查找数据，一旦访问了这个属性，sqlalchemy就会从数据库查找所有的数据，返回的数据是一个list对象，这也是懒加载。

2. dynamic：在访问user.articles属性的时候，返回的是一个AppendQuery对象。

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