#encoding: utf-8
from sqlalchemy importcreate_engine,Column,Integer,String,Float,func,and_,or_,Text,\
ForeignKey,DateTimefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy.orm importsessionmaker,relationship,backreffrom random importrandintfrom datetime importdatetime
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(32),nullable=False)#articles = relationship("Article") #获取用户发布所有文章
classArticle(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)#没有加载懒加载技术的relationship
#author = relationship('User',backref='articles')
#注意是user表对article表的懒加载,写在多关系一边
author = relationship('User',backref=backref('article',lazy=
'dynamic'))def __repr__(self):return ''%self.title
s#Base.metadata.drop_all()#
#Base.metadata.create_all()#
#user = User(username='zhiliao')#
#for x in range(100):#article = Article(title='title%s'%x,content='content%s'%x)#article.author = user#session.add(article)#session.commit()#获取该用户的所有文章#user = session.query(User).first()#
#print(user.articles)
#如果获取该用户今天发表的文章#articles = user.articles#article_today = [article for article in articles if article.create_time.day=='today']#如果想上面那样做的话,很耗费性能,等于我要全部查一遍
#user = session.query(User).first()#print(type(user.articles)) #,这是没有加载lazy
user=session.query(User).first()print(type(user.articles))#加了lazy就变成了AppenderQuery对象#因为AppenderQuery对象,就可以这样写,比如说查询该用户31天发表的文章,大大节省了性能
print(user.articles.filter(Article.create_time.day == 31))#还可以追加数据进去
article = Article(title= 'title100')
user.articles.append(article)
session.commit()