sqlalchemy-Object Relationship Tutorial Orm-relationship

One: Filter common operations

1) equals

' Ed ')

2)Not equals

' Ed ')

3)Like is case-sensitive

Query.filter (User.name.like ('%ed%'))

4) ILIKE is case insensitive

Query.filter (User.name.ilike ('%ed%'))

5) in

Query.filter (User.name.in_ (['ed'Wendy' ) Jack ']))

# works with Query objects too:
Query.filter (User.name.in_ (
Session.query (User.Name). Filter (User.name.like ('%ed% '))
))

6) not in

Query.filter (~user.name.in_ (['ed'Wendy' ) Jack']))

7) is NULL

Query.filter (User.Name = = None) Query.filter (User.name.is_ (none))

8) is not NULL

Query.filter (User.Name! = None) Query.filter (User.name.isnot (none))

9) and

#Use and_ () fromSQLAlchemyImportAnd_query.filter (And_ (User.Name=='Ed', User.fullname = ='Ed Jones'))#or send multiple expressions to. Filter ()Query.filter (User.Name = ='Ed', User.fullname = ='Ed Jones')#or chain multiple filter (). Filter_by () callsQuery.filter (User.Name = ='Ed'). Filter (User.fullname = ='Ed Jones')

Ten) OR

 from Import  'ed'Wendy'))

One) MATCH

match () uses a database-specific match or contains function, which behaves differently back-end and unavailable, such as back-end SQLite. Query.filter (User.name.match ('Wendy'))

Second: return results

Query emits a SQL statement and returns a value that includes database results

　　

Print (Session.query (User.Name). Filter (user.fullname=="shi jun2"))--------------- -----------------------------=% (fullname_1) s

1) All () take out the record

2) First () returns to record one

3) One (): does not exist, or has multiple lines of record when the error (no items found,multiple items found

4) One_or_none (): Returns a record if there is a record, or none if there is no record

5) scalar () calls the one () method to successfully return the 1th column with only a single row of results

　　

query=session.query (User.Name). First ()Print(query) ('Shi',) query=session.query (User.Name). All ()Print(query) [('Shi',), ('Shi2',)] Query=session.query (User.Name). One ()Print(query) sqlalchemy.orm.exc.MultipleResultsFound:Multiple rows were found forOne () query=session.query (User.Name). Filter (user.name=="Shi"). One ()#There are only 1 results in this query, so there is no error.Print(query) ('Shi',) returns a record if there is a record , and returns if no record is Nonequery=session.query (User.Name). Filter (user.name=="Shi"). One_or_none () ('Shi',)Print(query) query=session.query (User.Name). Filter (user.name=="SH3"). One_or_none ()Print(query) nonequery=session.query (User.Name). Filter (user.name=="Shi"). Scalar ()Print(query) Shi

Three: Query through text () can use string

#For the user in Session.query (user). Filter (Text ("id<224")). Order_by (TEXT ("id")). All ():#print (user)#pass parameters by params, using: to separate parameters#For the user in Session.query (user). Filter (Text ("Id>:value and Name=:name"). Params (value=1,name= "Fred"). Order_ by (User.ID). One ():#print (user) forUserinchSession.query (User). From_statement (Text ("SELECT * from users where Name=:name"). Params (name="Shi"). All ():Print(user)

Four: The count () method is used to determine how many rows the SQL statement will return

Print(Session.query (User). Filter (User.name.like ("shi%") . Count ()) use Func.count () statistics fromSQLAlchemyImportfunc forRowinchsession.query (Func.count (user.name), User.Name). group_by (User.Name). All ():Print(Row)------------(1,'Shi')(1,'Shi2') SELECT COUNT (*) fromTable==session.query (Func.count ('*'). Select_from (User). Scalar ()

sqlalchemy-Object Relationship Tutorial Orm-relationship