sqlalchemy: delete() on m:n-relationship

Discussion in 'Python' started by Wolfgang Meiners, May 19, 2012.

  1. Hi all,

    i dont understand, how sqlalchemy deletes from m:n relationships.

    Maybe, someone can explain to me, how to delete in the following program:

    (pyhton3, sqlalchemy 0.7.0)

    =====================================================================
    > #!/usr/bin/env python3
    > # -*- coding: utf-8 -*-
    >
    > '''
    > Created on 19.05.2012
    >
    > @author: wolfgang
    >
    > '''
    >
    > from sqlalchemy import *
    >
    > from sqlalchemy.orm.session import sessionmaker
    > from sqlalchemy.orm import relationship, backref
    > from sqlalchemy.ext.declarative import declarative_base
    >
    >
    > Base = declarative_base()
    >
    > class Book(Base):
    > __tablename__='books'
    >
    > def __init__(self, title, authors):
    > # here authors is a list of items of type Autor
    > self.title = title
    > for author in authors:
    > self.authors.append(author)
    >
    > bid = Column(Integer, primary_key=True)
    > title = Column(String, index=True)
    >
    > authors = relationship('Author', secondary='author_book',
    > backref=backref('books', order_by='Book.title', cascade='all, delete'),
    > cascade='all, delete')
    >
    > class Author(Base):
    > __tablename__ = 'authors'
    >
    > def __init__(self, name):
    > self.name = name
    >
    > aid = Column(Integer, primary_key=True)
    > name = Column(String, index=True)
    >
    >
    > # Association table between authors and books:
    > author_book = Table('author_book', Base.metadata,
    > Column('aid', Integer, ForeignKey('authors.aid'), primary_key=True),
    > Column('bid', Integer, ForeignKey('books.bid'), primary_key=True))
    >
    >
    > class DB:
    > def __init__(self, dbname=None, echo=False):
    > self.dbname = dbname if dbname else ':memory:'
    > self.dbfile = 'sqlite:///{db}'.format(db=self.dbname)
    > self.engine = create_engine(self.dbfile)
    > Base.metadata.create_all(self.engine)
    > self.Session = sessionmaker(self.engine)
    >
    > def find_or_create_author(session, name):
    > qauthor = session.query(Author).filter_by(name=name)
    > if qauthor.count() == 0:
    > session.add(Author(name=name))
    > return qauthor.one()
    >
    > if __name__ == '__main__':
    >
    > db = DB(dbname='booksdb.sqlite', echo=True)
    > session = db.Session()
    >
    > # insert 4 books into db
    > session.add_all([Book(title='Title a',
    > authors=[find_or_create_author(session, name='Author 1'),
    > find_or_create_author(session, name='Author 2')]),
    > Book(title='Title b',
    > authors=[find_or_create_author(session, name='Author 1'),
    > find_or_create_author(session, name='Author 2')]),
    > Book(title='Title c',
    > authors=[find_or_create_author(session, name='Author 3'),
    > find_or_create_author(session, name='Author 4')]),
    > Book(title='Title d',
    > authors=[find_or_create_author(session, name='Author 3'),
    > find_or_create_author(session, name='Author 4')])])
    >
    > session.commit()
    >
    > # At this point there are 4 book in db, the first 2 written by Author 1 and Author 2,
    > # the last 2 written by Author 3 and Author 4.
    > # Now, i delete books with bid == 1 and bid == 3:
    >
    > book1 = session.query(Book).filter_by(bid=1).one()
    > session.delete(book1)
    >
    > session.query(Book).filter_by(bid=3).delete()
    >
    > session.commit()
    >
    > # The first query deletes to much: Title b is related to Author 1 and Author 2
    > # this relation has dissapeared from the db
    >
    > # The last query deletes to less: There is no Title 3, but the entries
    > # of this book remain in the associationtable.
    >
    > # How is this done right?

    ==========================================================================================

    after i run this program, the contents of booksdb.sqlite has the
    following data:

    $ sqlite3 booksdb.sqlite
    SQLite version 3.6.12
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    sqlite> select * from author_book;
    3|3
    4|3
    3|4
    4|4

    sqlite> select * from
    ...> books natural inner join author_book
    ...> natural inner join authors;
    4|Title d|3|Author 3
    4|Title d|4|Author 4

    which means, association between Title b and ist authors is lost,
    information on Title c is still in author_book table.

    Thank you for any help

    Wolfgang
     
    Wolfgang Meiners, May 19, 2012
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. davout
    Replies:
    0
    Views:
    614
    davout
    Apr 18, 2004
  2. sylvian

    SQLAlchemy problem : /

    sylvian, Aug 31, 2006, in forum: Python
    Replies:
    0
    Views:
    338
    sylvian
    Aug 31, 2006
  3. John Salerno

    SQLObject or SQLAlchemy?

    John Salerno, Aug 31, 2006, in forum: Python
    Replies:
    20
    Views:
    1,285
    lazaridis_com
    Sep 6, 2006
  4. Replies:
    1
    Views:
    279
    Bruno Desthuilliers
    Oct 9, 2006
  5. Karlo Lozovina

    SQLAlchemy and py2exe

    Karlo Lozovina, Oct 21, 2006, in forum: Python
    Replies:
    1
    Views:
    487
    Steve Holden
    Oct 21, 2006
Loading...

Share This Page