SQLAlchemy Part 05 -Delete single and multi rows in table

Part 04 Link here

Delete Single Row (Inefficient way)

user = db_session.query(User).filter(User.id==2).first()
db_session.delete(user)
db_session.commit()

This is inefficient as it first reads the data and then deletes it.

Full Code:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()

# Create ORM Class for User
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(255),unique=True, nullable=False)
    password = Column(String(255),nullable=False)

# Create Engine
SQLALCHEMY_DATABASE_URL = "postgresql://admin:dbpass@localhost:5432/mydb"
engine = create_engine(SQLALCHEMY_DATABASE_URL,pool_size=5,pool_recycle=1800,pool_pre_ping=True)

# Create Session
local_session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
db_session = local_session()

# Retrieve single record
user = db_session.query(User).filter(User.id==1).first()

# Perform Delete Action
db_session.delete(user)

# Commit changes
db_session.commit()

For efficient deleting its better to run PLAIN SQL query.

Delete Single/Multiple Row (Efficient Way)

query = "delete from users where id=2"
with engine.connect() as con:
    rs = con.execute(text(f"""{query}"""))
    con.commit()

Now lets see the full Code

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import text
Base = declarative_base()

# Create ORM Class for User
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(255),unique=True, nullable=False)
    password = Column(String(255),nullable=False)

# Create Engine
SQLALCHEMY_DATABASE_URL = "postgresql://admin:dbpass@localhost:5432/mydb"
engine = create_engine(SQLALCHEMY_DATABASE_URL,pool_size=5,pool_recycle=1800,pool_pre_ping=True)

# Plain SQL Query
query = "delete from users where id=2"
with engine.connect() as con:
    # Execute query post connecting
    rs = con.execute(text(f"""{query}"""))
    # Commit Changes
    con.commit()