Python Flask and postgreSQL

For this weeks Linux course homework (h5) at Tero Karvinen’s course, I got a homework to test out Python Flask in both test deployment and production environment.
The Final step is to connect a PostgreSQL-database and read out some data from the database.
I’ll base my experiment at this guide http://terokarvinen.com/2017/hello-python-flask-web-app-development-server-install-on-ubuntu-16-04

”Hello world” on Flask

First I start by installing the flask components and curl to test the endpoints without using my GUI web browser.

sudo apt-get update && sudo apt-get install -y python3-flask curl

The next step is to create a directory for my new python project and create the main-class inside.
I’ll name my project as ”helloFlask” and the main-class will be simply named as ”main.py”

mkdir helloFlask && cd helloFlask
nano main.py

inside the main-class file, I’ll just put the very basic structure to run Flask:

from flask import Flask
app = Flask(__name__)

@app.route(”/”)
def helloWorld():
return ”Hello World!”

if __name__ == ”__main__”:
app.run(debug=True)

It seems a bit confusing, but let me explain, what is happening here.
At the first line, I import Flask-class from the flask-library.
Next I store a new Flask object (with variable __name__)
into an app-variable. Then comes the app.route-annotation, where I define a root-level request dispatcher on top of helloWorld()-function, which
returns a string ”Hello World!”, when called.
Next, there’s a checker for __name__ to be ”__main__”-string and if so, the class will be run with debug-mode.

Now I have everything set, so let’s start the program into a test run and see, if it compiles and works.

CAUTION: the following method is only allowed to be used on the development purposes. It’s not considered safe enough to be run on
a production environment, especially on servers accessible via public Internet.
I will go through the production initialization process later at this article.

python3 main.py
returns
* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
* Restarting with stat

now it runs actively on my terminal session. To test out the root endpoint, I can use curl for that purpose.
First I open up an new terminal tab and then, with curl, I call the local ip-address and the port mentioned on Flask’s startup response.
curl 127.0.0.1:5000
response
Hello World!krister@krister-VirtualBox:~/helloFlask$
there wasn’t any linebreak on that response string, therefore the terminal prefix was printed right after the response.
Anyway, this is a proof, that my code works.

Deploying Flask project into Production environment with wsgi

Next thing is to test, how the production deployment works. I’ll be following the points of this tutorial http://terokarvinen.com/2016/deploy-flask-python3-on-apache2-ubuntu
First I need an apache2-server installed

sudo apt-get install apache2
curl http://localhost/ | grep title
results
Apache2 Ubuntu Default Page: It works

Now that the apache server is up and running, it’s time to install mod_wsgi into it

sudo apt-get install -y libapache2-mod-wsgi-py3

The next thing is to create a configuration file for apache2 that runs my helloFlask-project

sudoedit /etc/apache2/sites-available/helloFlask.conf

content

ServerName virtualbox.krister.com

WSGIDaemonProcess helloFlask user=krister group=krister threads=5
WSGIScriptAlias / /home/krister/helloFlask/main.wsgi

WSGIProcessGroup helloFlask
WSGIApplicationGroup %{GLOBAL}
WSGIScriptReloading On

Require all granted

This just simply a virtualhost-configuration that defines the home path of my project.

Since the main.wsgi-file doesn’t exist yet, let’s create it now

nano ~/helloFlask/main.wsgi
content
import sys

if sys.version_info[0]<3: #check if is run with python3
raise Exception("Python3 is required to run this program! Current version: '%s'" %
sys.version_info)

sys.path.insert(0,'/home/krister/helloFlask/') # path where the project is located
from main import app as application

Now I shall disable the default page configuration from apache2 and enable this new config, helloFlask.conf, instead

sudo a2dissite 000-default.conf
sudo a2ensite helloFlask.conf
sudo service apache2 restart

now I’m going to test, if this setting works with curl. Luckily, since the apache2-server handles the traffic, I can just call plain localhost
curl http://localhost/
result
Hello World!krister@krister-VirtualBox:~/helloFlask$

again, the linebreak was missing, but it printed out the ”Hello World!”-phrase, as I wanted, so it’s quaranteed to work.

Read content from PostgreSQL with Python Flask

I’ll base my testing to this tutorial http://terokarvinen.com/2017/database-connection-from-python-flask-to-postgre-using-raw-sql

First of all, let’s install PostgreSQL

sudo apt-get install -y postgresql

next, I create a new database

sudo -u postgres createdb helloflask

and finally, I create a new database user

sudo -u postgres createuser krister

the next thing is to install sql-alchemy and postgresql-flask-module

sudo apt-get install -y python3-flask-sqlalchemy python3-psycopg2

and finally, let’s modify my main.py file to insert some data into the db and read it out when the endpoint is called

nano ~/helloFlask/main.py
content
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://krister/helloflask'
app.config['SECRET_KEY'] = 'k377AglooNex+932.asdjReajeIxane436'

def sql(rawSql, sqlVars={}):
assert type(rawSql)==str
assert type(sqlVars)==dict
res=db.session.execute(rawSql, sqlVars)
db.session.commit()
return res

@app.before_first_request
def initDBforFlask():
sql(”CREATE TABLE IF NOT EXISTS members (id SERIAL PRIMARY KEY, name VARCHAR(160) UNIQUE);”)
sql(”INSERT INTO members(name) VALUES (’Tom Johnson’),(’John Thompson’) ON CONFLICT (name) DO NOTHING;”)

@app.route(”/”)
def helloWorld():
return ”Hello World!”

@app.route(”/members”)
def members():
members=sql(”SELECT * FROM members;”)
return render_template(”members.html”, members=members)

if __name__ == ”__main__”:
from flask_sqlalchemy import get_debug_queries
app.run(debug=True)

to parse out the database content cleanly, I’ll define an html-template named members.html

mkdir ~/helloFlask/templates
nano ~/helloFlask/templates/members.html
content

memberlist

Member list

{% for member in members %}

{{ member.name }}

{% endfor %}

and now, curl localhost/members

apparently there’s an error in the code, since I got Internal Server Error 500

For some reason, the apache logs don’t say anything about it, even if I try to grep

Let’s run it on test environment

python3 ~/helloFlask/main.py

curl localhost:5000/members

okay, now I got the error traceback
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "krister" to address: Name or service not known

It’s propably a syntax error, according to this https://stackoverflow.com/questions/23839656/sqlalchemy-no-password-supplied-error

the correct syntax is postgresql://user:password@localhost:5432/database_name so let’s change that part on SQLALCHEMY_DATABASE_URI

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql:///helloflask

curl localhost:5000/members

nice! now it works!