19

I am attempting to create a revision with --autogenerate using Alembic for two Models, but am receiving a duplicate table keys error. Does, a schema need to be specified? If so, how can it be set? The documentation I've read says to use __table_args__ = {'schema': 'somename'}, but that hasn't helped. Any tips or suggestions are greatly appreciated.

My current setup is:

base.py

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

workspace.py

from sqlalchemy import Column, Integer, String
from base import Base

class WorkspaceModel(Base):

    __tablename__ = 'workspaces'

    id = Column(Integer, primary_key=True)
    name = Column(String)

host.py

from sqlalchemy import Column, Integer, String
from base import Base

class HostModel(Base):

    __tablename__ = 'hosts'

    id = Column(Integer, primary_key=true)
    ip = Column(String)

alembic/env.py

from host import HostModel
from workspace import WorkspaceModel
target_metadata = [HostModel.metadata, WorkspaceModel.metadata]

Error

ValueError: Duplicate table keys across multiple MetaData objects: "hosts", "workspaces"
chirinosky
  • 4,438
  • 1
  • 28
  • 39
  • 4
    `target_metadata` should just be `target_metadata = Base.metadata`. – univerio Jan 02 '18 at 00:00
  • 5
    Thanks @univerio. When I tried doing that, the model data wasn't read and gave me a blank migration; upgrade() and downgrade() didn't contain anything. – chirinosky Jan 02 '18 at 00:04
  • Are you sure that doesn't just mean it thinks your schema has no changes? – univerio Jan 02 '18 at 01:25
  • 3
    I am sure. The alembic/versions directory contents were deleted, and the database was dropped and recreated. Switching to ```target_metadata = Base.metadata``` generated blank migrations. – chirinosky Jan 02 '18 at 14:48
  • 14
    Then are you sure you've imported everything correctly? Using `Base.metadata` doesn't mean you can remove the imports `from host import HostModel` and `from workspace import WorkspaceModel`. – univerio Jan 02 '18 at 19:09
  • 1
    Oh wow, wasn't importing the models. That solved the issue. I'd be happy to accept if you post as an answer. Certainly appreciate the help. – chirinosky Jan 03 '18 at 01:44
  • Unfortunately, importing models in alembic 1.5.4 didn't help: upgrade(), downgrade() were left unpopulated – Ladenkov Vladislav Feb 17 '21 at 10:28
  • If you are using flask-sqlalchemy the equivalent of `Base.metadata` is `db.Model.metadata`. – snakecharmerb Sep 27 '21 at 10:47

3 Answers3

36

To make it clear from what @esdotzed and @univerio said, you have to use a single Base.metadata - but still import the individual models.

In the original question, this is how the alembic/env.py should look like:

from base import Base

# This two won't be referenced, but *have* to be imported to populate `Base.metadata`
from host import HostModel
from workspace import WorkspaceModel

target_metadata = Base.metadata

If you didn't import both models, the autogenerated migration would end up deleting your whole database - because Base.metadata doesn't know about any model by itself.

mgarciaisaia
  • 14,521
  • 8
  • 57
  • 81
  • Unfortunately, importing models in alembic 1.5.4 didn't help: upgrade(), downgrade() were left unpopulated – Ladenkov Vladislav Feb 17 '21 at 10:21
  • I hope this answer gets accepted because it's the correct one – pcko1 Oct 17 '21 at 10:59
  • Another thing you should note: If you're trying to create the fist migration, the database you're connecting with **must be empty**. You may connect to a temp db to generate the migration and then switch back. – Ramon Dias Sep 20 '22 at 17:31
  • 1
    If you have configured precommit hooks in git, it will remove these unused imports any work around for this? – Krishnadas PC Oct 12 '22 at 08:49
  • Yes this works, i come to here after the error ValueError: Duplicate table keys across multiple MetaData objects. This answer solve the autogenerate detect blank tables problem for multiple model scenario. – Tan Hoo Chuan Nickson Mar 08 '23 at 10:26
2

quoting univerio's answer from the comment section:

target_metadata should just be target_metadata = Base.metadata

Using Base.metadata doesn't mean you can remove the imports from host import HostModel and from workspace import WorkspaceModel

It worked for me.

Ente
  • 2,301
  • 1
  • 16
  • 34
esdotzed
  • 498
  • 1
  • 7
  • 22
  • 1
    @snakecharmerb this user just moved an answer provided in the comment section into an actual answer. Which i think is a valid thing to do. – Ente Sep 27 '21 at 10:00
  • @Ente ah, the "answer" fooled me. Good edit. I can't retract the NAA flag, but I acknowledge that this answer should be left open. – snakecharmerb Sep 27 '21 at 10:06
0

I just want to add to @mgarciaisaia answer, it will work but the thing is when I tried changing, for example, the max length of username field of User model and running alembic revision --autogenerate -m "test migration", alembic output a migration file with empty upgrade() and downgrade() functions!

Note: the following operations will erase your data from the database so please back them up beforehand!

In order to update the changes made to the original User model, I had to

  1. Delete the first migration file
  2. Rerun alembic revision --autogenerate -m "update user model" and alembic upgrade head again for the changes to appear inside upgrade() and downgrade() functions of the migration file.
William Le
  • 825
  • 1
  • 9
  • 16