Creating a SQLAlchemy DB instance

SQLAlchemy is a Python SQL toolkit and provides an ORM that gives the flexibility and power of SQL with the feel of Python's object-oriented nature.

Getting ready

Flask-SQLAlchemy is the extension that provides the SQLAlchemy interface for Flask.

This extension can be simply installed using pip as follows:

$ pip install flask-sqlalchemy

The first thing to keep in mind with Flask-SQLAlchemy is the application config parameter that tells SQLAlchemy about the location of the database to be used:

app.config['SQLALCHEMY_DATABASE_URI'] = os.environ('DATABASE_URI')

This SQLALCHEMY_DATABASE_URI is a combination of the database protocol, any authentication needed, and also the name of the database. In the case of SQLite, this would look something like the following:

sqlite:////tmp/test.db

In the case of PostgreSQL, it would look like the following:

postgresql://yourusername:yourpassword@localhost/yournewdb.

This extension then provides a class named Model that helps in defining models for our application. Read more about database URLs at http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html#database-urls.

Note

For all database systems other than SQLite, separate libraries are needed. For example, for using PostgreSQL, you would need psycopg2.

How to do it…

Let's demonstrate this with a small application. We will build over this application in the next few recipes. Here, we will just see how to create a db instance and some basic DB commands. The file's structure would look as follows:

flask_catalog/
    - run.py
    my_app/
      - __init__.py 

First, we start with flask_app/run.py. It is the usual run file that we have read about up to now in this book:

from my_app import app
app.run(debug=True)

Then we configure our application configuration file, that is, flask_app/my_app/__init__.py.

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

Here, we configure our application to point SQLALCHEMY_DATABASE_URI to a specific location. Then, we create an object of SQLAlchemy with the name db. As the name suggests, this is the object that will handle all our ORM-related activities. As mentioned earlier, this object has a class named Model, which provides the base for creating models in Flask. Any class can just subclass or inherit the Model class to create models, which will act as database tables.

Now, if we open the URL http://127.0.0.1:5000 in a browser, we will actually see nothing. This is because there is nothing in the application.

There's more…

Sometimes, you might want a single SQLAlchemy db instance to be used across multiple applications or create an application dynamically. In such cases, we might not prefer to bind our db instance to a single application. Here, we will have to work with application contexts to achieve the desired outcome.

In this case, we will register our application with SQLAlchemy differently, as follows:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    db.init_app(app)
    return app

Tip

The preceding approach can be taken up while initializing the app with any Flask extension and is very common when dealing with real-life applications.

Now, all the operations that were earlier possible globally with the db instance will now require a Flask application context at all times:

Flask application context
>>> from my_app import create_app
>>> app = create_app()
>>> app.test_request_context().push()
>>> # Do whatever needs to be done
>>> app.test_request_context().pop()
Or we can use context manager
with app():
    # We have flask application context now till we are inside the with block

See also

• The next couple of recipes will extend the current application to make a complete application, which will help us understand the ORM layer better