Python之ORM框架sqlachemy

from sqlalchemy import create_engine
from sqlalchemy import Column,String,Integer,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import and_,or_,not_
from sqlalchemy.sql import func

Base = declarative_base()

# class User(Base):
#     # 定义表名
#     __tablename__ = 'users'
#     # 定义id字段，整型，主键，自增
#     id = Column(Integer,primary_key=True,autoincrement=True)
#     # 定义name字段，变长字符串一定要指定长度，不可为空
#     name = Column(String(20),nullable=False)

# 定义一个员工信息表
# class Employee(Base):
#     # 定义表名
#     __tablename__ = 'employee'
#     # 定义字段
#     id = Column(Integer,autoincrement=True,primary_key=True)
#     name = Column(String(20),nullable=False)
#     salary = Column(Integer,nullable=False)
#     department = Column(Integer,nullable=False)

class Classes(Base):
    # 定义表名
    __tablename__ = 'classes'
    cid = Column(Integer,autoincrement=True,primary_key=True)
    name = Column(String(10),nullable=False)

class Userinfo(Base):
    # 定义表名
    __tablename__ = 'userinfo'
    uid = Column(Integer,autoincrement=True,primary_key=True)
    name = Column(String(10),nullable=False)
    # 设置外键约束
    class_id = Column(Integer,ForeignKey(Classes.cid))
    # 创建关系
    usr_cls = relationship(Classes,backref='cls_usr')

def sqlengine():
    # 连接数据库,最大连接数为5
    engine = create_engine('mysql+pymysql://root:123456@192.168.1.124:3306/DB3?charset=utf8',max_overflow=5)
    return engine
# 执行sql转化与建表te_all(sqlengine())
# 删除表
# Base.metadata.drop_all(sqlengine())
# 定义为函数
# Base.metadata.crea
def create_db():
    Base.metadata.create_all(sqlengine())

def drop_db():
    Base.metadata.drop_all(sqlengine())

# create_db()
# drop_db()

# 操作表需要获取一个session相当于一个连接
Session = sessionmaker(bind=sqlengine())
session = Session()

# 增
# 插入一条数据
# row1 = User(name='张三') # 创建一个行对象
# session.add(row1) # 将数据写入表中
# session.commit() # 提交数据
# session.close() # 关闭连接
# users表插入多条数据
# row_lst = [
#     User(name='李四'),
#     User(name='高渐离'),
#     User(name='大铁锤'),
#     User(name='班大师'),
#     User(name='盖聂')
# ]
# session.add_all(row_lst)
# session.commit()
# session.close()

# employee插入多条数据
# row_lst = [
#     Employee(name='李四',salary=300,department=0),
#     Employee(name='高渐离',salary=400,department=2),
#     Employee(name='大铁锤',salary=500,department=4),
#     Employee(name='班大师',salary=600,department=3),
#     Employee(name='盖聂',salary=700,department=1),
#     Employee(name='卫庄',salary=800,department=2),
#     Employee(name='高渐离',salary=900,department=1),
#     Employee(name='荆轲',salary=1000,department=3),
#     Employee(name='丽姬',salary=1400,department=4),
#     Employee(name='白凤',salary=1300,department=0),
#     Employee(name='墨鸦',salary=1200,department=3),
#     Employee(name='颜路',salary=1500,department=1),
#     Employee(name='张良',salary=2600,department=2),
#     Employee(name='伏念',salary=4500,department=3),
#     Employee(name='星魂',salary=6000,department=4),
#     Employee(name='逍遥子',salary=8900,department=0),
#     Employee(name='晓梦',salary=12000,department=0),
#     Employee(name='韩非',salary=23600,department=1),
#     Employee(name='扶苏',salary=100,department=3),
# ]

classes_lst = [
    Classes(name='墨家'),
    Classes(name='纵横'),
    Classes(name='儒家'),
    Classes(name='燕国'),
    Classes(name='秦国'),
    Classes(name='流沙'),
    Classes(name='夜幕'),
    Classes(name='阴阳家'),
    Classes(name='道家')
]

userinfo_lst = [
    Userinfo(name='高渐离',class_id=1),
    Userinfo(name='大铁锤',class_id=1),
    Userinfo(name='班大师',class_id=1),
    Userinfo(name='盖聂',class_id=2),
    Userinfo(name='卫庄',class_id=2),
    Userinfo(name='荆轲',class_id=4),
    Userinfo(name='丽姬',class_id=5),
    Userinfo(name='白凤',class_id=6),
    Userinfo(name='墨鸦',class_id=7),
    Userinfo(name='颜路',class_id=3),
    Userinfo(name='张良',class_id=3),
    Userinfo(name='伏念',class_id=3),
    Userinfo(name='星魂',class_id=8),
    Userinfo(name='逍遥子',class_id=9),
    Userinfo(name='晓梦',class_id=9),
    Userinfo(name='韩非',class_id=4),
    Userinfo(name='扶苏',class_id=5)
]

# session.add_all(row_lst)
# session.commit()

# classes表中插入数据
# session.add_all(classes_lst)
# userinfo表中插入数据
# session.add_all(userinfo_lst)
# session.commit()
# session.close()


# 删
# 删除一条数据delete from users where id=1
# session.query(User.id,User.name).filter(User.id==1).delete()
# session.commit()
# 查
# 查询所有--相当于select * from users
# res_lst = session.query(User).all()
# for line in res_lst: # line 是(行对象)每一行数据
#     print(line.id,line.name) # 获取字段

# 多条件查询
# 相当于:select * from employee where id>4 and id<10;
# res_lst = session.query(Employee).filter(Employee.id>4,Employee.id<10).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# filter传的是表达式,而filter_by传的是值
# res = session.query(Employee).filter_by(name='卫庄').one()
# print(res.id,res.name,res.salary)

# filter默认多条件之间是and的逻辑关系
# res_lst = session.query(Employee).filter(and_(Employee.id>4,Employee.id<10)).all()
# res_lst = session.query(Employee).filter(Employee.id>4,Employee.id<10).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# between and用法 相当于：select * from employee where id between 4 and 10
# res_lst = session.query(Employee).filter(Employee.id.between(4,10)).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# in用法 相当于：select * from employee where id in (1,4,7);
# res_lst = session.query(Employee).filter(Employee.id.in_([1,4,7])).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)
# not in用法 相当于：select * from employee where id not in (1,4,7);
# res_lst = session.query(Employee).filter(Employee.id.notin_([1,4,7])).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# in 子语句查询 相当于select * from employee where id in(select id from employee where id>5);
# res_lst = session.query(Employee).filter(Employee.id.in_(session.query(Employee.id).filter(Employee.id>5))).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# 逻辑或 or 相当于：select * from employee where id<4 or id>7;
# res_lst = session.query(Employee).filter(or_(Employee.id<4,Employee.id>7)).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# 逻辑非 not 相当于:select * from employee where id!=1 and id!=19 and id!=10;
# res_lst = session.query(Employee).filter(not_(or_(Employee.id==1,Employee.id==19,Employee.id==10))).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# 模糊查询 like select * from employee where name like '张%';
# res_lst = session.query(Employee).filter(Employee.name.like('张%')).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)
# 模糊查询 not like 相当于select * from employee where name not like '张%';
# res_lst = session.query(Employee).filter(Employee.name.notlike('张%'))
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# 分页 相当于select * from employee limit 10,5;
# res_lst = session.query(Employee)[10:15]
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# 排序查询 -- 降序 相当于：select * from employee order by id desc;
# res_lst = session.query(Employee).order_by(Employee.id.desc()).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)
# 排序查询 -- 升序(默认) 相当于：select * from employee order by id;
# res_lst = session.query(Employee).order_by(Employee.id.asc()).all()
# res_lst = session.query(Employee).order_by(Employee.id).all()
# for line in res_lst:
#     print(line.id,line.name,line.salary)

# 分组查询
# res_lst = session.query(func.count(Employee.salary)).group_by(Employee.salary).all()
# for line in res_lst:
#     print(line[0])

# 分组查询之聚合函数
# res_lst = session.query(
#     func.max(Employee.salary),
#     func.sum(Employee.salary),
#     func.min(Employee.salary)
# ).group_by(Employee.department).all()
# for line in res_lst:
#     print(line[0],line[1],line[2])

# 聚合函数判断(having) 统计平均值大于5000的部门 相当于:select department,avg(salary) from employee group by department having avg(salary)>5000;
# res_lst = session.query(
#     Employee.department,
#     func.avg(Employee.salary)
# ).group_by(Employee.department).having(func.avg(Employee.salary) > 5000).all()
# for line in res_lst:
#     print(line[0],line[1])

# 连表查询
# ret = session.query(Userinfo,Classes).filter(Userinfo.class_id == Classes.cid)
# print(ret)
'''
转化后的语句
SELECT userinfo.uid AS userinfo_uid, userinfo.name AS userinfo_name, userinfo.class_id AS userinfo_class_id, classes.cid AS classes_cid, classes.name AS classes_name 
FROM userinfo, classes 
WHERE userinfo.class_id = classes.cid
'''
# ret = session.query(Userinfo).join(Classes)
# print(ret)
'''
转化后结果
SELECT userinfo.uid AS userinfo_uid, userinfo.name AS userinfo_name, userinfo.class_id AS userinfo_class_id 
FROM userinfo INNER JOIN classes ON classes.cid = userinfo.class_id
'''
# 左连接
# ret = session.query(Userinfo).join(Classes,isouter=True)
# print(ret)
'''
转化后的结果
SELECT userinfo.uid AS userinfo_uid, userinfo.name AS userinfo_name, userinfo.class_id AS userinfo_class_id 
FROM userinfo LEFT OUTER JOIN classes ON classes.cid = userinfo.class_id
'''
# 以上语句加上.all()及执行查询

# 组合查询 相当于:select name from userinfo union select name from classes; 且union有去重的功能,不需要去重则将"union"替换为"union all(sqlachemy中是union_all)"
# q1 = session.query(Userinfo.name).filter(Userinfo.uid>16)
# for i in q1:
#     print(i)
'''
('晓梦',)
('韩非',)
('扶苏',)
'''
# q2 = session.query(Classes.name).filter(Classes.cid>7)
# for i in q2:
#     print(i)
'''
('阴阳家',)
('道家',)
'''
# res_lst = q1.union(q2).all()
# for line in res_lst:
#     print(line)
'''
('晓梦',)
('韩非',)
('扶苏',)
('阴阳家',)
('道家',)
'''

# 子查询--使用subquery将指定语句变为子语句
# tmp = session.query(Userinfo).filter(Userinfo.uid<5).all()
# for i in tmp:
#     print(i.uid,i.name,i.class_id)
'''
3 高渐离 1
4 大铁锤 1
'''
# q1 = session.query(Userinfo).filter(Userinfo.uid<5).subquery()
# res_lst = session.query(q1)
# for line in res_lst:
#     print(line)
'''
(3, '高渐离', 1)
(4, '大铁锤', 1)
'''

# 子查询2--印射：select name,(select name from classes where cid=userinfo.class_id) from userinfo;(查询人名与班级名)
# res_lst = session.query(Userinfo.name,session.query(Classes.name).filter(Classes.cid==Userinfo.class_id).as_scalar())
# for line in res_lst:
#     print(line)
'''
('高渐离', '墨家')
('大铁锤', '墨家')
('班大师', '墨家')
('盖聂', '纵横')
('卫庄', '纵横')
('荆轲', '燕国')
('丽姬', '秦国')
('白凤', '流沙')
('墨鸦', '夜幕')
('颜路', '儒家')
('张良', '儒家')
('伏念', '儒家')
('星魂', '阴阳家')
('逍遥子', '道家')
('晓梦', '道家')
('韩非', '燕国')
('扶苏', '秦国')
'''

# 创建relationship(写在外键的表中)正向查找,简便方法实现连表操作的效果,获取名称归属于哪一个班级
# res_lst = session.query(Userinfo)
# for line in res_lst:
#     print(line.name,line.usr_cls.name)
'''
高渐离 墨家
大铁锤 墨家
班大师 墨家
盖聂 纵横
卫庄 纵横
荆轲 燕国
丽姬 秦国
白凤 流沙
墨鸦 夜幕
颜路 儒家
张良 儒家
伏念 儒家
星魂 阴阳家
逍遥子 道家
晓梦 道家
韩非 燕国
扶苏 秦国
'''

# 反向查找  获取班级下的人名
# res_lst = session.query(Classes)
# for line in res_lst:
#     print(line.cid,line.name,[i.name for i in line.cls_usr])
'''
1 墨家 ['高渐离', '大铁锤', '班大师']
2 纵横 ['盖聂', '卫庄']
3 儒家 ['颜路', '张良', '伏念']
4 燕国 ['荆轲', '韩非']
5 秦国 ['丽姬', '扶苏']
6 流沙 ['白凤']
7 夜幕 ['墨鸦']
8 阴阳家 ['星魂']
9 道家 ['逍遥子', '晓梦']
'''

# res = session.query(User.id,User.name).filter(User.id==1).one()
# print(res) # (1, '张三')
# 改
# 修改某个字段
# session.query(User.name).filter(User.name=='李四').update({User.name:'雪女'})
# session.commit()
# session.close()
# 某个字段增加信息--字符串类型
# session.query(User.name).filter(User.id > 4).update({User.name:User.name + '秦时明月'},synchronize_session=False)
# session.commit()
# session.close()
# 数字字段加某个值--涨工资(1500)
# session.query(Employee.salary).filter(Employee.id > 4).update({Employee.salary:Employee.salary + 1500},synchronize_session='evaluate')
# session.commit()
# session.close()