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.
- 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
- 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
- Import flask-sqlalchemy
from flask_sqlalchemy import SQLAlchemy
- 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
- 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)
- 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
- 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()
- And run the script
python >>> from hello import create_test_data >>> create_test_data()
- 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
- 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
- If you’re using git, make sure this isn’t under version control. Create/update .gitignore to include the following:
config.py
- 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
- 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)
- Create an “app” folder in the project_root folder
- 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
- Move models.py into the app folder and change the first line to:
from app import db
- In the app folder, create views.py, containing @app.route(“/”) and hello() function from hello.py, plus necessary imports
- Test this. You should get
Welcome, today is June 7th 2016
Let’s show some data
- In views.py, add:
from models import Name
- In render_template, pass in the Name objects:
return render_template('helloworld.html', names=Name.query.all())
- 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}} ]
- The chart title no longer makes sense. Change this to “Name frequencies”
- Test this. You should see another chart
Done
- For creating the new database on WebFaction I followed the instructions at https://docs.webfaction.com/user-guide/databases.html
- See the result at http://cm-demo.com/namepy_step4/
- The source code is at https://github.com/CoachCoen/namepy/tree/step4
Next
Continue to Step 5 – Flask-Restless