SQLAlchemy(增删改查)

添加数据

db_session = db_session if db_session else g.pg_db

1. 添加单条数据
u = User(name='用户1')
db_session.add(u)

2. 添加多条数据
db_session.add_all([
    User(name='用户2'),
    User(name='用户3'),
])

db_session.commit()

删除数据

db_session = db_session if db_session else g.pg_db
res = db_session.query(data).filter(data.id==20).delete()
db_session.commit()

修改数据

db_session = db_session if db_session else g.pg_db

db_session.query(data).filter(data.id == id).update({"age": 2}, synchronize_session=False)
db_session.commit()

查看数据

db_session = db_session if db_session else g.pg_db
user_all = db_session.query(data).all()
# where条件查询
user = db_session.query(data).filter(data.id>=2).all()

复杂的查询

# 1.and_ or_ 条件查询
from sqlalchemy.sql import and_,or_
ret = db_session.query(data).filter(and_(data.id>3,data.name=='xxx')).all()
ret2 = db_session.query(data).filter(or_(data.id<2,data.name=='xxx')).all()

# 2. 排序 
user_list = db_session.query(User).order_by(User.id.desc()).all()

# 3. 通配符 
ret = db_session.query(User).filter(~User.name.like('e%')).all()

# 4. 分组 
from sqlalchemy.sql import func ret = db_session.query(User).group_by(User.extra).all()