Category Archives: Highcharts

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>'
  5. Define the models in <project_root>/
    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(''))
        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
    >>> 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 add:
    def create_test_data():
        for name in ('Fred', 'Sue'):
            new_name = Name(name=name)
            for year in range(1990, 1996):
                new_frequency = NameFrequency(
                    boys_count=random.randint(50, 100),
                    girls_count=random.randint(50, 100))
  8. And run the script
    >>> from hello import create_test_data
    >>> create_test_data()
  9. Test this:
    >>> from models import Name
    >>> names = Name.query.all()
    >>> names[0].name
    >>> names[0].frequencies[0].boys_count

    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 you get a circular import error. Let’s tidy this up

  1. Create a new file, <project_root>\, 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;'
  2. If you’re using git, make sure this isn’t under version control. Create/update .gitignore to include the following:
  3. Create a new file,, containing create_test_data from, plus necessary imports, and the code to run the create_test_data function when called from the command line
  4. Create
    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__':
  5. Create an “app” folder in the project_root folder
  6. In <project_root>/app create
    from flask import Flask
    from flask.ext.sqlalchemy import SQLAlchemy
    app = Flask(__name__)
    db = SQLAlchemy(app)
    from app import views, models
  7. Move into the app folder and change the first line to:
    from app import db
  8. In the app folder, create, containing @app.route(“/”) and hello() function from, plus necessary imports
  9. Test this. You should get

    Welcome, today is June 7th 2016

Let’s show some data

  1. In, 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: [
                            {% for name in names %}
                                    name: '{{ }}',
                                    data: [
                                        {% for frequency in name.frequencies %}
                                            {{ frequency.boys_count }},
                                        {% endfor %}
                            {% endfor %}
  4. The chart title no longer makes sense. Change this to “Name frequencies”
  5. Test this. You should see another chart



Continue to Step 5 – Flask-Restless

Namepy step 3 – Adding in Highcharts

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)
Highcharts is a JavaScript library for creating high quality interactive charts. It is not open source but requires a license fee for commercial projects

  1. Grab Highcharts from
  2. Copy highcharts.src.js (for a production site use highcharts.js) into static/external
  3. Include this in helloworld.html:
     <script src="static/external/highcharts.src.js"></script> 
  4. Ditto for jQuery, download from, and include in helloworld.html, before the highcharts link
  5. Create a container for the chart:
    <div id="container" style="min-width: 310px; height: 400px; margin: 0 auto;"></div>
  6. Create a very simple chart, e.g.:
    			$(function () { 
    			        chart: { 
    			            type: 'column' 
    			        title: { 
    		                text: 'Rabbit sightings' 
    			        xAxis: { 
    			            categories: [ 
    			        series: [{ 
    			            name: '2015', 
    			            data: [5, 7, 8, 3] 
    			        }, { 
    			            name: '2016', 
    			            data: [6, 6, 10, 5] 
  7. Start the Flask/python script (python and view the result in your browser (




Continue to Step 4 – PostgreSQL, SQLAlchemy and Flask-SQLAlchemy