Python3中sqlalchemy模块学习之删除操作

起步

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""删"""

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


engine = create_engine('mysql+pymysql://root:[email protected]:3306/sqlalchemy_db?charset=utf8', max_overflow=10, echo=True)

Base = declarative_base()

# 多对一：假设多个员工可以属于一个部门，而多个部门不能有同一个员工


class Dep(Base):
    __tablename__ = 'dep'
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)


class Emp(Base):
    __tablename__ = 'emp'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey(column='dep.id'))


def init_db():
    Base.metadata.create_all(bind=engine)


def drop_db():
    Base.metadata.drop_all(bind=engine)


# 想要增删改查，就得先拿连接
session = sessionmaker(bind=engine)()

删

相当于 delete from dep;

affected = session.query(Dep).delete()
session.commit()

使用delete高性能删除

affected = session.query(Dep).filter(1 == 1).delete()
session.commit()
print(affected)  # int, 返回受影响的行数数目
session.close()

按条件删
相当于 delete from dep where dep.id > 0;

affected = session.query(Dep).filter(Dep.id > 0).delete()
session.commit()
print(affected)
session.close()

整体代码

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""删"""

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


engine = create_engine('mysql+pymysql://root:[email protected]:3306/sqlalchemy_db?charset=utf8', max_overflow=10, echo=True)

Base = declarative_base()

# 多对一：假设多个员工可以属于一个部门，而多个部门不能有同一个员工


class Dep(Base):
    __tablename__ = 'dep'
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)


class Emp(Base):
    __tablename__ = 'emp'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey(column='dep.id'))


def init_db():
    Base.metadata.create_all(bind=engine)


def drop_db():
    Base.metadata.drop_all(bind=engine)


# 想要增删改查，就得先拿连接
session = sessionmaker(bind=engine)()

# 删
# 相当于delete from dep;
# affected = session.query(Dep).delete()
# session.commit()

# 使用delete高性能删除
# affected = session.query(Dep).filter(1 == 1).delete()
# session.commit()
# print(affected)  # int, 返回受影响的行数数目

# 更改自增值
engine.execute('alter table dep auto_increment=1;')

# 按条件删
# 相当于delete from dep where dep.id > 0;
affected = session.query(Dep).filter(Dep.id > 0).delete()
session.commit()
print(affected)

# 关闭连接
session.close()