SQLALCHEMY

#creat_table.py

# pip3 install SQLAlchemy
#1通过sqlalchemy创建数据库
# 导入sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

# 2创建orm模型基类
Base = declarative_base()

# 3导入orm对应数据库类型的字段
from sqlalchemy import Column,Integer ,String

# 4创建orm对象
class User(Base):
    __tablename__ = "user"
    id = Column(Integer,primary_key=True,)   #设置主键,自增字段,不必再设置autoincrement=True
    name = Column(String(32),index = True)


# 创建数据库连接
from sqlalchemy import create_engine
#                                        ://数据库用户名:密码@id:端口/数据库名字数据库字符集
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy?charset=utf8")
# 数据库连接创建完成   需要自己手动创建数据库

# 6 数据库中创建与User所对应的数据表
# 去engine数据库中创建所有继承Base类的 ORM对象
Base.metadata.creat_all(engine)

# 增   crud_insert.py

from creat_table import engine
# 创建回话,打开数据库连接
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)   #创建回话窗口
db_session = Session()  #打开回话窗口

from creat_table import User

# 添加数据操作
user = User(name="laotang")
db_session.add(user)
db_session.commit()  #执行会话窗口中的所有操作


# 批量添加数据库的操作
user_list = [User(name="emma"),User(name="lili")]
db_session.add_all(user_list)
db_session.commit()

#删 crud_delete.py

from sqlalchemy.orm import  sessionmaker
from creat_table_ForeignKey import engine ,Student,School
Session = sessionmaker(engine)
db_session = Session()


# 查询并删除数据
sch=db_session.query(School).filter(School.name == "lalala").first()
db_session.query(Student).filter(Student.school_id==sch.id).delete()
db_session.commit()
db_session.close()

#改 crud_update.py

# 更新修改数据
from sqlalchemy.orm import sessionmaker
from creat_table import engine, User

Session = sessionmaker(engine)
db_session = Session()

# 1.修改数据 - 先查询预修改的数据
res = db_session.query(User).filter(User.id==5).update({"name":"lalal3"})

#查 crud.select.py

# 查询
# 会话窗口
from sqlalchemy.orm import sessionmaker
from creat_table import engine

Session = sessionmaker(engine)
db_session = Session()

from creat_table import User

# 1简单查询
# user_list = db_session.query(User).all()


# 2.带条件的查询
# user_list = db_session.query(User).filter(User.id == 4).all()

# user_list = db_session.query(User).filter(User.id >= 4).all()

# user = db_session.query(User).filter_by(id=4).first()

 

#有外键关系的表创建

#一对多 多表操作
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship
Base = declarative_base()

class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    school_id = Column(Integer, ForeignKey("school.id"))  #外键关系  表名

    stu2sch = relationship("School",backref = "sch2stu")   #类名  只是方便正向和反向查询,不会在表中创建字段
# 学生与学校是一对多关系

class School(Base):
    __tablename__ = "school"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))


from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy2?charset=utf8")
Base.metadata.creat_all(engine)

#增  

from creat_table_ForeignKey import engine,Student,School
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()


###笨方法添加数据
school = School(name="beijing_school")
db_session.add(school)  #增加一个学校
db_session.commit()

# 通过学校添加学生
# sch = db_session.query(School).filter(School.name == "beijing_school").first()
# stu_obj = Student(name="DragonFire",school_id=sch.id)
# db_session.add(stu_obj)
# db_session.commit()
# db_session.close()


# 正向添加数据 正向 relationship 版
# stu = Student(name="lilio",stu2sch=School(name="shanghai"))
# db_session.add(stu)
# db_session.commit()

# 反向添加数据 正向 relationship 版

sch = School(name="jiaozuo")
sch.sch2stu=[Student(name="lala1"),Student(name="lala2")]
db_session.add_all(sch)
db_session.commit()

#删 crud_delete_ForeignKey.py

from sqlalchemy.orm import sessionmaker
from creat_table_ForeignKey import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

# # 1.查询数据 - relationship 版 正向
stu_list=db_session.query(Student).all()
for row in stu_list:
    print(row.od,row.name,row.stu2sch.name)



# 2.查询数据 - relationship 版 反向
sch_list=db_session.query(School).all()
for school in sch_list:
    for student in school.sch2stu:
        print(school.name,student.name)

#改 crud_update_ForeignKey.py

# 添加数据
from sqlalchemy.orm import sessionmaker
from creat_table_ForeignKey import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

# 修改数据
sch = db_session.query(School).filter(School.name=="OldBoyShanghai").first()
db_session.query(Student).filter(Student.name == "小笼包").update({"school_id":sch.id})
db_session.commit()
db_session.close()

#查 crud_select_ForeignKey.py

from sqlalchemy.orm import sessionmaker
from creat_table_ForeignKey import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

# # 1.查询数据 - relationship 版 正向
stu_list=db_session.query(Student).all()
for row in stu_list:
    print(row.od,row.name,row.stu2sch.name)



# 2.查询数据 - relationship 版 反向
sch_list=db_session.query(School).all()
for school in sch_list:
    for student in school.sch2stu:
        print(school.name,student.name)

#增强版修改

#高级版更新操作
from creat_table import User,engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

#直接修改
# db_session.query(User).filter(User.id > 0).update({"name" : "099"})

#在原有值基础上添加 - 1
db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)
db_session.commit()

#在原有值基础上添加 - 2
# db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate")
# db_session.commit()

#增强版查询

from creat_table import User, engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

# or 或操作
res1 = db_session.query(User).filter(User.id == 4 or User.name == "DragonFire").all()
# and
# ret = db_session.query(User).filter_by(id=4,name="DragonFire").all()

from sqlalchemy import and_,or_
res2= db_session.query(User).filter(and_(User.id == 3, User.name == 'lala2')).all()
res3 = db_session.query(User).filter(or_(User.id == 4, User.name == 'DragonFire')).all()


# 查询数据 指定查询数据列 加入别名
# r2 = db_session.query(User.id.label("uid"),User.name.label("username")).first()
# print(r2.uid,r2.username) # 15 NBDragon

# 表达式筛选条件
# r3 = db_session.query(User).filter(User.name == "DragonFire").all()


# 筛选查询列
# query的时候我们不在使用User ORM对象,而是使用User.name来对内容进行选取
# user_list = db_session.query(User.name).all()
# for row in user_list:
#     print(row.name)


# user_list = db_session.query(User.name.label("nick")).all()
# print(user_list)
# for row in user_list:
#     print(row.nick) # 这里要写别名了



# 排序 :
# user_list = db_session.query(User).order_by(User.id.asc()).all()
# user_list = db_session.query(User).order_by(User.id.desc()).all()
# for row in user_list:
#     print(row.name,row.id)



"""
ret = session.query(User).filter_by(name='DragonFire').all()
ret = session.query(User).filter(User.id > 1, User.name == 'DragonFire').all()
ret = session.query(User).filter(User.id.between(1, 3), User.name == 'DragonFire').all() # between 大于1小于3的
ret = session.query(User).filter(User.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
ret = session.query(User).filter(~User.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='DragonFire'))).all() 子查询
from sqlalchemy import and_, or_
ret = session.query(User).filter(and_(User.id > 3, User.name == 'DragonFire')).all()
ret = session.query(User).filter(or_(User.id < 2, User.name == 'DragonFire')).all()
ret = session.query(User).filter(
    or_(
        User.id < 2,
        and_(User.name == 'eric', User.id > 3),
        User.extra != ""
    )).all()
# select * from User where id<2 or (name="eric" and id>3) or extra != "" 

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

# 限制
ret = db_session.query(User)[1:2]

# 排序
ret = db_session.query(User).order_by(User.name.desc()).all()
ret = db_session.query(User).order_by(User.name.desc(), User.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = db_session.query(User).group_by(User.extra).all()
ret = db_session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).all()

ret = db_session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()
"""

# ret = db_session.query(User).filter(User.id.between(1, 3)).all() # between 大于1小于3的
# print(ret)
# ret = db_session.query(User).filter(User.id.in_([1,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
# ret = db_session.query(User).filter(~User.id.in_([1,2,3])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
# print(ret)

# from sqlalchemy.sql import and_, or_
#
# ret = db_session.query(User).filter(
#     or_(
#         User.id < 2,
#         and_(User.name == 'eric', User.id > 3),
#         User.name != ""
#     ))
# print(ret)

# 关闭连接
db_session.close()