#encoding: utf-8
fromsqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_,Text,\
ForeignKey,DateTimefromsqlalchemy.ext.declarative import declarative_basefromsqlalchemy.orm import sessionmaker,relationship,backreffromrandom import randintfromdatetime import datetime
HOSTNAME= '127.0.0.1'PORT= 3306DATABASE= 'first_sqlalchemy'USERNAME= 'root'PASSWORD= '123456'#dialect+driver://username:password@host:port/database
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/"\"{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
engine=create_engine(DB_URI)
Base=declarative_base(engine)
# Session=sessionmaker(engine)
# session=Session()
session= sessionmaker(engine)() #Session(**local_kw)
#第三种排序方式根据一对多,多对对关系,进行排序,排序只能作用在多的关系上,classUser(Base):
__tablename__= 'user'id= Column(Integer, primary_key=True, autoincrement=True)
username= Column(String(50),nullable=False)classArticle(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)#datetime.now不能加括号,是文章生成插入数据库的时间
uid= Column(Integer,ForeignKey('user.id'))
#第三种排序方式,跟relationship结合使用,作用多的关系上
author= relationship("User",back_ref=backref('articles',order_by=create_time.desc()))
#第二种排序方式,使用魔术属性
__mapper_args__={"order_by": create_time.desc()
}
def __repr__(self):return ''.format(self.title,self.create_time)
# Base.metadata.drop_all()
# Base.metadata.create_all()
# article= Article(title='123456')
# session.add(article)
# session.commit()
#一个order_by根据文章的发表时间进行查询
#字段前没有减号(-)就是升序排列
#字段前有减号(-)就是降序排列
# article= session.query(Article).order_by(-Article.create_time).all()
#或使用desc,来倒序
# article= session.query(Article).order_by(-Article.create_time.desc).all()
#或用字段名来倒叙
# article= session.query(Article).order_by("-create_time").all()
# print(article)"""SELECT article.id AS article_id, article.title AS article_title,
article.create_time AS article_create_time
FROM article ORDER BY article.create_time"""#使用第二种排序方式试一试
articles=session.query(Article).all()
print(articles)#测试是可以的