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’ ‘ ‘)