python - How to make alembic autogenerate foreign key relations? - Stack Overflow

admin管理员组

文章数量:1296312

I'm trying to get Alembic to autogenerate a first version of my database, which is at this point very simple. The command works, and generates a migration with all of my tables and columns, except all ForeignKey relations.

My code looks like this:

from typing import List
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped, relationship


class Base(DeclarativeBase):
    pass


class DBUnitCategory(Base):
    __tablename__ = 'unit_category'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(20))

    unit_id: Mapped[List["DBUnit"]] = relationship(back_populates="category_id")

class DBUnit(Base):
    __tablename__ = 'unit'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(12))

    category_id: Mapped[DBUnitCategory] = relationship(back_populates="unit_id")

And the generated upgrade migration looks like this:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    
    op.create_table('unit',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=12), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('unit_category',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=20), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('unit_category')
    op.drop_table('unit')

I don't think I messed up my definitions, it looks like in the tutorial to me. There is no issue with this on the github, so I don't understand what's going on. Is it related to the fact I'm using an sqlite database?

I'm trying to get Alembic to autogenerate a first version of my database, which is at this point very simple. The command works, and generates a migration with all of my tables and columns, except all ForeignKey relations.

My code looks like this:

from typing import List
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped, relationship


class Base(DeclarativeBase):
    pass


class DBUnitCategory(Base):
    __tablename__ = 'unit_category'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(20))

    unit_id: Mapped[List["DBUnit"]] = relationship(back_populates="category_id")

class DBUnit(Base):
    __tablename__ = 'unit'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(12))

    category_id: Mapped[DBUnitCategory] = relationship(back_populates="unit_id")

And the generated upgrade migration looks like this:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    
    op.create_table('unit',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=12), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('unit_category',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=20), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('unit_category')
    op.drop_table('unit')

I don't think I messed up my definitions, it looks like in the tutorial to me. There is no issue with this on the github, so I don't understand what's going on. Is it related to the fact I'm using an sqlite database?

• python
• sqlite
• sqlalchemy
• alembic

Share Improve this question Follow edited Feb 12 at 7:07 snakecharmerb 55.9k1313 gold badges132132 silver badges186186 bronze badges asked Feb 11 at 21:39 S.ComeauS.Comeau 631010 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Relationships create relationships between entities in the Python layer. You need to declare foreign key columns explicitly to create foreign keys in the database. You might want to review the documentation on relationship configuration.

Assuming that each category can have multiple units, you probably want something like this:

...

class DBUnitCategory(Base):
    __tablename__ = 'unit_category'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(20))

    units: Mapped[List["DBUnit"]] = relationship(back_populates="category")

class DBUnit(Base):
    __tablename__ = 'unit'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(12))
    category_id: Mapped[int] = mapped_column(ForeignKey('unit_category.id'))

    category: Mapped[DBUnitCategory] = relationship(back_populates="units")

本文标签： pythonHow to make alembic autogenerate foreign key relationsStack Overflow