CODEWITHSUNDEEP
pythonflasksqlalchemy
JayHelton
JayHelton

Reputation: 91

Proper sqlalchemy use in flask

I am making a basic API for funsies with flask and sqlalchemy. I read in the sqlalchemy documentation that you should only ever have one engine with the connection string. How is that supposed to be structured within an app> An __init__.py file? In the flask file that runs to start the flask server? Here is my github repo, since i think its easier just to see the current app structure and any flaws it may have. https://github.com/JayHelton/Geekdom_Web_App

In my query methods, i am creating a new DB engine everytime and it seems to be working, but i dont want to run into issues if multiple people try to call these methods. Thank you!

Upvotes: 7

Views: 9338

Answers (2)

Messa
Messa

Reputation: 25191

Of course there is already a Flask extension Flask-SQLAlchemy - also mentioned in the Flask docs SQLAlchemy in Flask. But like most Flask extensions, all it does is "just" some "plumbing" Flask and SQLAlchemy (or any other library) together. The best documentation is often times the source code :)

github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py

However, in case of Flask-SQLAlchemy it is a lot of code and also some black magic about scoped sessions intertwined with Flask contexts and modification tracking and duration debugging and some things useful in web apps and all possible corner cases and some other things. I'd say it's a bit overengineered. I'm not saying that you shouldn't use it - just that the connection between SQLAlchemy and Flask is not visible at first look from the extension code so it may take more reading time.

But if you wish to do it yourself it is very easy (well, as easy as SQLAlchemy can get) - just initialize SQLAlchemy so you get a sessionmaker, and then create a session before each request, then don't forget to close it after the request :) and just use this session in your Flask handlers (I mean the @app.route functions).

import flask
import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Item (Base):

    __tablename__ = 'items'

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

engine = sqlalchemy.create_engine('sqlite:///bestappever.db', echo=True)
Session = sessionmaker(bind=engine)

# create all tables if you wish...
Base.metadata.create_all(engine)

app = flask.Flask(__name__)

@app.before_request
def create_session():
    flask.g.session = Session()

@app.teardown_appcontext
def shutdown_session(response_or_exc):
    flask.g.session.commit()
    flask.g.session.remove()

@app.route('/')
    # just use the SQLAlchemy session :)
    items = flask.g.session.query(Item).all()
    return flask.render_template('index.html', items=items)

See my example Flask + SQLAlchemy app: https://github.com/messa/db-workshop-web-app/blob/sqlalchemy/anketa.py

As you see you can even have everything on one big file but splitting it into multiple files (one of which is usually named like model.py) is the best practice.

The most important thing is to have isolated sessions between individual requests - in my solution the session is created for each request in the before_request hook. The solution from Flask docs "SQLAlchemy in Flask" uses scoped_session which has basically the same effect (achieved using thread-local variable with one separate session per each thread).

Ad application architecture: for bigger applications it is good to use Larger Applications patterns and blueprints. So all my flask route handlers will be in blueprints, and then I would have one "main" function get_app() that 1) creates Flask object 2) registers the blueprints with it 3) creates SQLAlchemy engine and hooks Session() with Flask app.before_request. Roughly similar to this or this.

you should only ever have one engine

Why? Technically, engine is just a connection (pool) to the database. What if your app uses three separate databases? Then you need three engines, of course.

But of course a session is connected to exactly one engine. So you would need multiple sessions. And multiple declarative bases for your model classes, and - most importantly - not mix it up accidentally. That's why it's recommended to have just one engine - if possible.

You can create engine per request - it is technically correct, but it is inefficient. Keep one engine for whole application and just create session per request.

Upvotes: 18

susan mashevich
susan mashevich

Reputation: 7

Once you have SQLAlchemy installed on your Flask app you will need to create a separate *.py file to store the database configuration and use import statements to incorporate it into your other .py files to use.

If you want to see/create tables for your API data within the app itself, they would reside in something like a 'models.py' file which also imports the db connection string.

Here is a pretty good Flask + SQLAlchemy todo app tutorial, link is to the section where she describes the file structure and db implementation in detail.

http://www.vertabelo.com/blog/technical-articles/web-app-development-with-flask-sqlalchemy-bootstrap-part-2

Below is a link with a bit more generic however also detailed walk-thru regarding setting up a SQLAlchemy db with Flask, and also goes over a few examples of using ORM queries rather than raw SQL queries (although I generally use raw SQL queries - given that I usually also install MySQL & create tables in MySQL workbench, however this may be overkill for an app just using API data):

http://flask.pocoo.org/docs/0.12/patterns/sqlalchemy/

Upvotes: 0

Related Questions