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 BotBook.com 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

# moi.py
# Copyright 2017 Tero Karvinen http://TeroKarvinen.com
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)
 db.session.commit()
 return res
@app.before_first_request
def initDBforFlask():
 sql("CREATE TABLE IF NOT EXISTS animals (id SERIAL PRIMARY KEY, name VARCHAR(160) UNIQUE);")
 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 ('TeroKarvinen.com') ON CONFLICT (name) DO NOTHING;")
 sql("INSERT INTO animals(name) VALUES ('Horse') ON CONFLICT (name) DO NOTHING;")
@app.route("/")
def hello():
 return "See you at TeroKarvinen.com! <a href='/animals'>List animals</a>\n"
@app.route("/animals")
def animals():
 animals=sql("SELECT * FROM animals;")
 return render_template("animals.html", animals=animals)
if __name__ == "__main__":
 from flask_sqlalchemy import get_debug_queries
 app.run(debug=True)

Template with a Loop

templates/animals.html

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

templates/base.html

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

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
DROP TABLE
xubuntu=> \d
No relations found.
xubuntu=> ctrl-D

Result


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?

Adminstrivia

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 | Comments Off on Database Connection from Python Flask to Postgre, Using Plain SQL

Comments are closed.