通过SQLALchemy操作MySQL的基本增删改查

基本框架（

mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]举例

engine=create_engine("mysql+pymysql://root:@localhost:3306/db2?charset=utf8",max_overflow=5)

）

#连接
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
import pymysql
Base = declarative_base()
engine=create_engine("mysql+pymysql://root:@localhost:3306/db2",max_overflow=5)
Session=sessionmaker(bind=engine)
session=Session()


#提交与关闭
session.commit()#提交数据，增删改都需要
session.close()#关闭

创建与删除表格

class UserType(Base):
    __tablename__='usertype'
    id=Column(Integer,primary_key=True,autoincrement=True)
    title=Column(String(32),nullable=True,index=True)

class Users(Base):
    __tablename__='users'
    id=Column(Integer,primary_key=True)
    name=Column(String(32))
    email=Column(String(16))
def create_db(): #创建表
        engine=create_engine("mysql+pymysql://root:@localhost:3306/db2",max_overflow=5)
        Base.metadata.create_all(engine)
def drop_db(): #删除表
        engine = create_engine("mysql+pymysql://root:@localhost:3306/db2", max_overflow=5)
        Base.metadata.drop_all(engine)

增

#增加单条
obj1=UserType(title='普通用户')
session.add(obj1)
#增加多条
objs=[
    UserType(title='白金用户'),
    UserType(title='黑金用户'),
]
session.add_all(objs)

简单查

# 以下相当于sellect xx from UserType where...
usertype_list=session.query(UserType.title).filter(UserType.id>1)#filer里面加的是表达式

删和改

session.query(UserType.title).filter(UserType.id>1).delete()

###修改某一列
##更新全部
session.query(UserType.title).filter(UserType.id==1).update({'title':'黑经'})
###更新字符串
session.query(UserType.title).filter(UserType.id>1).update({UserType.title: UserType.title + "X"}, synchronize_session=False) ##更新全部
###更新数字
session.query(UserType.title).filter(UserType.id>1).update({"id": UserType.id + 1}, synchronize_session="evaluate")

 复杂查询条件

#　条件
#filter_by表示分组？filter里面必须放表达式
ret = session.query(Users).filter_by(name='alex').all() 
#filter里两个条件并列默认and关系
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
#~相当于not in
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
#引入and_和or_模块from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符，若为_则表示一个字符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()#第一个相同时根据第二个排

# 分组，相当于mysql的聚合函数
from sqlalchemy.sql import func

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

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

 连表与组合（外键必须是在SQLALchemy中创建的，否则连表不成功）

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
ret = session.query(Person).join(Favor).all()
ret = session.query(Person).join(Favor, 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()#不去重

 临时表操作

### select * from (select * from tb)as B等价于
q1=session.query(Users).filter(Users.id>1).subquery()
result=session.query(q1).all()
print(result)

 子查询（试验未成功）

SELECT usertype.tid AS usertype_tid, (SELECT users.id, users.name, users.email 
FROM users 
WHERE users.name = usertype.tid) AS anon_1 
FROM usertype

等价于
result = session.query(UserType.tid,session.query(Users).filter(Users.name==UserType.tid).as_scalar())