Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > sqlalchemy: delete() on m:n-relationship

Reply
Thread Tools

sqlalchemy: delete() on m:n-relationship

 
 
Wolfgang Meiners
Guest
Posts: n/a
 
      05-19-2012
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off




Advertisments