SQLAlchemy create_all() does not create tables in to mysql when i import models class

Question

I'm trying to integrate Mysql and SQLAlchemy but SQLAlchemy.create_all() is not creating any tables from my models. this is my code

//Patient class
#!/usr/bin/env python
import sqlalchemy
from os import getenv
from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, String, DateTime
import uuid
from typing import TypeVar, List, Iterable
from os import path
from datetime import datetime
from models.base_person import BasePerson

Base = declarative_base()


class Patient(BasePerson, Base):

    __tablename__ = 'patients'
    appointement_payement = relationship("AppointementPayement", backref="Patient", cascade="delete")

// AppointementPayement file (appointement_payement.py)

#!/usr/bin/env python
import sqlalchemy
from os import getenv
from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, String, DateTime, Numeric,  ForeignKey
import uuid
from typing import TypeVar, List, Iterable
from os import path
from datetime import datetime
from models.base_person import BasePerson
#from models.engine.db_storage import MDBStorage

#from models.base import Base
Base = declarative_base()
from models.base import BaseModel

class AppointementPayement(BaseModel, Base):

    __tablename__ = 'appointement_payement'
    payement_amount = Column(Numeric(10, 2), nullable=False)
    payement_method = Column(String(128), nullable=False)
    patient_id = Column(String(60), ForeignKey('patients.id'), nullable=False)
    

// base

#!/usr/bin/env python
import sqlalchemy
from os import getenv
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, String, DateTime, Boolean
import uuid
from typing import TypeVar, List, Iterable
from os import path
from datetime import datetime
TIMESTAMP_FORMAT = "%Y-%m-%dT%H:%M:%S"

Base = declarative_base()


class BaseModel:
    id = Column(String(60), primary_key=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow)
    is_deleted = Column(Boolean, default=False)

// the class that is supposed to create the classes (db_storage.py)

from models.base_person import BasePerson
from models.base import Base
from os import getenv
from sqlalchemy.orm import declarative_base, sessionmaker, scoped_session, relationship
from sqlalchemy import Column, String, DateTime, create_engine, Numeric, ForeignKey, Text
import uuid
from typing import TypeVar, List, Iterable
from os import path
from datetime import datetime
from models.patient import Patient
from models.appointement_payement import AppointementPayement
Base = declarative_base()
class MDBStorage:
    """interaacts with the MySQL database"""
    __engine = None
    __session = None

    def __init__(self):
        """Instantiate a DBStorage object"""
         PRIMETEL_MYSQL_USER = getenv('PRIMETEL_MYSQL_USER')
         PRIMETEL_MYSQL_PWD = getenv('PRIMETEL_MYSQL_PWD')
         PRIMETEL_MYSQL_HOST = getenv('PRIMETEL_MYSQL_HOST')
         PRIMETEL_MYSQL_DB = getenv('PRIMETEL_MYSQL_DB')
         self.__engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.
                                            format('PRIMETEL_MYSQL_USER',
                                                   'PRIMETEL_MYSQL_PWD',
                                                   'PRIMETEL_MYSQL_HOST',
                                                   'PRIMETEL_MYSQL_DB'), echo=True)

    def reload(self):
        """reloads data from the database"""
        Base.metadata.create_all(self.__engine)
        sess_factory = sessionmaker(bind=self.__engine, expire_on_commit=False)
        Session = scoped_session(sess_factory)
        self.__session = Session

    def close(self):
        """call remove() method on the private session attribute"""
        self.__session.remove()

// init.py, instantiation of the class MDBStorage

from models.engine.db_storage import MDBStorage


storage = MDBStorage()
storage.reload()

But when i put all models class in db_storage.py and also creat an object of MDBStorage and invoqued reload(), all table are created. i use SQLAlchemy 2.0.9

Answer 1

It seems like the issue is related to how the declarative_base is being used in your code. You have defined Base multiple times in different files, which is causing SQLAlchemy to lose track of the table definitions.

To fix this issue create a single base.py file where you define Base and import it in all other model files:

base.py

from sqlalchemy.orm import declarative_base

Base = declarative_base()

Then, in your model files, import Base from base.py

patient.py

from sqlalchemy.orm import relationship
from sqlalchemy import Column, String, DateTime
from models.base_person import BasePerson
from models.base import Base

class Patient(BasePerson, Base):
    # rest of the code

appointement_payement.py

from sqlalchemy import Column, String, DateTime, Numeric, ForeignKey
from models.base_person import BasePerson
from models.base import Base, BaseModel

class AppointementPayement(BaseModel, Base):
    # rest of the code

db_storage.py

from sqlalchemy import Column, String, DateTime, create_engine, Numeric, ForeignKey, Text
from sqlalchemy.orm import sessionmaker, scoped_session, relationship
from models.patient import Patient
from models.appointement_payement import AppointementPayement
from models.base import Base

class MDBStorage:
    # rest of the code

You need to update the format string in the __init__ method of MDBStorage class to properly use the environment variables.

self.__engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.
                              format(PRIMETEL_MYSQL_USER,
                                     PRIMETEL_MYSQL_PWD,
                                     PRIMETEL_MYSQL_HOST,
                                     PRIMETEL_MYSQL_DB), echo=True)

SQLAlchemy should be able to create the tables as expected.