SQLAlchemy not creating tables

Question

I am trying to setup a database just like in a tutorial but I am getting a programming error that a table doesn't exist when I'm trying to add a User

This is the file that errors (database.py):

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine(
    "mysql+pymysql://testuser:testpassword@localhost/test?charset=utf8",
    connect_args = {
        "port": 3306
    },
    echo="debug",
    echo_pool=True
)

db_session = scoped_session(
    sessionmaker(
        bind=engine,
        autocommit=False,
        autoflush=False
    )
)

Base = declarative_base()

def init_db():
    import models
    Base.metadata.create_all(bind=engine)

    from models import User
    db_session.add(
        User(username="testuser", password_hash=b"", password_salt=b"", balance=1)
    )
    db_session.commit()

    print("Initialized the db")


if __name__ == "__main__":
    init_db()

To init the database (create the tables) I just run the file. It errors when it creates the test user.

Here is models.py:

from sqlalchemy import Column, Integer, Numeric, Binary, String
from sqlalchemy.orm import relationship

from database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)

    username = Column(String(16), unique=True)
    password_hash = Column(Binary(32))
    password_salt = Column(Binary(32))

    balance = Column(Numeric(precision=65, scale=8))

    def __repr__(self):
        return "<User(balance={})>".format(balance)

I tried:

• Committing before adding users (after create_all)
• Drop existing tables from the database (although it seems like the table never gets committed)
• from models import User instead of import models (before create_all)

Sorry if there are so many simillar questions, I promise I scavenged for answers, but it's always silly mistakes I made sure I didn't make (or atleast the ones I saw).

I am using MariaDB.

Sorry for long post, many thanks in advance.

Answer 1

The Base in database.py isn't the same Base that is imported into models.py.

A simple test is to put a print('creating Base') function call just above the Base = declarative_base() statement, and you'll see it is being created twice.

Python calls the module that is being executed '__main__', which you know as you have the if __name__ == '__main__' conditional at the bottom of your module. So the first Base that is created is __main__.Base. Then, in models.py, from database import Base causes the database module to be parsed again, creating database.Base in the namespace, and that is the Base from which User inherits. Then back in database.py, the Base.metadata.create_all(bind=engine) call is using the metadata from __main__.Base which has no tables in it, and as such creates nothing.

Don't execute out of the module that creates the Base instance. Create another module called main.py (or whatever), and move your init_db() function there and import Base, db_session and engine from database.py into main.py. That way, you are always using the same Base instance. This is example of main.py:

from database import Base, db_session, engine
from models import User


def init_db():

    Base.metadata.create_all(bind=engine)

    db_session.add(
        User(username="testuser", password_hash=b"", password_salt=b"", balance=1)
    )
    db_session.commit()

    print("Initialized the db")


if __name__ == "__main__":
    init_db()

Answer 2

• Declare Base class once(for each database) & import it to all modules which define table classes (inherited from Base)
• For Base (a metaclass) to scan & find out all classes which are inherited from it, we need to import all the modules where such table classes (inherited from Base) are defined to module where we call Metadata.create_all(engine).

Answer 3

You need to import the relevant model where you call "Base.metadata.create_all". Example below to create user table

from ModelBase import Base
from UserModel import User
    
        
def create_db_schema(engine):
        Base.metadata.create_all(engine,checkfirst=True)