Search code examples
pythonsqlalchemy

How to use separate model files with SQLAlchemy

There are many questions about this and I tried various combinations, but can't get this to work. I want to be able to separate my model files into separate files.

I have the following structure

app.py
db
   Database.py
   models
      __init__.py
      City.py
      Meteo.py

In models.__init__.py, I am including all the models

from sqlalchemy.orm import DeclarativeBase

import City
import Meteo

class ModelBase(DeclarativeBase):
    pass

metadata = ModelBase.metadata

Database.py contains the initialization of the db

from sqlalchemy import URL, create_engine

from db.models import metadata

engine = create_engine("   ")


def create_tables(engine):
    metadata.create_all(engine)


# Print the names of all tables in the database
def print_all_tables(engine):
    metadata.reflect(bind=engine)

    tables = metadata.tables.keys()

    print("List of tables:")
    for table in tables:
        print(f'    {table}')

app.py includes the models and tries to create all the tables

from db.Database import engine, create_tables, print_all_tables
from views.calibration_views import calibration
import db.models


create_tables(engine)

print_all_tables(engine)

if __name__ == '__main__':
    app.run()

And just for completeness, City.py looks like this:

from sqlalchemy import String, Integer
from sqlalchemy.orm import mapped_column, relationship

from db.models import ModelBase


class City(ModelBase):
    __tablename__ = 'city'
    city_id = mapped_column(Integer, primary_key=True)
    city_name = mapped_column(String)
    city_climate = mapped_column(String)
    city_meteo_data = relationship("Meteo", backref="city")

But it dies in Database.py when it includes db.models. Somehow, it can't find the other files in the model directory

enter image description here

Is there something wrong with the way I have \_\_init\_\_.py defined?

Solution

  • Probably the issue is with:

    from sqlalchemy.orm import DeclarativeBase

import City
import Meteo

class ModelBase(DeclarativeBase):
    pass

metadata = ModelBase.metadata

    you need to change imports

    from sqlalchemy.orm import DeclarativeBase

import models.City # or import .City
import models.Meteo # or import .City

class ModelBase(DeclarativeBase):
    pass

metadata = ModelBase.metadata

    But I should also mention that the order of defining models might be circular. You first import the models and then define the base class from which these models inherit. Better to move ModelBase somewhere else, to ,for example, a new file a name base.py in the same directory to avoid the issue.