SQLAlchemy的基础查询的汇总

这是连接mysql数据库以及操作数据库所要准备的

from sqlalchemy import create_engine, Column, Integer, String, Table, MetaData, select, or_, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, session
import sys  # 导入sys模块
sys.setrecursionlimit(3000)  # 将默认的递归深度修改为3000
engine = create_engine('mysql+pymysql://（数据库账号名）:（数据库密码）@localhost:（数据库端口号默认为3306）/（数据库名）?charset=UTF8')

# 定义模型类继承的父类及数据连接会话
DBsession = sessionmaker(bind=engine)
dbsession = DBsession()
Base = declarative_base()
md = MetaData()

为数据表user1234建立映射类

class Users(Base):
    __table__ = Table('user1234',MetaData(),Column('id',String(11), primary_key=True),
                      Column('country',String(20), default=None, nullable=False, comment="国家"),
                      Column('name',String(20), default=None, nullable=False, comment="用户姓名"),
                      Column('phone',String(20), default=None, nullable=False, comment="电话"))
    

基础查询

 # select * from user1234
result = dbsession.query().all()  

 # select id,name from user1234
result = dbsession.query(Users.id,Users.name).all()  

 # select * from user1234 where id = 1 and name = 'Bob
result =  dbsession.query(Users).filter_by(id=1,name='Bob').all()  

# select * from user1234 where id = 1 or name = 'Bob
result = dbsession.query(Users).filter(or_(Users.id ==1,Users.name =='Bob')).all()  

# select * from user1234 limt 3
reesult = dbsession.query(Users).limit(3).all()
# select * from user1234 limt 3,5
reesult = dbsession.query(Users).limit(5).offset(3).all()

# select count(*) from user1234 wherre id > 3
count = dbsession.query(Users).filter(Users.id>3).count()

# select distinct(country) from user1234 在country列中去重
result = dbsession.query(Users.country).distinct(Users.country).all()

# select * from user1234 order by id desc 倒叙
result =  dbsession.query(Users).order_by(Users.id.desc()).all()

# select * from user1234 where name like '%陈%'
result =  dbsession.query(Users).filter(Users.name.like('%陈%')).all()

# 分组 select * from user1234 group by coutry
result =  dbsession.query(Users).group_by(Users.country).all()
result =  dbsession.query(Users).group_by(Users.country).having(Users.id>2).all()

# 聚合函数 min ,max, avg, sum
result =  dbsession.query(func.sum(Users)).first()

filter()与filter_by()的 区别

1. 这俩函数都是为select语句添加条件的相当于 where.......

2. 这俩函数添加条件的形式不一样如 filter（x==y ,c==d）适用于等值查询,复杂的查询条件的对比，filter_by（x=y,c=d）