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
--
http://mail.python.org/mailman/listinfo/python-list
