Skip to content

ORM's in python

Object Relational Mappers convert a python object to a database row, and vice versa. The most popular ORM in python is SqlAlchemy. 

import sqlalchemy
from sqlalchemy import create_engine, inspect # helper functions
db_url = 'postgresql://username:password@hostname:hostnumber/database'

SqlAlchemy

SqlAlchemy engine is a helper (database client) that does our connection to our database and runs our statements for us. In this blog, we are connecting to the database that I created as described in Handling databases using python.

engine = create_engine(db_url)

An inspector is another helper that explores the database, for example to get table names, SqlAlchemy talks to the database, consults its schema and gets the table names. 

inspector = inspect(engine)
print(inspector.get_table_names())

['daily_historic_pollution', 'daily_prediction_pollution']

We have two tables in the database, daily_historic_prediction and daily_prediction_pollution. We can now use SqlAlchemy to run a query

result = engine.execute('SELECT * FROM daily_historic_pollution ORDER BY date DESC LIMIT 10')

for r in result:
    print(r.date, r.value)

2021-12-10 163
2021-10-15 158
2021-10-14 148
2021-10-03 50
2021-10-02 47
2021-10-01 87
2021-09-30 63
2021-09-29 43
2021-09-28 41
2021-09-27 61

In this way, we can get the latest 10 data points in our dataset. We could integrate this to a web server so that our web server can talk to our database.

Flask web app

In this blog, we will be using a python web server called Flask. Flask is simple, light and customisable that can generate simple web apps with a few pages, read/set cookies and connect to databases using ORM's. To use the ORM, we have to first develop a flask app. One of the important things to set up in an ORM is the list of models. Models are python objects that map onto entities in the database. For example, we want a historic_pollution table with a row for each date in the past. The ORM object will generate python objects for each row allowing us to easily work with rows in python rather than in SQL. One of the main jobs of the ORM is to notice changes of the python object and construct SQL statements to automatically modify the database accordingly.
The flask app is present in the GitHub repository flask-web-app with the complete code discussed here.
Inside the app folder, we have a file called models.py. This holds our database models, and tells us how Python is going to translate between data in the database tables and python objects. We are defining two python classes here. Each of them maps directly onto a table in the database. Each of the lines inside the class tells how to treat one of the columns in the database. For instance, we have date, type and value in the daily_historic_pollution table, and for each of them we have to tell SqlAlchemy the type. We can also mention the primary key, index and unique columns etc. This file allows SqlAlchemy to do two things:
1. Read from the database
2. Build SQL statements and execute them 

# File models.py
from app import db
from sqlalchemy.orm import relationship
from sqlalchemy import Table, Column, Integer, ForeignKey

class Historic(db.Model):
  __tablename__ = 'daily_historic_pollution'
  date = db.Column(db.DateTime, ForeignKey('daily_prediction_pollution.date'), primary_key=True)
  type = db.Column(db.String(255))
  value = db.Column(db.Integer)

  def __repr__(self):
    return 'Date: ' + self.date + ' PM25: ' + self.value

class Prediction(db.Model):
  __tablename__ = 'daily_prediction_pollution'
  date = db.Column(db.DateTime, primary_key=True)
  type = db.Column(db.String(255))
  prediction = db.Column(db.Float)

  past_pred = relationship('Historic')

  def __repr__(self):
    return 'Date: ' + self.date + ' PM25: ' + self.value

In the routes.py file we are declaring the routes, or the web addresses that we can visit using our python server. The first one is for the home page, and it renders index.html page. The @ sign is a decorator saying that if we request "/" or "/index" the first function (index function) will be run. For the "/raw_historic_data" a simple query to select the latest 15 values are selected and the raw_historic_data.html is rendered.

# File routes.py
from flask import render_template

from app import app,db
from app.models import Historic, Prediction
import pdb
from sqlalchemy import desc

@app.route('/')
@app.route('/index')
def index():
  return render_template('index.html')

@app.route('/raw_historic_data')
def historic():
  historic = Historic.query.order_by(desc(Historic.date)).limit(15).all()
  return render_template('raw_historic_data.html', historic = historic)

@app.route('/raw_predicted_data')
def predicted():
  prediction = Prediction.query.order_by(desc(Prediction.date)).limit(15).all()
  return render_template('raw_prediction_data.html', predicted = prediction)

In 'raw_historic_data.html' we have a template with a python loop taking the input from the query and displaying the data. The snippet of the code is shown:

<table>
    <tr><td><b>Date</b></td>&nbsp;<td><b>PM25</b></td></tr>
    {% for day in historic %}
    <tr><td>{{ day.date }}</td>&nbsp;<td>{{ day.value }}</td></tr>
    {% endfor %}

</table>

To run flask, we simply need to run "flask run" and the Flask app runs on local server http://127.0.0.1:5000/.

Deployment on Heroku

Heroku is a cloud platform which supports Python, Ruby and various other programming languages. It has many cloud based products including Heroku platform (runs customer apps in virtual containers), Heroku Postgres (cloud database) and many others making it a platform as a service product.
First we have to create an app on heroku. I have created the app hydpm25 (hyderabad-PM25). Then deployment is easy through connecting it with git repository. A proc file containing "web: gunicorn app:app" will serve the web app on heroku. The final app can be found at hydpm25.herokuapp.com

References

  1. ORMs in Python, Fintan Nagle, Fundamentals of Database Technologies module, MSc Business analytics, Imperial College London, Class 2020-22
Back to top