Common sqlalchemy syntax and sqlalchemy syntax

Common sqlalchemy syntax and sqlalchemy syntax
1. Add data

# Add a new data user_obj = User (name = "bigberg", passwd = "twgdh123") Session. add (user_obj) Session. commit () # user_obj1 = User (name = "bigberg", passwd = "twgdh123") user_obj2 = User (name = "someone", passwd = "twgdh123 ") session. add_all ([user_obj1, user_obj2]) Session. commit ()

　　

Ii. query data

2.1 normal query

# Filter_by obtains the Object List data = Session. query (User ). filter_by (name = 'bigberg '). all () print (data [0]. id, data [0]. name, data [0]. passwd) # output [<__ main __. user object at 0x0000029dc2d000060>] 1 bigberg twgdh123 # condition data = Session is not specified. query (User ). filter_by (). all () print (data [0]. id, data [0]. name, data [0]. passwd) # output [<__ main __. user object at 0x0000026C9D27F0F0>, <__ main __. user object at 0x0000026C9D27F160>, <__ main __. user object at 0x0000026C9D27F1D0>] 1 bigberg twgdh123

　　

2.2 explicit display of query data

Define in class

Class User (Base): _ tablename _ = "user" # table name id = Column (Integer, primary_key = True) name = Column (String (32 )) passwd = Column (String (64) def _ repr _ (self): return "id: % s name: % s password: % s" % (self. id, self. name, self. passwd)

Data = Session. query (User ). filter_by (). all () print (data [0]. id, data [0]. name, data [0]. passwd) # output [id: 1 name: bigberg password: twgdh123, id: 2 name: Jerry password: twgdh123, id: 3 name: Jack password: twgdh123] 1 bigberg twgdh123

　　

2.3 obtain the first data

Data = Session. query (User ). filter_by (). first () print (data. id, data. name, data. passwd) # output id: 1 name: bigberg password: twgdh1231 bigberg twgdh123

　　

2.4 obtain all data

Print (Session. query (User. id, User. name, User. passwd ). all () # output [(1, 'bigberg ', 'twgdh123'), (2, 'Jerry', 'twgdh123'), (3, 'jack ', 'twgdh123')]

　　

2.5 multi-condition Query　

Data = Session. query (User ). filter (User. id> 2 ). filter (User. id <7 ). all () print (data) # output [id: 3 name: Jack password: twgdh123]

　　

2.6 fuzzy search

Data = Session. query (User ). filter (User. name. like ('J % ')). all () # output [id: 2 name: Jerry password: twgdh123, id: 3 name: Jack password: twgdh123]

　　

2.7 and/or

From sqlalchemy import and _, or_data = Session. query (User ). filter (and _ (User. id> 2, User. name. like ('J % '))). all () print (data) # output [id: 3 name: Jack password: twgdh123]

 

2.8 in _

data = Session.query(User).filter(User.id.in_([1,3])).all()print(data)data = Session.query(User).filter(User.name.in_(['bigberg', 'Jack'])).all()print(data)

 

2.9 sorting

data = Session.query(User).order_by(User.name.desc()).all()print(data)

　　

3. modify data

• First assignment

# data = Session.query(User).filter(User.name=='Marry').first()data = Session.query(User).filter_by(name='Marry').first()data.name = 'Tom'Session.commit()

• Second update

Session.query(User).filter_by(name='Tom').update({'name': 'Hary'})Session.commit()

• Rollback

Ession. query (User ). filter_by (name = 'hary '). update ({'name': 'john'}) print (Session. query (User ). filter_by (name = 'john '). all () # roll back the Session. rollback () print (Session. query (User ). filter_by (name = 'john '). all () Session. commit () # output [id: 2 name: John password: twgdh123] [] mysql> select * from user; + ---- + --------- + ---------- + | id | name | passwd | + ---- + --------- + ---------- + | 1 | bigberg | twgdh123 | 2 | Hary | twgdh123 | 3 | Jack | twgdh123 | + ---- + --------- + ---------- + 3 rows in set (0.00 sec) # I did not change Hary to John.

　　

Iv. Statistics

Data = Session. query (User). filter (User. name. like ('% a %'). count () print (data) # output 2

　　

V. Grouping

From sqlalchemy import funcdata = Session. query (User. name, func. count (User. name )). group_by (User. name ). all () print (data) # output [('bigberg ', 1), ('hary', 1), ('jack', 1)]