librelist archives

Re: [flask] declarative sqlalchemy

From:

Tom Borthwick

Date:

2011-11-07 @ 21:59

Thanks! I haven't tried blueprints yet but I'll check it out. That's
just what I was looking for.

On Sun, Nov 6, 2011 at 11:27 PM, Sean Chittenden <sean@chittenden.org> wrote:
> * Tom Borthwick <tborthwick@gmail.com> [2011-11-06 20:58:45 -0600]:
>
>>I'm just starting with python and this is more of a beginning
>>python/SqlAlchemy question, but following the declarative instructions
>>on http://flask.pocoo.org/docs/patterns/sqlalchemy/, what's the best
>>way to pass in the database uri rather than hard-code it? Is it really
>>a best practice to have the uri used in the global engine var or is
>>that just for demonstration?
>
> This should answer a few questions that have come up recently regarding
> templates, databases/SQLAlchemy and Blueprints.
>
> This is how I'm currently structuring apps now that we're in a
> post-Blueprints world. Figuring out this structure took a number of
> iterations, but it seems sane-enough now. -sc
>
>
> 1) I put a default SQLALCHEMY_DATABASE_URI string in
>    myapp/default_settings.py:
>
> #+begin_src python
> DATABASE_URI_FMT = 
'postgresql+psycopg2://{username}:{password}@{hostname}:{port}/{dbname}'
> DB_PASS = ''
> DB_PORT = '5432'
> DB_SCHEMA = 'initial_schema'
> DB_ADMIN = 'dba_role'
> DB_USER = 'app_user'
>
> from local_settings import *
> try:
>     from local_settings import *
> except ImportError:
>     pass
>
> SQLALCHEMY_DATABASE_URI = DATABASE_URI_FMT.format(**
>     {   'username': DB_USER,
>         'password': DB_PASS,
>         'hostname': DB_HOST,
>         'port':     DB_PORT,
>         'dbname':   DB_NAME,
>         'schema':   DB_SCHEMA,
>     })
> #+end_src
>
>    Where DB_USER, etc., are overridden from the top-level local_settings.py.
>
> 2) In my app factory (myApp/__init__.py, create_app()), I pull in
>    myapp/default_settings.py via:
>
> #+begin_src python
> __all__ = ['create_app', 'db']
> def create_app(name  __name__):
>     app = Flask(name, static_path='/static')
>     from app.config.from_object('myapp.default_settings')
>     db.init_app(app)
>
>     from myapp.bp.views import bp
>     app.register_blueprint(bp)
>     return app
>
> db = SQLAlchemy()
> #+end_src
>
> 3) Then wherever you need to call the db, you can import the db object and go
>    to town.
>
> #+begin_src python
> from myapp import db
> #+end_src
>
> #+begin_src python
> # bp/views.py
> from flask import (Blueprint,)
> from myapp import db
> from .model import *
>
> bp = Blueprint('bp', __name__, template_folder='templates')
> @bp.route('/')
> def index():
>     return render_template('bp/index.html')
> #+end_src
>
>    And then in my models, for instance:
>
> #+begin_src python
> from sqlalchemy.dialects import postgresql
> from sqlalchemy.sql import expression
> from myapp import db
>
> class FooClass(db.Model):
>     __tablename__ = 'foo'
>     __table_args__ = {'autoload':False, 'schema':'my_table_schema'}
>     id = db.Column(db.Integer, primary_key=True)
>     name = db.Column(postgresql.TEXT)
>
>     @classmethod
>     def foo_factory(self, var):
>         """ Factory method that returns a valid Foo object if this query was
>         successful. This is how I return results from a CTE query, btw. """
>         return db.session.query(FooClass).from_statement('''SELECT id, 
name FROM foo WHERE some_crazy_expression = :var''').params(var_name = 
var)
>
> # NoSQL = Wille E. Coyote. NoSQL = ~50 sec and 2:14 in to each of these
> # episodes, respectively. http://www.youtube.com/watch?v=01cShwNkuMk
> # http://www.youtube.com/watch?v=kvgADFXHajs
> #+end_src
>
>    That last foo_factory() is a *rediculously* handy trick for writing custom
>    SQL and getting valid ORM objects back, but it took forever for me to
>    figure that out. I don't use SQLA to generate my schema and advise against
>    it in favor of always checking in your SQL create scripts.
>
> #+begin_src shell
> echo 'Hello World' >> myap/bp/templates/bp/index.html
> #+end_src
>
> 4) Your file layout looks like:
>
> #+begin_src
> .git
> local_settings.py
> myapp/
> myapp/__init__.py
> myapp/bp/views.py
> myapp/bp/model.py
> myapp/bp/templates/bp/index.html
> myapp/default_settings.py
> #+end_src
>
>
> --
> Sean Chittenden
>