Tornado 07 Database-orm-sqlalchemy-Query

Tornado 07 Database-orm-sqlalchemy-Query

Introduction

# The previous lesson queried the results from the database using query, but is the object returned by query directly available?

#Enter the content within the query.py fromConnectImportSession fromUser_modulesImportUserrs= Session.query (User). Filter (user.username=='Xuchengcheng')#based on the returned results, RS is a query object that can be printed to see the converted SQL#Print (Rs,type (RS))Print(Session.query (User). Filter (user.username=='Xuchengcheng'). All ())#This returns a list, which is a classPrint(Session.query (User). Filter (user.username=='Xuchengcheng'). First ())#This returns the instantiation of a class.Print(Session.query (User) [0])#The value of index 0, similar to first, but not equal to, empty list with this will errorRS= Session.query (User). Filter (user.username=='Xuchengcheng'). All ()#Print (RS) also returns a listPrint(Rs[0].username)#list so instantiate the class of 0 and then use the Dot method to remove the usernamePrint(GetAttr (Rs[0),'username'))#This method can also be removed usernameRS= Session.query (User.username). Filter (user.username=='Xuchengcheng'). All ()Print(RS)#return [(' Xuchengcheng ',)], specify a property value in query, return is not a query object, but directly query the value of User.username, so we returned a list including the tupleRS= Session.query (User.username). Filter (user.username=='Xuchengcheng'). First ()Print(RS)#The return is a tuple

Second, the conditions of inquiry

# How to implement queries in SQLAlchemy with different conditions

 #   filter function filter, the popular point is to add conditions  #  filter is a filter function that can be written in the secondary function with different conditions separated by commas  session.query (User). Filter (user.username  == " xuchengcheng   " ). All ()  #  filter_by is also a filter function, but the function is weaker  Session.query (User). filter_by (Username= '   Xuchengcheng   "). All () 

# difference between the two # 1, the filter needs to add Class object, filter_by do not need # 2, filter_by can only add equal conditions, cannot add not equal, greater than less than the conditions

Quickly add data

# #1, click Pycharm Right Database#2, click the Green plus DataSource--->mysql #  3. Name customization; database   : Select the database that needs operation MyDB; user:admin  ; Password: Password under the user root110qwe   #4, test connection testing Connection #5, apply---->  #  You can then quickly add content through MySQL inside the database schemas, remember to  finish the operation click Green  ->db  Save

Conditional query

#Conditional Queryrs = Session.query (User). Filter (user.username=='Xuchengcheng')#Fuzzy Queryrs = Session.query (user.username). Filter (User.username.like ('%cheng%'). All ()#Fuzzy query likeRS= Session.query (User.username). Filter (User.username.notlike ('%cheng%'). All ()#Fuzzy query Notlike#exact Queryrs = Session.query (user.username). Filter (User.username.is_ (None)). All ()#accurate query is, need to add ' _ 'RS= Session.query (User.username). Filter (User.username.isnot (None)). All ()#Accurate Query IsNot#Scope Queryrs = Session.query (user.username). Filter (User.username.in_ (['Xuchengcheng','Zhouzhou']). All ()#Range Lookup In_RS= Session.query (User.username). Filter (User.username.notin_ (['Xuchengcheng','Zhouzhou']). All ()#Range Lookup Notin_#Limit,offset,slicePrint(Session.query (user.username). Limit (3). All ())#The first three lines are traced back to the previous sequence .Print(Session.query (user.username). Offset (2). All ())#Offset 2, front two data not looking, starting from the third data queryPrint(Session.query (user.username). Slice (1,3). All ())#slices, data indexed from 1 to 2#Print (Session.query (user.username). Filter (user.username== ' Xuchengcheng '). One ()) #只能有一个数据, If there is more than one Xuchengcheng will be an error, can be used to test whether there is only one#Sort fromSQLAlchemyImportDesc#desc is required for importPrint(Session.query (user.username,user.id). Filter (user.username!='Xuchengcheng'). Order_by (User.ID). All ())#The query is not equal to the username of the Xuchengcheng and is sorted according to the order of the IDs (via Order_by)Print(Session.query (user.username,user.id). Filter (user.username!='Xuchengcheng'). Order_by (Desc (user.id)). All ())#The query is not equal to the username of Xuchengcheng and is sorted by the reverse order (DESC) Order of the IDPrint(Session.query (user.username,user.id). Filter (user.username!='Xuchengcheng'). Order_by (user.id). Limit (2). All ())#Limit Quantity#function fromSQLAlchemyImportFunc,extract#using a function requires importing these twoPrint(Session.query (User.password). group_by (User.password). All ())#Group Group_byPrint(Session.query (User.password,func.count (User.ID)). Group_by (User.password). All ())#using Func, the function name method to use the functionPrint(Session.query (User.password,func.count (User.ID)). Group_by (User.password). Having (Func.count (User.password) > 1). All ())#Having a common with group_by, adding conditions#sort by time within the creation timePrint(Session.query (Extract ('minute', User.creatime). Label ('minute'), Func.count (User.ID)). Group_by ('minute'). All ())#GROUP BY minutes                #extract: Extract minutes from Creatime label: alias group_by: Using aliases

Three, multi-table query

#Multi-Table QueryPrint(Session.query (User,userdetails). Filter (Userdetails.id==user.id). All ())#Cross Join Query methodPrint(Session.query (User.username,userdetails.lost_login). Join (Userdetails,userdetails.id==user.id). Filter ( userdetails.id==user.id). All ())#INNER Join Query method      #There's no difference between the two methods in MySQL .Print(Session.query (Userdetails.lost_login,user.username). Outerjoin (User,user.id==userdetails.id). Filter ( userdetails.id==user.id). All ())#outer Join Query method#Union Union queryQ1 =session.query (user.id) Q2=session.query (userdetails.id) q1.union (Q2). All ()#Federated queries, with a go-to-weight feature#child Table QuerySql_0 = Session.query (userdetails.lost_login). Subquery ()#subquery declaring a child tablePrint(Session.query (User,sql_0.c.lost_login))#querying with a child table

Iv. Native SQL queries

# SQLAlchemy Although you can not worry about SQL, but some things seem to be more troublesome, these times with native SQL will Genjia more convenient

#Native SQL querySql_1 ="""select * from ' user ' #这里需要用反引号"""Row=Session.execute (Sql_1)Print(Row)Print(Row.fetchone ())#get a piece ofPrint(Row.fetchmany ())#get a list with only onePrint(Row.fetchall ())#Get all forIinchRow#Get all    Print(i)

Tornado 07 Database-orm-sqlalchemy-query