代码之家 › 专栏 › 技术社区 › Sophus

python/sqlacalchemy:如何删除分配表中的记录?

associations sqlalchemy python

Sophus · 技术社区 · 6 年前

下面我给您带来了一个可执行程序。这个程序中有一些注释,可以使情况更容易理解。请阅读评论。我想要什么?我希望Pogram只删除/删除分配表中的记录( 分配电影类型 )-表中的行不是这样的电影或体裁 . 如您所见,在这个例子中,我将电影“saw”指定为类型“comedy”。故意的错误。现在我想解决这个星座,但我不想从数据库中删除“喜剧”和“锯”,只删除任务。但是如何呢?

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

sqlite_url = 'sqlite:///test.sqlite'

engine = sqlalchemy.create_engine(sqlite_url, echo = True)

Base = declarative_base()


class Allocation_Film_Genre(Base):
  __tablename__ = 'allocation_film_genre'
  genre_id = Column(Integer, ForeignKey('genre.id'), primary_key=True)
  film_id = Column(Integer, ForeignKey('film.id'), primary_key=True)

  genre = relationship("Genre", backref=backref("allocation_film_genre", lazy='dynamic', cascade="all, delete-orphan" ))
  film = relationship("Film", backref=backref("allocation_film_genre", lazy='dynamic', cascade="all, delete-orphan" ))

class Film(Base):
  __tablename__ = 'film'
  id = Column(Integer,  primary_key=True, unique=True)
  name = Column(String(80))

class Genre(Base):
  __tablename__ = 'genre'
  id = Column(Integer,  primary_key=True, unique=True)
  name = Column(String(80))

# Let us create all tables with certain columns
Base.metadata.create_all(engine)

# Now we have to create a session to work with.
Session = sessionmaker(bind=engine)
session = Session()

# We want to save some movies
film1 = Film(name="Saw")
film2 = Film(name="Amageddon")
film3 = Film(name="Little Stuart")
film4 = Film(name="Doom Day")

session.add_all([film1, film2, film3, film4])
session.commit()

# By the way we also want to save some genres 
genre1 = Genre( name = "Horror")
genre2 = Genre( name = "Comedy")
genre3 = Genre( name = "Psycho")
genre4 = Genre( name = "Thriller")

session.add_all([genre1, genre2, genre3, genre4])
session.commit()

# Hold on, we know we created an allocation table, because
# one movie can contains one or more genre, otherwise, one genre
# also can contains one or more movie, right? Let take us a look.
# For simulate we use the movie named 'Saw".
film_obj1 = session.query(Film).filter(Film.name=="Saw").one()
genre_obj1 = session.query(Genre).filter(Genre.name=="Horror").one()

film_obj2 = session.query(Film).filter(Film.name=="Saw").one()
genre_obj2 = session.query(Genre).filter(Genre.name=="Psycho").one()

film_obj3 = session.query(Film).filter(Film.name=="Saw").one()
genre_obj3 = session.query(Genre).filter(Genre.name=="Comedy").one()

allocation1 = Allocation_Film_Genre(film=film_obj1, genre=genre_obj1)
allocation2 = Allocation_Film_Genre(film=film_obj2, genre=genre_obj2)
allocation3 = Allocation_Film_Genre(film=film_obj3, genre=genre_obj3)

session.add_all([allocation1, allocation2, allocation3])
session.commit()

# Ok, we are done. Alle movies and genre are saved, and we also saved all
# allocation records. But wait! There is a mistake. Saw isn't a comedy. Damn!
# Shame on me!

# And now, I don't know what I have to do.

1 回复 | 直到 6 年前

mad_ 6 年前

from sqlalchemy import and_

allocation_obj_to_delete _list=session.query(Allocation_Film_Genre).join(Film).join(Genre).filter(and_(Film.name=='Saw',Genre.name=="Comedy")).all()

for obj in allocation_obj_to_delete:
    session.delete(obj)
session.commit()

联合查询和删除对象