from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func, and_, or_, ForeignKey, Table

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker, relationship, backref

from datetime import datetime

# 连接数据库字符串

DB_URI = "mysql://root:123456@127.0.0.1/pyDemo?charset=utf8mb4"

engine = create_engine(DB_URI)

# 判断是否连接成功

engine.connect()

# 创建ORM

Base = declarative_base(engine)

# 创建回话

session = sessionmaker(engine)()

# 用户

class User(Base):

    # 表名字

    __tablename__ = 'User'

    # 列

    id = Column(Integer, primary_key=True, autoincrement=True)

    username = Column(String(50), nullable=False)

# 文章

class Article(Base):

    # 表名字

    __tablename__ = 'article'

    # 列

    id = Column(Integer, primary_key=True, autoincrement=True)

    title = Column(String(50))

    # 设置外键

    uid = Column(Integer, ForeignKey('User.id', ondelete="RESTRICT"))

    create_time = Column(DateTime, default=datetime.now)

    update_time = Column(DateTime, onupdate=datetime.now, default=datetime.now)

    author = relationship("User", backref=backref("articles",

                                                  order_by=create_time.desc()))

    __mapper__args__ = {

        "order_by": create_time.desc

    }

    def __repr__(self):

        return "<Article(title:%s)>" % self.title

# 生成实体

# Base.metadata.drop_all()

# Base.metadata.create_all()

# import time

# user = User(username="ceshi001")

# article1 = Article(title="title0001")

# article2 = Article(title="title0002")

# article3 = Article(title="title0003")

# article4 = Article(title="title0004")

# user.articles.append(article1)

# session.add(article1)

# time.sleep(2)

# user.articles.append(article2)

# session.add(article2)

# time.sleep(2)

# user.articles.append(article3)

# session.add(article3)

# time.sleep(2)

# time.sleep(2)

# user.articles.append(article4)

# session.add(article4)

# session.commit()

user = session.query(User).first()

print(user.articles)

# order_by排序

# article = session.query(Article).order_by(Article.create_time.desc()).all()

# print(article)

# article = session.query(Article).order_by("create_time").all()

# print(article)

#

# article = session.query(Article).all()

# print(article)