# -*- coding: UTF-8 -*-
# Author  :  LinYaoHong
# Date    :  2019/8/6 11:13
# connsqlalchemy.py
# Tools   :  PyCharm

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
Base = declarative_base()

engine = create_engine("mysql+pymysql://root:111111@127.0.0.1/testdb?charset=utf8", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()

import  connsqlalchemy as sql_table

'''
删除

'''

# session.query(sql_table.UserType.id)可以只取对应的列
session.query(sql_table.UserInfo).filter(sql_table.UserInfo.id < 2).delete()

'''
三种类型的修改
'''

# 批量修改
session.query(sql_table.UserInfo).filter(sql_table.UserInfo.id < 3).update({"name": "修改的"})

# synchronize_session=False 字符串相加 更新的类型不同,参数不同，
# 所有的user_type 后面加上 009
session.query(sql_table.UserInfo).filter(sql_table.UserInfo.id < 8).update({sql_table.UserInfo.name: sql_table.UserInfo.name +"009"},synchronize_session=False)

# 所有年龄加 1
session.query(sql_table.UserInfo).filter(sql_table.UserInfo.id < 8).update({"age": sql_table.UserInfo.age + 1}, synchronize_session="evaluate")


session.commit()  # 提交
session.close()   # 关闭