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>'
    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: [
                            {% for name in names %}
                                {
                                    name: '{{ name.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

Done

Next

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 http://www.highcharts.com/download
  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 https://jquery.com/download/, 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 () { 
    			    $('#container').highcharts({ 
    			        chart: { 
    			            type: 'column' 
    			        }, 
    			        title: { 
    		                text: 'Rabbit sightings' 
    			        }, 
    			        xAxis: { 
    			            categories: [ 
    			                'Spring', 
    			                'Summer', 
    			                'Autumn', 
    			                'Winter' 
    			            ], 
    			        }, 
    			        series: [{ 
    			            name: '2015', 
    			            data: [5, 7, 8, 3] 
    			        }, { 
    			            name: '2016', 
    			            data: [6, 6, 10, 5] 
    
    			        }] 
    			    }); 
    			}); 
    
  7. Start the Flask/python script (python hello.py) and view the result in your browser (127.0.0.1:5000)

 

Done

Next

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