Namepy step 4 – PostgreSQL, SQLAlchemy and Flask-SQLAlchemy

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)

I’ve used PostgreSQL for a number of different projects. It is the database of choice for Django developers, with MySQL a close second. It is fast, stable, able to hand large databases and open source.

For a small project like this PostgreSQL is overkill, and SQLite would be easier to set up. However, I wanted to show how to integrate the key elements of a serious Flask/etc project, which has to include a production-grade database.

  1. Create a new database
    • For instructions on creating a new database in PostgreSQL you’ll find many helpful posts online, including my own blogpost
    • Make a note of the database name, user name and password
  2. Grab the required libraries (psycopg2 is needed for using flask-sqlalchemy with PostgreSQL, but not automatically installed with it)
    (virtualenv) pip install flask-sqlalchemy
    (virtualenv) pip install psycopg2
    
  3. Import flask-sqlalchemy
    from flask_sqlalchemy import SQLAlchemy
    
  4. Configure it. Replace <user name>, etc, in the text below with the actual user name, etc
    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://<user name>:<password>@localhost/<database>'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
  5. Define the models in <project_root>/models.py
    from hello import db
    
    class Name(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String())
        frequencies = db.relationship('NameFrequency', backref='name', lazy='dynamic')
    
    class NameFrequency(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name_id = db.Column(db.Integer, db.ForeignKey('name.id'))
        year = db.Column(db.Integer)
        boys_count = db.Column(db.Integer)
        girls_count = db.Column(db.Integer)
    
  6. Test the database and create the tables
    python
    >>> from hello import db
    >>> db.create_all()
    

    As long as you see no error message this has worked

  7. Create a script for adding some test data. In hello.py add:
    def create_test_data():
        for name in ('Fred', 'Sue'):
            new_name = Name(name=name)
            db.session.add(new_name)
            for year in range(1990, 1996):
                new_frequency = NameFrequency(
                    name=new_name,
                    year=year,
                    boys_count=random.randint(50, 100),
                    girls_count=random.randint(50, 100))
        db.session.commit()
    
  8. And run the script
    python
    >>> from hello import create_test_data
    >>> create_test_data()
    
    
  9. Test this:
    >>> from models import Name
    >>> names = Name.query.all()
    >>> names[0].name
    u'Fred'
    >>>
    
    >>> names[0].frequencies[0].boys_count
    57
    >>>
    

    Or some other number between 50 and 100

Whilst this works so far, there are a couple of problems. The database details, including password, are in the main source code. And when you try running it as a Flask script (python hello.py) you get a circular import error. Let’s tidy this up

  1. Create a new file, <project_root>\config.py, containing the configuration (with the correct login and database details):
    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://&lt;username&gt;:&lt;password&gt;@localhost/&lt;database name&gt;'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
  2. If you’re using git, make sure this isn’t under version control. Create/update .gitignore to include the following:
    config.py
    
  3. Create a new file, create_test_data.py, containing create_test_data from hello.py, plus necessary imports, and the code to run the create_test_data function when called from the command line
  4. Create index.py:
    import os
    import sys
    
    CURRENT_FILE = os.path.abspath(__file__)
    CURRENT_DIR = os.path.dirname(CURRENT_FILE)
    PROJECT_DIR = os.path.dirname(CURRENT_DIR)
    
    sys.path.append(PROJECT_DIR + '/src/')
    sys.path.append(PROJECT_DIR + '/virtualenv/lib/python3.5/site-packages/')
    
    from app import app as application
    
    if __name__ == '__main__':
        application.run(debug=True)
    
  5. Create an “app” folder in the project_root folder
  6. In <project_root>/app create __init__.py:
    from flask import Flask
    from flask.ext.sqlalchemy import SQLAlchemy
    
    app = Flask(__name__)
    app.config.from_object('config')
    db = SQLAlchemy(app)
    
    from app import views, models
    
  7. Move models.py into the app folder and change the first line to:
    from app import db
    
  8. In the app folder, create views.py, containing @app.route(“/”) and hello() function from hello.py, plus necessary imports
  9. Test this. You should get

    Welcome, today is June 7th 2016

Let’s show some data

  1. In views.py, add:
    from models import Name
    
  2. In render_template, pass in the Name objects:
    return render_template('helloworld.html', names=Name.query.all())
    
  3. In the helloworld.html template, get the series data from the Name objects:
                        series: [
                            {{d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb} for name in names {d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb}}
                                {
                                    name: '{{ name.name }}',
                                    data: [
                                        {{d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb} for frequency in name.frequencies {d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb}}
                                            {{ frequency.boys_count }},
                                        {{d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb} endfor {d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb}}
                                    ]
                                },
                            {{d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb} endfor {d34bf16ac7b745ad0d2811187511ec8954163ba9b5dbe9639d7e21cc4b3adbdb}}
                        ]
    
  4. The chart title no longer makes sense. Change this to “Name frequencies”
  5. Test this. You should see another chart

Done

Next

Continue to Step 5 – Flask-Restless