Table 'XXX' is already defined for this MetaData instance

[mybigman]

First Check

• [X] I added a very descriptive title to this issue.
• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the SQLModel documentation, with the integrated search.
• [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [X] I already read and followed all the tutorial in the docs and didn't find an answer.
• [X] I already checked if it is not related to SQLModel but to Pydantic.
• [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

# using code from - https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/?h=hero#the-herocreate-data-model

from typing import List, Optional

from fastapi import FastAPI
from sqlmodel import Field, Session, SQLModel, create_engine, select


class HeroBase(SQLModel):
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)


class HeroCreate(HeroBase):
    pass


class HeroRead(HeroBase):
    id: int


engine = create_engine(
    "postgresql+psycopg2://postgres:postgres@localhost/testing", echo=True
)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


app = FastAPI()


@app.on_event("startup")
def on_startup():
    create_db_and_tables()


@app.post("/heroes/", response_model=HeroRead)
def create_hero(hero: HeroCreate):
    with Session(engine) as session:
        db_hero = Hero.from_orm(hero)
        session.add(db_hero)
        session.commit()
        session.refresh(db_hero)
        return db_hero


@app.get("/heroes/", response_model=List[HeroRead])
def read_heroes():
    with Session(engine) as session:
        heroes = session.exec(select(Hero)).all()
        return heroes

Description

First attempt fails with.

sqlalchemy.exc.InvalidRequestError: Table 'hero' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

Adding this fixes this problem, however reading various sites this is not recommended?

class Hero(HeroBase, table=True):
    __table_args__ = {"extend_existing": True} # < new
    id: Optional[int] = Field(default=None, primary_key=True)

Second attempt fails with.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "ix_hero_name" already exists

[SQL: CREATE INDEX ix_hero_name ON hero (name)]

Moving the create_db_and_tables() out of the functions startup event fixes this problem and everything works as expected

create_db_and_tables()
@app.on_event("startup")
def on_startup():
    print("startup")
    # create_db_and_tables()

Am I missing something that is causing this behavior?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.10.4

Additional Context

No response

[ArVar]

Similar problem when trying to set a timing decorator. Must be something general in the context of wrapper functions or decorators respectively. This is apperently not limited to SQLModel, since I'm not using SQLModel but declarative_base from SQLAlchemy.

[daniil-berg]

I cannot reproduce this error. Works fine for me.

What version of SQLAlchemy is installed in the environment where you experienced this error?

Or has this issue been resolved?

[AlekseyFedorovich]

I had the same problem. Surprisingly it was caused by missing the package 'psycopg2' (I'm using a Postgresql db).

[l00p1n6]

I have the same problem with even less code:

from typing import Optional

import uvicorn as uvicorn
from fastapi import FastAPI
from sqlmodel import Field, SQLModel

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

app = FastAPI()

if __name__ == "__main__":
    uvicorn.run("main:app", host="0.0.0.0", port=8001)

The same ERROR pops when I run the program with python main.py

But everything looks OK if I run it with uvicorn main:app

Here is pipenv graph, if it helps:

aiosqlite==0.17.0
  - typing-extensions [required: >=3.7.2, installed: 4.4.0]
fastapi==0.87.0
  - pydantic [required: >=1.6.2,<2.0.0,!=1.8.1,!=1.8,!=1.7.3,!=1.7.2,!=1.7.1,!=1.7, installed: 1.10.2]
    - typing-extensions [required: >=4.1.0, installed: 4.4.0]
  - starlette [required: ==0.21.0, installed: 0.21.0]
    - anyio [required: >=3.4.0,<5, installed: 3.6.2]
      - idna [required: >=2.8, installed: 3.4]
      - sniffio [required: >=1.1, installed: 1.3.0]
sqlmodel==0.0.8
  - pydantic [required: >=1.8.2,<2.0.0, installed: 1.10.2]
    - typing-extensions [required: >=4.1.0, installed: 4.4.0]
  - SQLAlchemy [required: >=1.4.17,<=1.4.41, installed: 1.4.41]
    - greenlet [required: !=0.4.17, installed: 2.0.1]
  - sqlalchemy2-stubs [required: Any, installed: 0.0.2a29]
    - typing-extensions [required: >=3.7.4, installed: 4.4.0]
uvicorn==0.20.0
  - click [required: >=7.0, installed: 8.1.3]
    - colorama [required: Any, installed: 0.4.6]
  - h11 [required: >=0.8, installed: 0.14.0]

[meirdev]

The problem is because uvicorn is loading the file again, you can see this by adding print:

from sqlmodel import Field, SQLModel

print("here")

class Hero(SQLModel, table=True):

You can solve this by calling app directly:

app = FastAPI()

if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8001)

Or, by separating the code into two files:

main.py:

import uvicorn

if __name__ == "__main__":
    uvicorn.run("app:app", host="0.0.0.0", port=8001)

app.py:

from typing import Optional

import uvicorn as uvicorn
from fastapi import FastAPI
from sqlmodel import Field, SQLModel

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

app = FastAPI()

[talhaanwarch]

meirdev thanks issue resolved

[wilson-romero]

Thanks

[mielvds]

I was also getting this error when I imported the *.py file with model definitions in my unittests, which is located under models/crud/. I was not even importing the class that has table=True. Running the app with uvicorn actually worked fine.

In the end I figured out that I was importing from app.models.crud instead of models.crud and that fixed the issue

[raymondsryang]

Similar problem with streamlit when file changed and streamlit app hotreload, error "Table 'XXX' is already defined for this MetaData instance" raised anyone have idea?

[thaithamtawan]

this work for me. try adding this. doc

from sqlmodel import SQLModel

SQLModel.__table_args__ = {'extend_existing': True}