python访问数据库,SQLAlchemy中的Query方法

SQLAlchemyQuerySQLAlchemysessionqueryquery你可以依据需要使用不同的查询方式查找数据，下面一一举例。直接通过映射类查找：#Queryinguserinstanceforinstanceinsession.query(User).order_by(User.id):print,instance.fullname这种方法只要在querySQLSELECTusers.idASusers_id,ASusers_name,users.fullnameASusers_fullname,users.passwordASusers_passwordFROMusersORDERBYusers.id通过映射类的属性字段查询：#QueryingbyORM-instrumentforname,fullnameinsession.query(U,User.fullname):printname,fullname这种方法指定了查询的具体字段，而不是像第一种方法那样把映射类的全部字段都查询出SQLSELECTASusers_name,users.fullnameASusers_fullnameFROMusersqueryquery类对象和其中的局部属性字段：#QueryingasaPythonobjectforrowinsession.query(User,U).all:printrow.User,UsernameSQLSELECTusers.idASusers_id,ASusers_name,users.fullnameASusers_fullname,users.passwordASusers_passwordFROMusers其输出结果为一个元组和一个字符串：<</span>user(“ed“,“EdJones“,“f8x902“)>ed<</span>user(“Wendy“,“WendyWilliams“,“foobar“)>Wendy<</span>user(“Marry“,“MarryContrary“,“xxg527“)>Marry<</span>user(“Fred“,“FredFlinstone“,“blah“)>Fred4.我们还可以给返回的结果起一个别名，或者叫标签：#Queryinglabeledforrowinsession.query(U.label(”name_label”)).all:print(_label)labelUsernamename_label，其相SQLSELECTASname_labelFROMusers除了给映射类字段起别名，我们还可以给映射类起个别名：#Queryingwithaliasedfromsqlalchemy.ormimportaliaseduser_alias=aliased(User,name=”user_alias”)forrowinsession.query(user_alias,user_).all:printrow.user_aliasaliasedUseruser_alias。queryUserSQLSELECTuser_alias.idASuser_alias_id,user_ASuser_alias_name,user_alias.fullnameASuser_alias_fullname,user_alias.passwordASuser_alias_passwordFROMusersASuser_aliasqueryPython片”的操作，来限制返回的结果集范围：#Queryingwithlimitandoffsetforuinsession.query(User).order_by(User.id)[1:3]:printu这里我们对返回的结果加上了[1:3]，来限制返回的结果集范围。其执行相当于下面的SQL语句：SELECTusers.idASusers_id,ASusers_name,users.fullnameASusers_fullname,users.passwordASusers_passwordFROMusersORDERBYusers.idLIMIT?OFFSET?SQL的whereSQLAlchemyfilter_by#Qyeringwithfilterbyforname,insession.query(U).filter_by(fullname=”EdJones”):printnameUserfullname’EdJones’SQLSELECTASusers_nameFROMusersWHEREusers.fullname=?filter_byfilter依据映射类对象的属性来指定查询条件：#Queryingwithfilterforname,insession.query(U).filter(User.fullname==”EdJones”):printname其参数与filter_bySQLSELECTASusers_nameFROMusersWHEREusers.fullname=?假设我们想要嵌套多个查询条件，可以嵌套多个filter：#Queryingwithfullygenerativeforname,insession.query(U).filter(User.fullname==”EdJones”).filter(U==”ed”):printnamefilterfilter，理论上可以嵌套很多个，其相当于SQLSELECTASusers_nameFROMusersWHEREusers.fullname=?AND=?完整的例如代码如下：fromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemyimportColumn,Integer,StringfromsqlalchemyimportSequencefromsqlalchemy.ormimportsessionmakerBase=declarative_basefromsqlalchemyimportcreate_engineengine=create_engine(”sqlite:///:memory:”,echo=True)classUser(Base): tablename =”users”id=Column(Integer,Sequence(”user_id_seq”),primary_key=True)name=Column(String(50))fullname=Column(String(50))password=Column(String(12))def init (self,name,fullname,password):=nameself.fullname=fullnameself.password=passworddef repr (self):return””%(,self.fullname,self.password)Base.metadata.create_all(engine)Session=sessionmaker(bind=engine)session=Session#Addonusered_user=User(”ed”,”EdJones”,”edpassword”)session.add(ed_user)#Retrivesaveded_userour_user=session.query(User).filter_by(name=”ed”).firstprint”our_useris:”,our_userprint”our_useridis:”,our_user.idprint”our_userised_user”,our_user==ed_user#Addmultipleobjectsession.add_all([User(”Wendy”,”WendyWilliams”,”foobar”),User(”Marry”,”MarryContrary”,”xxg527”),User(”Fred”,”FredFlinstone”,”blah”)])#Detectivethedirtydataed_user.password=”f8x902”print”Dirtydata”,session.dirty#Detectivethenewdataprint”Newdata”,session.new#Commitdatasessionmit#========Querying===============#Queryinguserinstanceforinstanceinsession.query(User).order_by(User.id):print,instance.fullname#QueryingbyORM-instrumentforname,fullnameinsession.query(U,User.fullname):printname,fullname#QueryingasaPythonobjectforrowinsession.query(User,U).all:printrow.User,#Queryinglabeledforrowinsession.query(U.label(”name_label”)).all:print(_label)#Queryingwithaliasedfromsqlalchemy.ormimportaliaseduser_alias=aliased(User,name=”user_alias”)forrowinsession.query(user_alias,user_).all:printrow.user_alias#Queryingwithlimitandoffsetforuinsession.query(User).order_by(User.id)[1:3]:printprintu#Qyeringwithfilterbyforname,insession.query(U).filter_by(fullname=”EdJones”):printname