10SQLalchemy操作数据库

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, String, Date, Enum, Column
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://cai:123@localhost/test?charset=utf8',
                       # echo=True
                       )
Base = declarative_base()


class Employ(Base):
    __tablename__ = 'employ'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False)
    number = Column(String(10), unique=True, nullable=True)
    age = Column(Integer, nullable=False)
    gender = Column(String(1), server_default='F')

    def __repr__(self):
        return '(name=%s,number=%s,age=%s)' % (self.name, self.number, self.age)


Base.metadata.create_all(engine)

# 需要通过一个会话进行数据库的操作(sessionmaker)
# 1.创建一个Session类绑定访问数据库的应请
Session_class = sessionmaker(bind=engine)
# 2.实例化一个对象进行当前的数据库访问,可以创建多个不相关的实例进行分别的访问
Session = Session_class()

# 增
'''
# 1.创建类的实例对象
# e1=Employ(name='zhangfei',number='00001',age=18)
# e2=Employ(name='赵云',number='00002',age=20)
# e3=Employ(name='zhangfei',number='00003',age=20)
# e4=Employ(name='guanyu',number='00004',age=30)
e5=Employ(name='sunshagnxiang',number='00005',age=15)
e6=Employ(name='甄宓',number='00006',age=16)
e7=Employ(name='貂蝉',number='00007',age=18)
# 2.利用会话的add方法添加实例
Session.add_all([e5,e6,e7])
Session.add(e1)
# 3.提交数据库更改.
Session.commit()
'''

# 查 first()方法返回一个元组,all()返回一个列表
# 查询所有记录
'''
#my_data=Session.query(Employ).filter(Employ.age<=18).all()
# 查询首条记录
#my_data=Session.query(Employ).filter(Employ.age==20).first()
#多条件查询
# my_data=Session.query(Employ).filter(Employ.age==18).filter(Employ.name=='貂蝉').all()
#result=Session.query(Employ).filter_by(name='guanyu').all()# 写的是查询条件
# print(my_data)
'''
# 修改
'''
item=Session.query(Employ).filter(Employ.name=="貂蝉").first()
item.gender='M'
Session.commit()
'''

# 删除
'''
Session.query(Employ).filter(Employ.id==1).delete()
Session.commit()
'''

# 复杂查询
ret = Session.query(Employ).filter(Employ.id > 3, Employ.age < 16).all()
print(ret)
print("-------------------")
ret = Session.query(Employ).filter(Employ.id.between(2, 6), Employ.age > 19).all()
print(ret)
print("-------------------")
ret = Session.query(Employ).filter(Employ.id.in_([1, 3, 4])).all()  # 在范围内
print(ret)
print('---------------------')
ret = Session.query(Employ).filter(~Employ.id.in_([1, 3, 4])).all()  # 不在范围内
print(ret)
print('---------------------')
from  sqlalchemy import and_, or_

ret = Session.query(Employ).filter(and_(Employ.id > 3, Employ.age > 18)).all()
print(ret)
print('------------------------')
ret = Session.query(Employ).filter(or_(Employ.id > 3, Employ.age > 18)).all()
print(ret)
print('------------------------')

# 通配符
ret = Session.query(Employ).filter(Employ.name.like('赵%')).all()
print(ret)
print('------------------------')
ret = Session.query(Employ).filter(~Employ.name.like('赵%')).all()
print(ret)
print('------------------------')

# 切片
ret = Session.query(Employ)[1:2]
print(ret)
print('------------------------')

# 排序
ret = Session.query(Employ).order_by(Employ.age.desc()).all()
print(ret)
print('------------------------')
ret = Session.query(Employ).order_by(Employ.age.desc(), Employ.name).all()
print(ret)
print('------------------------')

# 分组
from sqlalchemy.sql import func

ret = Session.query(
    Employ.gender,
    func.max(Employ.age),
    func.sum(Employ.age)
).group_by(Employ.gender).all()
print('分组:', ret)
print('------------------------')

# 分组后查找出年龄总和大于100的组
ret = Session.query(
    Employ.gender,
    func.max(Employ.age),
    func.sum(Employ.age)
).group_by(Employ.gender).having(func.sum(Employ.age)>100).all()
print('分组:', ret)
print('------------------------')


#连表
#ret =Session.query(Employ,表2).filter(Employ.id==表2.eid).all()#内连表
# ret= Session.query(Employ).join(表2).all()
# ret =Session.query(Employ).join(表2,isouter=True).all()

# 组合
# q1 = Session.query(Users.name).filter(Users.id > 2)
# q2 = session.query(Favor.caption).filter(Favor.nid < 2)
# ret = q1.union(q2).all()
#
# q1 = Session.query(Users.name).filter(Users.id > 2)
# q2 = Session.query(Favor.caption).filter(Favor.nid < 2)
# ret = q1.union_all(q2).all()