Table 'XXX' is already defined for this MetaData instance

mybigman commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 1 year ago

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

l00p1n6 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

meirdev thanks issue resolved

wilson-romero commented 1 year ago

Thanks

mielvds commented 1 year ago

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 commented 9 months ago

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 commented 7 months ago

this work for me. try adding this. doc

from sqlmodel import SQLModel

SQLModel.__table_args__ = {'extend_existing': True}

Yangeok commented 5 months ago

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 commented 5 months ago

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

ben05allen commented 5 months ago

Works with this edit to the get_parent_name method:

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):
        p = self.parent
        return p.name

ReRubis commented 5 months ago

If you encounter such error, it might be because the file with models gets executed twice.

Add a debug print on top of the file to check it.

print("----")

As mentioned above the cause of such behavior might be uvicorn.

In my case it was relative imports. Will suggest to use only absolute ones.

h4gen commented 4 months ago

this work for me. try adding this. doc
from sqlmodel import SQLModel

SQLModel.__table_args__ = {'extend_existing': True}

Thanks, that worked for me!

fastapi / sqlmodel