In flask, many people prefer to operate the database through SQLAlchemy. In this case, it is recommended to use package instead of module to split the data model into a separate module. Such an approach is not necessary, but more reasonable.

Flask-SQLAlchemy Extension

Because SQLAlchemy is a general database abstraction layer and ORM, it requires some additional configuration, and there is an extension in Flask to handle this.

Declarative

declarative extension in SQLAchemy is a recent approach. This approach allows you to define forms and data models at once, similar to how Django works.

Here is an example of database.py module:

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

engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    # import all modules here that might define models so that
    # they will be registered properly on the metadata.  Otherwise
    # you will have to import them first before calling init_db()
    import yourapplication.models
    Base.metadata.create_all(bind=engine)

We only need to inherit the Base in the above code to define our own data model. There is no need to worry about thread safety in this place, because SQLAlchemy has helped us deal with it through scoped ﹣ session.

We just need to put the following code into our application module to use SQLAlchemy in a declarative way. Flask will automatically close the session when the request ends or the application exits:

from yourapplication.database import db_session

@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()

The following code is an example of a data model (can be put into models.py, e.g.):

from sqlalchemy import Column, Integer, String
from yourapplication.database import Base

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)
    email = Column(String(120), unique=True)

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

    def __repr__(self):
        return '<User %r>' % (self.name)

When creating a database, you can use init? Db():

>>> from yourapplication.database import init_db
>>> init_db()

Insert records into the database:

>>> from yourapplication.database import db_session
>>> from yourapplication.models import User
>>> u = User('admin', 'admin@localhost')
>>> db_session.add(u)
>>> db_session.commit()

The query is also simple:

>>> User.query.all()
[<User u'admin'>]
>>> User.query.filter(User.name == 'admin').first()
<User u'admin'>