Understanding the Error

When working with many-to-many or one-to-many relationships in SQLAlchemy, assigning a raw string (or a set of strings) directly to a relationship field triggers the following error:

TypeError: Incompatible collection type: str is not list-like

This occurs because SQLAlchemy's relationship() expects a collection (typically a Python list) of model instances, not primitive types like str or raw data values.

Why This Happens

In your Post model, the tags attribute is configured as a many-to-many relationship pointing to the Tag model:

tags = relationship("Tag", secondary=posts_tags, back_populates="posts")

When you instantiate a Post and pass tags="First Tag" or tags={"First Tag"}, SQLAlchemy tries to populate the collection. It encounters a primitive string (or a set of strings) instead of a list of Tag instances (e.g., [Tag(tag_name="First Tag")]), causing the type check to fail.

How to Fix It

1. Pass a List of Model Instances

To populate the relationship properly, wrap your values inside actual Tag objects within a list:

first_tag = Tag(tag_name="First Tag")
second_tag = Tag(tag_name="Second Tag")
long_tag = Tag(tag_name="This Is A Long String Of Text For A Tag!")

posts = [
    Post(
        title="First Post!",
        body="This is my first post!",
        tags=[first_tag]
    ),
    Post(
        title="Second Post, With More Text",
        body="Lorem ipsum dolor sit amet...",
        tags=[second_tag, long_tag]
    )
]

2. Reuse Existing Tags (Preventing Duplicate Key Errors)

Since Tag.tag_name is marked as unique=True, you should check whether a tag already exists in the database before creating a new instance. A helper function like get_or_create can streamline this:

def get_or_create_tag(session, tag_name):
    tag = session.query(Tag).filter_by(tag_name=tag_name).first()
    if not tag:
        tag = Tag(tag_name=tag_name)
        session.add(tag)
    return tag

# Usage:
post = Post(
    title="First Post!",
    body="This is my first post!",
    tags=[get_or_create_tag(session, "First Tag")]
)

Additional Fixes in Your Code

While fixing the relationship issue, keep an eye out for a few other common bugs in the provided snippet:

  • date = DateTime(): DateTime is a SQLAlchemy column type, not a timestamp value. Either omit this argument so your server_default=func.now() handles it automatically, or pass a valid Python datetime object (e.g., datetime.now()).
  • self.name in Tag.__repr__: The attribute defined on Tag is tag_name, not name. Accessing self.name will raise an AttributeError.
  • post.post_id: The primary key on Post is defined as id, so access it via post.id instead of post.post_id.

Complete Working Example

Here is the revised, fully working script:

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

Base = declarative_base()

posts_tags = Table(
    "posts_tags",
    Base.metadata,
    Column("post_id", Integer, ForeignKey("posts.id")),
    Column("tag_id", Integer, ForeignKey("tags.id")),
)

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    body = Column(Text)
    date = Column(DateTime(), server_default=func.now())
    tags = relationship("Tag", secondary=posts_tags, back_populates="posts")

    def __repr__(self):
        return f"<Post(title='{self.title}')>"

class Tag(Base):
    __tablename__ = "tags"
    id = Column(Integer, primary_key=True)
    tag_name = Column(String(50), unique=True, nullable=False)
    posts = relationship("Post", secondary=posts_tags, back_populates="tags")

    def __repr__(self):
        return f"<Tag(tag_name='{self.tag_name}')>"

# Database setup (SQLite in-memory for testing)
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
SessionLocal = sessionmaker(bind=engine)
session = SessionLocal()

# Creating and associating records correctly
tag1 = Tag(tag_name="First Tag")
tag2 = Tag(tag_name="Second Tag")
tag3 = Tag(tag_name="Long Tag")

posts = [
    Post(
        title="First Post!",
        body="This is my first post!",
        tags=[tag1]
    ),
    Post(
        title="Second Post, With More Text",
        body="Lorem ipsum dolor sit amet...",
        tags=[tag2, tag3]
    )
]

session.add_all(posts)
session.commit()

for post in posts:
    print(f"Added: {post} with ID: {post.id} and Tags: {post.tags}")

session.close()

Bonus Tip: Using association_proxy

If you want to assign string values directly to post.tags without manually wrapping them in Tag(...) instances, you can use SQLAlchemy's association_proxy extension:

from sqlalchemy.ext.associationproxy import association_proxy

# Inside your Post model:
# _tags = relationship("Tag", secondary=posts_tags)
# tags = association_proxy("_tags", "tag_name", creator=lambda name: Tag(tag_name=name))

# Now you can assign strings directly:
# post = Post(title="First Post!", tags=["First Tag", "Second Tag"])