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

Namepy step 2 – Flask and Angular

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

We’re going to need some server-side processing, for which I’ll be using Python and the Flask micro framework

  1. I recommend you use a virtualenv for this project
  2. Install flask
    pip install flask
    
  3. Create /hello.py
    from flask import Flask
    app = Flask(__name___)
    app.config['DEBUG'] = True
    
    @app.route("/")
    def hello():
        return "Hello world!"
    
    if __name__ == "__main__":
        app.run()
    

Test this:

  1. Start Flask and the hello.py Python script
    python hello.py
  2. In your browser, go to http://127.0.0.1:5000/. This should show

    “Hello world!”

Show the Angular page from step 1

  1. In hello.py, add
    from flask import render_template
  2. hello() function, replace last line with
    return render_template('helloworld.html')
  3. Move helloworld.html to (project root)/templates
  4. Test this (see above). This will bring up

    Welcome, today is {{ dateToday }}

As you can see, this isn’t quite right. If you open up the browser’s console (e.g. ctrl-shift-j in Google Chrome) you’ll see that the browser can’t find the external files (AngularJS and MomentJS). Let’s fix this first

  1. Create (project root)/static. This is where Flask will be looking for anything in (url)/static, without doing any further processing
  2. Move /external to /static. You should now have /static/external, containing angular.js and moment.js
  3. In templates/helloworld.html, change the link from “external/angular.js” to “static/external/angular.js”
  4. Do the same for moment.js
  5. Test this again. This will show

    Welcome, today is

Almost there. If you check in your console you’ll see that the error messages are no longer there. However, the {{ dateToday }} has disappeared. If you view the web page’s source code you’ll see that the line has been replaced with “<p>Welcome, today is </p>”. This is because the Jinja template engine used by Flask also uses double curly brackets {{ }}, just like Angular. Jinja processes (and removes) the double curly brackets before it gets to Angular. There are a number of solutions for this, such as telling Angular to use double square brackets [[ ]] instead:

  1. In helloworld.html, after the line “angular.module(‘HelloWorldApp’, [])”, add:
        .config(function($interpolateProvider){
            $interpolateProvider.startSymbol('[[').endSymbol(']]');
        })
    
  2. And replace the {{ }} brackets with [[ ]]:
    
    
    Welcome, today is [[ dateToday ]]
    
    
  3. Test this. You should now see

    Welcome, today is June 7th 2016

Going live

Done

Next

Continue to Step 3 – Adding in Highcharts

Namepy step 1 – Angular “Hello World”

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

Let’s make a very simple start, with a webpage with a basic Angular controller, just enough to show that Angular is loaded and active.

Each step will build upon the previous and will live in its own folder/subpath. The external libraries will live in “external”.

Full code is in the git repo. The step-by-step instructions contain only the most relevant, new and/or tricky parts

  1. Download latest version of Angular from https://angularjs.org/ and store in project_root/external
  2. For date processing we’ll be using MomentJs. Download it from http://momentjs.com/ and store in project_root/external
  3. Create a html page, called helloworld.html and save it in project_ root
  4. Load angular.js and moment.js, using the <script src=”…”></script> tag
  5. Create a simple Angular module and controller
    angular.module('HelloWorldApp', [])
    .controller('HelloWorldController', function($scope) {
    $scope.dateToday = moment().format('MMMM Do YYYY')
    });
    
  6. Load the module and controller:
    <body ng-app="HelloWorldApp" ng-controller="HelloWorldController"</body>
    
  7. Create a curly-bracket expression to show the dateToday
    Welcome, today is {{dateToday}}
    
  8. Save the page

Done

Next

Continue to Step 2 – Flask and Angular

Namepy – on the shoulders of giants

Whilst my core skill/tool is Python, I’m always learning new things, either inside or outside the Python ecosystem. I recently had the pleasure of working with Angular and Python/Flask. Here is a playful application based on these, plus Highcharts.

Going through “Python for Data Analysis”, some of the examples use a database of frequency of (US) baby names since 1880. I thought I’d combine this with a bit of Scrabble™.

In the Python world it’s common to add “py” to a word when making up names, so I’m calling this project “namepy”.

Since I’ll be using various frameworks and libraries, all created by others, I’ve subtitled this “On the shoulders of giants”.

Taking small steps often results in faster progress, so that’s what I’m be doing here.

Technical set up

The source code is at https://github.com/CoachCoen/namepy, with one branch per step.

Many production sites Content Delivery Networks for serving Javascript frameworks and libraries, usually minified, which helps to take the load of the server and may speed up first page load. To keep things simple and stable over time, I’m using full-sized, downloaded, copies.

I’m using WebFaction (affiliate link) as the host, since they make it easy to create Flask, Django and similar projects. And, as a popular host for developers, you’ll find lots of helpful documentation for developers online.

Getting started

Create a project folder

mkdir namepy
cd namepy

At the start of each of the steps

cd (my folder for personal projects)
cd namepy
git clone https://github.com/CoachCoen/namepy.git -b step1 step1

Note: “-b step1” specifies the name of the branch to clone. The second “step1” is the target folder, i.e. namepy/step1.

Next

Continue to Step 1 – Angular “Hello World”

Investment Tracking System – Django/Python

My client, a start up with a lot of experience in their field, had identified an important gap in the market. Large sums of money were being invested, with very long payback periods, without access to effective performance tracking tools.

They designed a tool to cover the gap and asked me to create a demonstration system in preparation for generating interest and raising capital.

I developed the system in Django, Python, PostgreSQL and Javascript. The front end uses a dashboard template based on Bootstrap and jQuery. Graphs are created using the excellent Highcharts charting library.

The resulting system imports the base data and generates monthly cost and revenue forecasts, taking into account seasonal variations, tax allowances and more.

Selection_005

The main management screen gives quick access to some key performance indicators.

Selection_006

Constraints can be defined, and potential investments can be checked against them.

Selection_007

Actual results can be compared against the projections.

Selection_008

Different heat maps show absolute or relative performance by state or county.

Selection_009

This was an eight month intensive project, resulting in a demo site which generated a lot of interest in the industry and allowed the client to achieve their first round of funding.

VirtualBox – moving virtual disks

Having installed VirtualBox I created a few virtual machines, with 20GB virtual disks each. After all, I’ve got a 1TB hard disk in my computer.

When Linux started complaining about running out of disk space I realised that my main drive is a 256GB Solid State Drive, and that is where VirtualBox was storing the virtual disks. Time to move the virtual disks. Please note that it is a bit of a manual process and, whilst it worked fine for me, if you’ve already invested a lot of time setting up your virtual machines, I suggest that you test this out by moving one or two VMs first.

  1. Stop all VMs, stop VirtualBox
  2. Find a folder called “VirtualBox VMs” (probably in your home folder), and move it to the new location
    1. Or move just a few files first, to make sure it works
  3. Restart VirtualBox
    1. In File -> Preferences -> General, point the Default Machine Folder to the new location
    2. All your existing VMs are now broken. Delete them
    3. One at a time: Machine -> Add
      1. Select the .vbox file from the new folder

Virtual machines using VirtualBox

Tapping into all the wonderful tools and languages that make our lives as developers so interesting can throw up some fun (?) challenges.

You start installing the latest linter or library, and before you know it you’re in dependency hell. Required libraries require even more libraries, with difference versions numbers and clashing with your existing setup.  If you’re really unlucky you break some essential software along the way.

A safer approach is to use virtual machines, for experimentation, and to keep different (and clashing) environments separate.

For more detail see the step by step instructions at Everyday Linux User.

These instructions are for installing VirtualBox on Linux Mint (17.2), and then creating a Linux Mint virtual machine

Installation and setup

I used mostly used the default settings. This may not be right for everyone

  1. Use a package manager to install VirtualBox.
    1. I used Synaptic, and selected virtualbox-5.0
  2. When I started VirtualBox and tried to create my first virtual machine (VM), it only let me create 32 bit VMs, even though my computer is fully 64 bit
    1. Fix this by going into the BIOS and enabling (Intel) Virtualisation
  3. If like me you have multiple drives (SSDs and/or HDDs) or multiple partitions, specify where VirtualBox stores the virtual drives (files):
    1. File -> Preferences -> General -> Default Machine Folder

Your first virtual machine

  1. Download a copy of the relevant linux Distro
  2. Start VirtualBox: Menu -> Administrate -> Oracle VM VirtualBox
  3. Click on “New”
    1. Name: E.g. “Linux Mint 1”
    2. Type: “Linux”
    3. Version: Linux 2.6/3.x (64 bit) or Other Linux (64 bit)
    4. Memory: Recommended size or higher (note: you can always change it later)
      1. During my first attemp I used the default of 256MB. When trying to boot Linux Mint off the install ‘disk’, the virtual machine ground down to a halt. Increasing this to 1GB fixed this)
      2. 512MB minimum, 1GB+ is probably better
  4. Hard drive: Create a virtual drive now
    1. Note: VirtualBox creates a file and pretends that it is a whole hard disk (i.e. a “virtual drive”)
    2. Type: leave as is (VDI)
    3. Size: Dynamically allocated
    4. Limit: leave as is or increase
    5. Create
  5. The machine has been created, but isn’t running yet. Click on the “New” button
    1. Click on the folder with green ‘arrow’ icon
    2. Select the previously download distro
    3. Click Start
      1. This will start the VM, which will boot off the downloaded distro
    4. Follow the instructions to install the new OS
    5. Suggestion: Use a different password from your main password. This is generally good security practice, but in this case it may stop you doing something on your actual Linux install whilst thinking you’re working on a VM. I realised this when I tried to stripdown all s/w on a VM to only that relevant to development work, and nearly removed some software of the host operating system
  6. If you get a large error message “Running in software rendering mode”:
    1. With VM stopped, go into settings -> Display -> Enable 3D accelleration

I suggest you clone this VM before you start experimenting, to save you having to reinstall the OS should you need another (clean) VM

Installing Python 2.10 on Linux Mint 17.2

Warning: You may have some packages on your machine which rely on Python, and which may no longer work after installing a different version of Python. Ideally you should use a virtual machine for this

From https://slobaexpert.wordpress.com/2015/07/09/upgrade-python-2-7-6-to-python-2-7-10-on-linux-mint-os/:

  1. Download Gzipped source tarball from the Python website
  2. Unzip the downloaded file
  3. Switch to new folder (e.g. /Downloads/Python-2.7.10)
  4. In a terminal window:
    1. apt-get install libc-dev
      1. If you miss this, you’ll get an error: error: C compiler cannot create executables
    2. ./configure
    3. sudo make install
  5. Run “python –version”
    1. You should now see 2.7.10

Bottle – Python micro framework

Like Flask, Bottle is a Python micro-framework. It is so micro that it only consists of a single file. Whilst Flask is already a fairly small framework, some developers prefer Bottle, mainly for its easy of setup (single file, no dependencies)

The Bottle home page gives a Hello World example

Local trial

  1. Create and activate a new virtualenv or use a virtual machine.
    1. I use a VM for experimenting with Python libraries and frameworks, to keep it separate from my client work
  2. sudo pip install bottle
  3. Create hello_world.py with the code from the Bottle home page
  4. Run the script: python hello_world.py
  5. Check in your browser: http://localhost:8080/hello/world
    1. This should show “Hello world!”
    2. Also try it with your own name, e.g. http://localhost:8080/hello/Coen

On a server – using wsgi

For Python I use WebFaction, which makes it very easy to create new Python applications

In your WebFaction control panel:

  1. Domains / Websites -> Websites
    1. Select the domain
  2. Click on “Add an application” -> Create a new application
    1. Name: bottle_hello_world
    2. Category: mod_wsgi
    3. Type: mod_wsgi / Python 2.7 (note: Bottle also works with Python 3.x)
    4. URL: /bottle-hello-world
    5. Save
    6. Click Save again
  3. ssh into the host
    1. cd webapps
    2. cd <app name> (e.g. bottle_hello_world)
    3. cd htdocs
    4. pip install bottle
  4. Check bottle is installed
    1. python2.7
    2. import bottle
  5. Adapt code for wsgi (based on instructions at http://bottlepy.org/docs/dev/deployment.html#apache-mod-wsgi) and replace the contents of index.py with:

    import os
    # Change working directory so relative paths (and template lookup) work again
    os.chdir(os.path.dirname(__file__))

    import bottle
    from bottle import route, run, template
    application = bottle.default_app()

    @route(‘/hello/<name>’)
    def index(name):
    return template(‘<b>Hello {{name}}</b>!’, name=name)

  6. Test it, e.g. http://cm-demo.com/bottle-hello-world/index.py/hello/fred
    1. This should show “Hello fred!”