python sqlalchemy 大表删除数据

会飞的尼古拉斯

已于 2023-07-06 17:12:02 修改

阅读量1k

点赞数 2

分类专栏： sqlalchemy Python 文章标签： python 数据库

于 2023-07-06 14:52:37 首次发布

本文链接：https://blog.csdn.net/weixin_43632687/article/details/131576548

版权

Python 同时被 2 个专栏收录

127 篇文章 6 订阅

订阅专栏

sqlalchemy

7 篇文章 0 订阅

订阅专栏

我现在有一张很大的表，有多大呢，有你的快乐那么大，数据量贼大。现在呢我想使用sqlalchemy去删除数据。我们知道下面的方式会先将结果查出来，然后再删除

先查询出来然后删除

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

base = declarative_base()

class NewTable(base):
    __tablename__='ellistest'
    column1 = Column(String(255),primary_key=True)
    column2 = Column(String(255),primary_key=True)
    column3 = Column(Text)
    
engine = create_engine("postgresql://postgresadmin:admin123@192.168.214.133:32222/postgresdb")

session = sessionmaker(engine)

Session = session()

Session.query(NewTable).filter(NewTable.column1=='1').delete()
Session.commit()
Session.close()

使用原生sql直接删除

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

base = declarative_base()

class NewTable(base):
    __tablename__='ellistest'
    column1 = Column(String(255),primary_key=True)
    column2 = Column(String(255),primary_key=True)
    column3 = Column(Text)
    
engine = create_engine("postgresql://postgresadmin:admin123@192.168.214.133:32222/postgresdb")

session = sessionmaker(engine)

Session = session()

Session.execute("delete from ellistest where column1='2'")
Session.commit()
Session.close()

非原生sql直接删除

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Text
from sqlalchemy.orm import sessionmaker
from sqlalchemy import and_

base = declarative_base()

class NewTable(base):
    __tablename__='ellistest'
    column1 = Column(String(255),primary_key=True)
    column2 = Column(String(255),primary_key=True)
    column3 = Column(Text)
    
engine = create_engine("postgresql://postgresadmin:admin123@192.168.214.133:32222/postgresdb")

session = sessionmaker(engine)

Session = session()
stmt = NewTable.__table__.delete().where(and_(NewTable.column1=='3',NewTable.column2='3'))
Session.execute(stmt)
Session.commit()
Session.close()