Heroku Connect with Flask and Psycopg2

Heroku Connect makes it easy to build Heroku apps that share data with your Salesforce organization. This post shows you how to run a Python Flask app with Psycopg2 on Heroku, which uses Heroku Connect to sync data between PostgreSQL and Salesforce. Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Psycopg2 is a DB API 2.0 compliant PostgreSQL driver.

 

Figure 1: Heroku-Connect Data flow

Figure 1 shows how the HerokuConnect Add-On interacts with Heroku Postgres and Force.com behind the scenes. Make sure you have Python installed. Also, install the Heroku Toolbelt.

Install the Virtual Environment

Create a folder flask-psycopg2-sample and install a virtual environment in it.

$ mkdir flask-psycopg2-sample
$ cd flask-psycopg2-sample
$ virtualenv venv
$ source venv/bin/activate

Install Dependencies

$ pip install flask gunicorn

Creating a Simple Flask App

  1. First Create a base Flask app with simple REST endpoint/ in a file app.py in the folder created above.

    from flask import Flask
    app = Flask(__name__)
    
    @app.route('/')
    def hello_world():
     return 'Hello World!'
    
    if __name__ == '__main__':
     app.run() 
  2. Run the app using the following command

    $ python app.py
    

    Your app should now be running on localhost:5000

Initialize git

Initialize the git repository as shown by commands below.

$ git init
$ git add .
$ git commit -m "initial commit"

Create a Requirements File

$ pip freeze > requirements.txt

Create a Procfile

Create a file named Procfile in the root of the app and add the following content. Procfile specifies that the app uses a web dyno with gunicorn as HTTP server.

web: gunicorn app:app --log-file=-

Deploying to Heroku

$ heroku create
$ git push heroku master
$ heroku open

Add PostgreSQL Add-On

Add Postgress Add-On as shown below

$ heroku addons:create heroku-postgresql:hobby-dev

Add the Heroku Connect Add-On

Configure the Heroku Connect Add-On. The command below configures the Heroku-Connect Add-On to the application.

$ heroku addons:create herokuconnect

Configure Heroku Connect Add-On

  1. Setup Connection by pressing the “Setup Connection” button as shown below

  1. Enter Schema Name: This is the schema name under which database will be created. Default is set to “salesforce”

  1. Trigger OAuth by clicking the Authorize button shown below

  1. Enter Salesforce.com developer account credentials:

  1. Create Mappings :

  1. Create Mappings Contacts: Choose the fields in Salesforce Schema which need to be mapped to Postgres Database in the application.

  1. Explore Contacts in the Dashboard by pressing Explorer button :

Add Code for contacts endpoint

First Add following lines which configure the Connection object conn to PostgreSQL Database.

url = urlparse.urlparse(os.environ.get('DATABASE_URL'))
db = "dbname=%s user=%s password=%s host=%s " % (url.path[1:], url.username, url.password, url.hostname)
schema = "schema.sql"
conn = psycopg2.connect(db)
cur = conn.cursor()

Add code for the Getting the Contacts

@app.route('/contacts')
def contacts():
    try:
        cur.execute("""SELECT name from salesforce.contact""")
        rows = cur.fetchall()
        response = ''
        my_list = []
        for row in rows:
            my_list.append(row[0])

        return render_template('template.html',  results=my_list)
    except Exception as e:
        print e
        return []

Complete code listing:

import os
import psycopg2
from flask import Flask, render_template
import urlparse
from os.path import exists
from os import makedirs

url = urlparse.urlparse(os.environ.get('DATABASE_URL'))
db = "dbname=%s user=%s password=%s host=%s " % (url.path[1:], url.username, url.password, url.hostname)
schema = "schema.sql"
conn = psycopg2.connect(db)

cur = conn.cursor()

app = Flask(__name__)

@app.route('/')
def hello():
    return 'Hello World!'

@app.route('/contacts')
def contacts():
    try:
        cur.execute("""SELECT name from salesforce.contact""")
        rows = cur.fetchall()
        response = ''
        my_list = []
        for row in rows:
            my_list.append(row[0])

        return render_template('template.html',  results=my_list)
    except Exception as e:
        print e
        return []

if __name__ == '__main__':
    app.run()

Add Jinja Template

The code shown in the previous section uses template.html file which is a Jinja template. Add this file under folder templates

<html>
<head>
<title>Flask Template Example</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet" media="screen">
<style type="text/css">
.container {
max-width: 500px;
padding-top: 100px;
}
</style>
</head>
<body>
<div class="container">
<p>Contacts:</p>
<ul>
{% for r in results %}
<li>{{r}}</li>
{% endfor %}
</ul>
</div>
<script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
<script src="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/js/bootstrap.min.js">
</script>
</body>
</html>

Update python packages

$ pip install psycopg2

Add Requirements file

$ pip freeze > requirements.txt

Create a Procfile

Create a Procfile which will be used to identify the web dyno and type of runtime

web: gunicorn app:app --log-file=-

Update Changes in Heroku

$ git add .
$ git commit -m "Added code for contacts"
$ git push heroku master

Open the App again in Heroku

$ heroku open

Show Contacts

Browse to URL http://{your-app-name}.herokuapp.com/contacts to see the list of contact names.

Summary

In this tutorial, we learned how to configure a Python Flask Application to work with Heroku Connect. We used Psycopg2 driver for talking to the PostgreSQL database deployed on Heroku.

About the Author

Rajdeep Dua is Director Developer Relations at Salesforce. He is passionate about helping developers learn about Cloud Computing and Salesforce. He has over 16 years of experience in Software Product Development and Developer Relations.

Leave your comments...

Heroku Connect with Flask and Psycopg2