SQLAlchemy "AttributeError: 'str' object has no attribute 'c'"

Question

I have two tables named users and permissions and I wanted to create a relationship between them using a table named userPermissions. here's how my code looks like:

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    first_name = Column(Text)
    last_name = Column(Text, nullable=True)

class Permission(Base):
    __tablename__ = 'permissions'

    id = Column(Integer, primary_key=True)
    title = Column(String(64))
    allow_anonymous = Column(Boolean)

class UserPermission(Base):
    __table__ = 'userPermissions'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    permission_id = Column(Integer, ForeignKey('permissions.id'))
    value = Column(Boolean)

I know I might be doing relationships incorrectly but looking through docs and searching, I couldn't find what it is. When I try to create the tables using db.Base.metadata.create_all(db.engine) it gives me the following error:

/usr/bin/python3.6 /path/project/out.py
Traceback (most recent call last):
  File "/path/project/out.py", line 1, in <module>
    from components.database import setup
  File "/path/project/components/database/__init__.py", line 41, in <module>
    class UserPermission(Base):
  File "/home/user/.local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/api.py", line 65, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/home/user/.local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 116, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/home/user/.local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 144, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/home/user/.local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 172, in __init__
    self._setup_table()
  File "/home/user/.local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 481, in _setup_table
    if not table.c.contains_column(c):
AttributeError: 'str' object has no attribute 'c'

Where is the problem?

Answer 1

In your UserPermission class, you are using the wrong dunder attribute:

__table__ = 'userPermissions'

Should be:

__tablename__ = 'userPermissions'

Sqlalchemy is trying to treat the string 'userPermissions' as a Table object.

Regarding the difference between __table__ and __tablename__, most cases will only require declaring __tablename__ = "stringvalue" on a declarative class. It signals that the object should reference a table of that name, and SQLAlchemy can handle the construction of that Table object internally.

Declaring a __table__ on the object instead signals to SQLAlchemy that you want to take control of the construction of the Table that the ORM class represents. This would be most useful if you already have a reference to the table from some other means like table reflection. More reading here.

Answer 2

class UserPermission(Base):
    __table__ = 'userPermissions'

here should be :

class UserPermission(Base):
    __tablename__ = 'userPermissions'