Database Connection from Python Flask to Postgre, Using Plain SQL

Reading PostgreSQL database with Python.

Create a website with database. Use the simple popular Python framework, Flask. Apply your existing SQL skills.

This short program creates a new PostgreSQL database and inserts some records.
If database and sample records already exist, initialization is skipped.
Finally, the records are read from the database and shown on a web page.

These notes contain code from projects.

This is a short example, not a complete tutorial.
Start with working production style installation of Flask, using Apache mod_wsgi and a working PostgreSQL user and database.

Install Required Packages

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

Main Program

# Copyright 2017 Tero Karvinen
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql:///xubuntu'
app.config['SECRET_KEY'] = 'k377AglooNex+932.asdjReajeIxane436'
def sql(rawSql, sqlVars={}):
 "Execute raw sql, optionally with prepared query"
 assert type(rawSql)==str
 assert type(sqlVars)==dict
 res=db.session.execute(rawSql, sqlVars)
 return res
def initDBforFlask():
 sql("INSERT INTO animals(name) VALUES ('Lion') ON CONFLICT (name) DO NOTHING;")
 sql("INSERT INTO animals(name) VALUES ('Dragon') ON CONFLICT (name) DO NOTHING;")
 sql("INSERT INTO animals(name) VALUES ('') ON CONFLICT (name) DO NOTHING;")
 sql("INSERT INTO animals(name) VALUES ('Horse') ON CONFLICT (name) DO NOTHING;")
def hello():
 return "See you at! <a href='/animals'>List animals</a>\n"
def animals():
 animals=sql("SELECT * FROM animals;")
 return render_template("animals.html", animals=animals)
if __name__ == "__main__":
 from flask_sqlalchemy import get_debug_queries

Template with a Loop


{% extends "base.html" %}
{%block title%} Animals {%endblock title%}
{% block contents %}
 {% for animal in animals %}
 <p>{{ }}</p>
 {% endfor %}
{% endblock contents %}


<!doctype html>
 {%block title%}
 Hello World
 {%endblock title%}
 <meta charset="utf-8" />
 {% block contents %}
 <h1>Hello World</h1>
 <p>Let's test UTF-8 with "päivää"</p>
 {% endblock contents %}

Dropping Tables

If you want to verify that the code works from scratch, you can drop the tables and have them created again. This will lose all data on the tables, no undo.

$ psql
xubuntu=> \d
 List of relations
 Schema |      Name      |   Type   |  Owner  
 public | animals        | table    | xubuntu
 public | animals_id_seq | sequence | xubuntu
(2 rows)
xubuntu=> DROP TABLE animals; # NO UNDO
xubuntu=> \d
No relations found.
xubuntu=> ctrl-D


Did you see the records from you database? Well done!
What next? Write a whole CRUD app: create, read, update, delete. What about a TODO-list?


Tested on Xubuntu 16.04.3 amd64 live USB. ($ grep DESC /etc/lsb-release; uname -m)
Other software used for testing: apache2 2.4.18-2ubuntu3.5 libapache2-mod-wsgi-py3 4.3.0-1.1build1 postgresql 9.5+173 python3 3.5.1-3 python3-flask 0.10.1-2build2 python3-flask-sqlalchemy 1.0-3 python3-psycopg2 2.6.1-1build2 ($ dpkg –list apache2 postgresql python3 python3-psycopg2 python3-flask python3-flask-sqlalchemy libapache2-mod-wsgi-py3|grep ^ii|awk ‘{ print $2 ” ” $3}’|tr ‘\n’ ‘ ‘)

Posted in Uncategorized | Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *