CODEWITHSUNDEEP
pythondatabasesqlalchemy
jerboa
jerboa

Reputation: 1421

how to use many-to-many in sqlalchemy

I'd like to use relationship from sqlalchemy in my project. I test many-to-many on simple code:

from sqlalchemy import Table, Column, Integer, String, Text, DateTime, ForeignKey, create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime


engine = create_engine('sqlite:///m2m.sqlite', echo=True)

Base = declarative_base(engine)

post_tags = Table('post_tags', Base.metadata,
                Column('post_id', Integer, ForeignKey('blog_posts.id')),
                Column('tag_name', String, ForeignKey('blog_tags.name'))
)

class BlogPost(Base):
    __tablename__ = 'blog_posts'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String)
    content = Column(Text)
    created = Column(DateTime, default=datetime.now)   

    tags = relationship('BlogTag', secondary=post_tags, backref='blog_posts')

    def __init__(self, title, content, tags=None):
        self.title = title
        self.content = content
        if tags:
            self.tags = tags

    def __repr__(self):
        return '%d %s %s' % (self.id, self.title, self.content)

class BlogTag(Base):
    __tablename__ = 'blog_tags'

    name = Column(String, primary_key=True)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '%s' % self.name

Base.metadata.create_all(engine)

Session = sessionmaker(engine)
dbs = Session()

post = BlogPost('1', '1111', [BlogTag('one'), BlogTag('two')])
dbs.add(post)
dbs.commit()

post = BlogPost('2', '222', [BlogTag('one'), BlogTag('newtag')])
dbs.add(post)
dbs.commit()

for x in dbs.query(BlogTag).all():
    print x

But I get exception on code:

post = BlogPost('2', '222', [BlogTag('one'), BlogTag('newtag')])
dbs.add(post)
dbs.commit()

How can I insert a new blog post to database if tags already exists in the table?

Thanks to everybody. I write follows code for BlogTag:

class BlogTag(Base):
    __tablename__ = 'blog_tags'

    name = Column(String, primary_key=True)

    @staticmethod
    def get(dbsession, name):
        obj = dbsession.query(BlogTag).filter(BlogTag.name == name).first()
        if not obj:
            obj = BlogTag(name)
        return obj

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '%s' % self.name

And I used them like below:

tags = [BlogTag.get(dbs, 'one'), BlogTag.get(dbs, 'two')]

post = BlogPost('1', '1111', tags)
dbs.add(post)
dbs.commit()

tags = [BlogTag.get(dbs, 'one'), BlogTag.get(dbs, 'newtag')]
post = BlogPost('2', '222', tags)
dbs.add(post)
dbs.commit()

I'm not sure that it is best way but it work :)

Can I get session from Base?

Upvotes: 2

Views: 2254

Answers (2)

codeape
codeape

Reputation: 100756

The problem here is that you create two BlogTag objects with primary key one.

SQLAlchemy's session object has no way of knowing that it should not create a new database row for the second object you create.

The following will work:

tag_one = BlogTag('one')
post = BlogPost('1', '1111', [tag_one, BlogTag('two')])
dbs.add(post)
dbs.commit()

post = BlogPost('2', '222', [tag_one, BlogTag('newtag')])
dbs.add(post)
dbs.commit()

You could also do the following (but note that dbs.query(BlogTag).get('one') will execute an extra SELECT against the database, not needed in the first example):

post = BlogPost('1', '1111', [BlogTag('one'), BlogTag('two')])
dbs.add(post)
dbs.commit()

post = BlogPost('2', '222', [dbs.query(BlogTag).get("one"), BlogTag('newtag')])
dbs.add(post)
dbs.commit()

Upvotes: 3

Rafał Rawicki
Rafał Rawicki

Reputation: 22690

BlogPost('one') creates completely new object. You have to fetch existing tags from database instead.

Also, in the example above you forgot to add a new tag to the database session.

Upvotes: 2

Related Questions