How to build a flask application around an already existing database?

I'd say your question has nothing to do with flask at all. For example, you don't have a problem with the templates, routes, views or logon decorators.

Where you struggle at is at SQLAlchemy.

So my suggestion is to ignore Flask for a while and get used to SQLAlchemy first. You need to get used to your existing database and how to access it from SQLAlchemy. Use some MySQL documentation tool to find your way around this. The start with something like this (note that it has nothing to do with Flask ask all ... yet):

#!/usr/bin/python# -*- mode: python -*-from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine('sqlite:///webmgmt.db', convert_unicode=True, echo=False)Base = declarative_base()Base.metadata.reflect(engine)from sqlalchemy.orm import relationship, backrefclass Users(Base):    __table__ = Base.metadata.tables['users']if __name__ == '__main__':    from sqlalchemy.orm import scoped_session, sessionmaker, Query    db_session = scoped_session(sessionmaker(bind=engine))    for item in db_session.query(Users.id, Users.name):        print item

In the line "engine =" you need to provide your path to your MySQL database, so that SQLAlchemy finds it. In my case I used a pre-existing sqlite3 database.

In the line "class Users(Base)" you need to use one of existing tables in your MySQL database. I knew that my sqlite3 database had a table named "users".

After this point, SQLalchemy knows how to connect to your MySQL database and it knows about one of the tables. You need now to add all the other tables that you care for. Finally, you need to specify relationships to SQLalchemy. Here I mean things like one-to-one, one-to-many, many-to-many, parent-child and so on. The SQLAlchemy web site contains a rather lenghty section about this.

After the line "if __name__ == '__main__'" just comes some test code. It will be executed if I don't import my python script, but run. Here you see that I create a DB session and is that for a very simply query.

My suggestion is that you first read about the important parts of SQLAlchemy's documentation, for example the descriptive table definition, the relationship model and how to query. Once you know this, you can change the last part of my example into a controller (e.g. using Python's yield method) and write a view that uses that controller.

The key to connecting Holger's answer to a flask context is that db.Model is a declarative_base object like Base. Took me a while to notice this important sentence in flask-sqlalchemy's documentation

Below are the steps I used for my app:

1. initiate a db object in the usual flask-alchemy manner:db = SQLAlchemy(app). Note you'll need to set app.config['SQLALCHEMY_DATABASE_URI'] = 'connection_string' before that.

2. bind the declarative base to an engine: db.Model.metadata.reflect(db.engine)

3. Then you can use existing tables easily (eg. I have a table called BUILDINGS):

   class Buildings(db.Model):    __table__ = db.Model.metadata.tables['BUILDING']    def __repr__(self):        return self.DISTRICT

Now your Buildings class will follow the existing schema. You can try dir(Buildings) in a Python shell and see all the columns already listed.

I recently went through the same thing, with the additional challenge of linking the models across two databases.

I used Flask-SQLAlchemy and all I had to do was define my models in the same way as my database tables looked. What I found difficult was figuring out exactly what my project structure should look like.

My project was a Restful API, and this is what I ended up with:

conf/    __init__.py    local.py    dev.py    stage.py    live.pydeploy/    #nginx, uwsgi config, etcmiddleware/    authentication.pyapp_name/    blueprints/        __init__.py        model_name.py #routes for model_name        ...    models/        __init.py        model_name.py    __init__.py    database.pytests/    unit/        test_etc.py        ...run.py

Files of note:

conf/xxx.py

This is how we tell Flask-SQLAlchemy what to connect to, plus you can put any other config items in here (like log location, debugging config, etc).

SQLALCHEMY_DATABASE_URI = 'mysql://username:password@host:port/db_name'

app_name/__init__.py

This is where I create my app and initialise the db. This db object will be imported and used across the entire app (i.e., in the models, tests, etc). I also set my logger, initialise my APIs and blueprints and attach my middleware in here (not shown).

from app_name.database import dbfrom flask import Flaskdef create_app(*args, **kwargs):    env = kwargs['env']    app = Flask(__name__)    app.config.from_object('conf.%s' % env)    db.init_app(app)    return app

app_name/database.py

from flask.ext.sqlalchemy import SQLAlchemydb = SQLAlchemy()

app_name/models/model_name.py

from services.database import dbclass Bar(db.Model):    __tablename__ = 'your_MySQL_table_name'    id = db.Column('YourMySQLColumnName', db.Integer, primary_key=True)    name = db.Column('WhateverName', db.String(100))    foo = db.Column(db.ForeignKey('another_MySQLTableName.id'))class Foo(db.Model):    __tablename__ = 'another_MySQLTableName'    id = db.Column('FooId', db.Integer, primary_key=True)    ...

run.py

#! /usr/bin/env pythonfrom app_name import create_appapp = create_app(env='local')if __name__ == '__main__':    app.run()

I use run.py to run the app locally, but I use nginx + uWSGI to run the app in the dev/stage/live environments.

I'm guessing you'll have a views/ directory in there in addition to this though.