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:123@localhost: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:123@localhost: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()

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值