Table 'XXX' is already defined for this MetaData instance

[mybigman]

First Check

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

Commit to Help

• 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

[raysry]

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}

[Yangeok]

When the import path is incorrect, I encountered the following error:

# occurred error
from foo.bar import Something

# worked out well
from src.foo.bar import Something

[alexjolig]

I'm having the problem even without FastAPI:

main.py

from typing import Optional
import os

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, TIMESTAMP, select

from job import Job


class Parent(SQLModel, table=True):
    id: int = Field(primary_key=True)
    name: str
    deleted_at: str | None = Field(sa_type=TIMESTAMP, default=None)
    parent_id: int | None = Field(
        foreign_key='parent.id',  # notice the lowercase "n" to refer to the database table name
        default=None,
        nullable=True
    )
    parent: Optional["Parent"] = Relationship(
        back_populates='children',
        sa_relationship_kwargs=dict(
            remote_side='Parent.id'  # notice the uppercase "N" to refer to this table class
        )
    )
    children: list["Parent"] = Relationship(back_populates='parent')
    jobs: list["Job"] = Relationship(back_populates="parent")

    @classmethod
    def get_name(cls, parent):
        return parent.name

sqlite_file_name = "database.db"
try:
    os.remove(sqlite_file_name)
except FileNotFoundError:
    pass
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url)

SQLModel.metadata.create_all(engine)

parent1 = Parent(id=1, name="John")
parent2 = Parent(id=2, name="David", parent_id=1)
job1 = Job(name="Police", parent_id=1)
job2 = Job(name="Nurse", parent_id=1)

with Session(engine) as s:
    s.add(parent1)
    s.add(parent2)
    s.add(job1)
    s.add(job2)
    s.commit()
    s.refresh(parent1)
    s.refresh(parent2)
    s.refresh(job1)

    print(f"parent1: {parent1}, Job is: {','.join([job.name for job in parent1.jobs])}")
    print(f"parent2: {parent2}, Job is: {parent2.jobs[0].name if parent2.jobs else None}")
    print(f"grandparent: {parent2.parent.name} is parent of {parent1.children[0].name}")
    print(job1.get_parent_name())

job.py

from sqlmodel import Field, Relationship, SQLModel

class Job(SQLModel, table=True):
    id: int = Field(primary_key=True)
    name: str
    parent_id: str = Field(foreign_key="parent.id")
    parent: "Parent" = Relationship(back_populates="jobs")

    def get_parent_name(self):
        from main import Parent
        p = Parent(name = self.parent.name)
        return p.name

By running python main.py I get the same error

Python version: 3.10
OS: MacOS