基于FLASK-SQLAlchemy的持久層SQL語法

1. insert into table
   

   a. create an object based on the Module

   b. db.session.add(object)

   
   

2. select * from table

   a. Classname(based on the Module).query.all()

3. select * from table where?

   a. Classname.query.filter_by(condition=value).all()

4. select * from table where? xxx limit 1

   a.?Classname.query.filter_by(condition=value).first()

5. select count(*) from table where? xxx

   a.?Classname.query.filter_by(condition=value).count()

6. select * from table where? primarykey=xx

   a.?Classname.query.get(xx/id)

   
   

????7. update column

????????a. fetch object, eg. flight = Flight.query.get(x)

????????b. flight.column = newValue

????8. delete from table

????????a. fetch object, eg. flight = Flight.query.get(x)

????????b. db.session.delete(object)?

Advanced sql:

a.?Classname.query.order_by(Classname.column).all()

b.?Classname.query.order_by(Classname.column.desc()).all()

c.?Classname.query.filter(Classname.column != value).all()

d.?Classname.query.filter(Classname.column.like(%abc%)).all()

e.?Classname.query.filter(Classname.column.in_(aPythonValueList)).all()

f.?Classname.query.filter(and_(condition1, condition2, ...)).all()

g.?Classname.query.filter(or_(condition1, condition2, ...)).all()

h. db.session.query(Classname1, Classname2).filter(Classname1.column == Classname2.column).all()

Notice:

insert, update, delete:?

should add a statement: db.session.commit()

一對多，多對多對象關系SQL語法：

https://www.jianshu.com/p/8c038f0134f8