I have the following issue in a project that uses SQLAlchemy. I've put all the code after the question.
If I use from base import Base in create_tables.py (as seen in the code below), the tables are not created.
If I use from persistence.base import Base in create_tables.py, the tables ARE created.
Why does this happen? Why does using the absolute import path fix everything?
I suspect that when I use from base import Base a new, clean Base class instance is used, so SQLAlchemy does not know of the child classes that inherit from Base and make up the tables. However, I don't understand why using the absolute import path (persistence.Base) actually solves the issue.
Another point to make is that I use absolute import paths in the *_api.py files as you can see below. How would using relative path imports and absolute path imports affect the outcome of this situation?
Any ideas? Thanks.
Project's structure:
src
--persistence
----create_tables.py
----base.py
----user
------user_api.py
----expenditure
------expenditure.py
----statement
------statement.py
Relevant code:
base.py
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
expenditure_api.py
from sqlalchemy import Column, Float, ForeignKey, String, TIMESTAMP
from sqlalchemy.dialects.mysql import INTEGER
from sqlalchemy.orm import relationship
from persistence.base import Base
class Expenditure(Base):
__tablename__ = 'expenditure'
expenditure_id = Column(INTEGER, primary_key=True, unique=True)
expenditure_user_id = Column(ForeignKey('user.user_id'), nullable=False, index=True)
expenditure_issue_date = Column(TIMESTAMP)
expenditure_payment_date = Column(TIMESTAMP)
expenditure_receipt_id = Column(String(45))
expenditure_statement_id = Column(ForeignKey('statement.statement_id'), index=True)
expenditure_transaction_details = Column(String(255))
expenditure_payment_num = Column(INTEGER)
expenditure_total_payments = Column(INTEGER)
expenditure_amount = Column(Float(asdecimal=True), nullable=False)
expenditure_currency = Column(String(45), nullable=False)
expenditure_statement = relationship('Statement')
expenditure_user = relationship('User')
statement_api.py
# coding=utf-8
from sqlalchemy import Column, Float, ForeignKey, String, TIMESTAMP
from sqlalchemy.dialects.mysql import INTEGER
from persistence.base import Base
class Statement(Base):
__tablename__ = 'statement'
statement_id = Column(String(255), primary_key=True, unique=True)
statement_from_date = Column(TIMESTAMP)
statement_to_date = Column(TIMESTAMP)
user_api.py
from sqlalchemy import Column, Float, ForeignKey, String, TIMESTAMP
from sqlalchemy.dialects.mysql import INTEGER
from persistence.base import Base
class User(Base):
__tablename__ = 'user'
user_id = Column(INTEGER, primary_key=True, unique=True)
user_username = Column(String(45), nullable=False)
user_full_name = Column(String(255))
create_tables.py
from sqlalchemy import exc
from sqlalchemy.orm import sessionmaker
from persistence_config import sql_server_socket, database_name
from user.user_api import User
from expenditure.expenditure_api import Expenditure
from statement.statement_api import Statement
from base import Base
from create_logger import create_logger
logger = create_logger(__name__)
def create_tables(engine):
# try:
Base.metadata.create_all(engine)
logger.info(f"Tables created.")
# except exc.DatabaseError as ex:
# logger.error(f"create_tables error: {ex}")
# except:
# logger.exception("create_tables error.")
if __name__ == "__main__":
from persistence_config import sql_server_socket, database_name
from sqlalchemy import create_engine
engine = create_engine(sql_server_socket + "/" + database_name, echo=True)
create_tables(engine)
