Custom engine creation SQLAlchemy in module

Question

I have a project where I want to isolate DB initialization (SQLAlchemy) from the other module so I've create a module

db_initializer.py:

engine = create_engine('sqlite:///db')  # TODO from config file
Session = sessionmaker(bind=engine)
Base = declarative_base(bind=engine)


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

First of all I need to put create_all in a function because my model is in another package.

model/foo.py:

from core.db_initializer import Base

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __init__(self, name: str = None):
        self.name = name

    def __repr__(self):
        return "<User(id=%d, name=%s)>" % (int(self.id), str(self.name))

And my main call create_tables.

Is there any other to do that? And now I need to create the engine with custom config (IP,User, ...) and only the main script know the config it's possible?

Something like

main.py:

import db_initializer as db
import model.foo
db.init(config) # which create the engine and create table

When I do something like that I got problem with the Base object which have not bind to the engine which is not created yet. Any solutions?

Sergey Shubin · Accepted Answer · 2017-02-21T11:12:38.370

You don't need to create engine or session before declaring your models. You can declare models in model/foo.py:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

Let's assume you have some application in myapp.py. Declare it so it can be initialized with engine:

from sqlalchemy.orm import Session
import model.foo

class MyApp:
    def __init__(self, engine):
        self.engine = engine

    def get_users(self):
        session = Session(self.engine)
        users = session.query(model.foo.User).all()
        session.close()

        return users

Create engine in main.py and use it to initialize models.foo.Base.metadata and other applications where you need it:

from sqlalchemy import create_engine
import model.foo
import myapp

engine = create_engine('sqlite:///db')

model.foo.Base.metadata.bind = engine
model.foo.Base.metadata.create_all()

app = myapp.MyApp(engine)

UPD: For scoped_session approach myapp.py can be look like this:

import model.foo

class MyApp:
    def __init__(self, session):
        self.session = session

    def get_users(self):
        return self.session.query(model.foo.User).all()

And main.py:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
import model.foo
import myapp

engine = create_engine('sqlite:///db')
session = scoped_session(sessionmaker(engine))

model.foo.Base.metadata.bind = engine
model.foo.Base.metadata.create_all()

app = myapp.MyApp(session)

How would you handle when you have a lot of model ? If I define Base in the __init__.py and use it in all sub module is that a good practice ? — Timo, Feb 21 '17 at 10:03
@Timo If you have models in several files you can declare `Base` in separate module model/base.py and then import it in model/foo.py, model/bar.py, main.py etc. You don't need to define it during initialization, you only use its `metadata` when creating engine. — Sergey Shubin, Feb 21 '17 at 10:14
Thank you ! But last question how would do if you want use scoped_session and sessionmaker ? — Timo, Feb 21 '17 at 10:53
@Timo In this case you create `scoped_session` object in main.py as well and initialize applications with it. Updated my answer. Also if you are trying to use Flask and standalone SQLAlchemy there is a more [simple way](http://stackoverflow.com/a/41014157/6682517) to do it (just a guess :) — Sergey Shubin, Feb 21 '17 at 11:18

Custom engine creation SQLAlchemy in module

1 Answers1