How to build a web application using Sync Heroku app (Heroku PostGres+Heroku Connect) with Salesforce using Python Flask

Leave a Comment / Python / By

Introduction

This workshop shows how to Create and Run a Python app with psycopg2 which uses PostgreSQL based Heroku Connect

Figure 1 show 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

We will be using a basic Flask app available at flaskpy-sfdc

This app has four rest endpoints

@app.route(\'/\') which displays Hello world
@app.route(\'/contacts\') which fetches list of contacts from Postgres table salesforce.contact
@app.route(\'/create_contact\', methods=[\'POST\',\'GET\']) which creates a new contact
@app.route(\'/contactform\') which serves an HTML Form for entering new contact details

Prerequisites

This workshop assumes you have following setup

Clone the Source Code

$ git clone https://github.com/salesforce888/flaskpy-sfdc

Heroku Login

First download CLI, install it and run the following command.

$ heroku login

Requirements File

Note : This section is for information only

Notice that the Requirements file already exists, this will be used by Heroku to setup the dynos

    $ cat requirements.txt
    Flask==0.10.1
    gunicorn==19.3.0
    itsdangerous==0.24
    Jinja2==2.8
    MarkupSafe==0.23
    psycopg2==2.6.1
    Werkzeug==0.11
    wheel==0.24.0

Procfile

Note : This section is for information only

There is already a Procfile which tells the Heroku what kind of Dyno is required and the source for the application.

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

DB Initialization

Note : This section is for information only

We will parsing the DATABASE_URL environment variable to connect to PostgreSQL database. The PostgreSQL Python driver is already configured in the requirements file.

Steps are

  1. Parse DATABASE_URL into variable url
  2. Create a connection string db from the parsed url.
  3. Create a Database connection conn.
  4. Open a Database cursor cur.
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()

Flask Controller

Note : This section is for information only

app.py is the main controller for our applications and code listing below shows the implementation of various use cases

List 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 []

Create Contacts

Implementation of /createcontactform endpoint

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

Implementation of /create_contact endpoint.

@app.route(\'/create_contact\', methods=[\'POST\',\'GET\'])
def create_contact():

    try:
        if request.method == \"POST\":
            first_name = request.form[\"first-name\"]
            last_name = request.form[\"last-name\"]
            email = request.form[\"email\"]

            app.logger.info(first_name)
            statement = \"insert into salesforce.contact(firstname, 
                        lastname, email) values (\'\" \\
                + first_name + \"\',\'\" + last_name + \"\',\'\" + email + \"\');\"
            cur.execute(statement)
            conn.commit()
            errors = []
            return render_template(\'result.html\', errors=errors, 
                                   firstname=first_name,
                                   lastname=last_name)
    except Exception as e:
        print(e)
        return []

Deploying to Heroku

Before moving on, create a Heroku account and run $ heroku login command to login to your created heroku account.

$ 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 Heroku Connect Add-On

Configure Heroku Connect Add-On. Command below configures Herok-Connect Add-On to the application.

  $ heroku addons:create herokuconnect

Configure Heroku Connect Add-On

  • Setup Connection
  • Enter Schema Name : This is the schema name underwhich database will be created.
  • Trigger OAuth
  • Enter Salesforce.com developer account credentials
  • Create Mappings
  • Create Mappings Contacts : Choose the fields in Salesforce Schema which need to be mapped to Postgres Database in the application.
  • Write Enable : Make sure you enable Write to Salesforce any updates to your database check box
  • Click on settings to get the database details and take URI  as a DATABASE_URL.
  • Explore Contacts in the Dashboard

Finally it looks like this:

\"heroku

Home Page

\"herliuflast\"

Contact List

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

\"contactlist\"

Create a New Contact

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

\"createcontact\"

Optional Step Show Contacts Locally

Prerequisites

  • Python 2.7
  • pip
  • virtualenv
  • PostgreSQL client (Optional if you want to run the application locally)
  1. Install Virtual EnvironmentGo to the application Folder flask-psycopg2-sample and install a virtual environment in it. 
    $ cd flask-psycopg2-v2
$ virtualenv venv
$ source venv/bin/activate
  2. Install Dependencies 
     $ pip install flask gunicorn psycopg2
  3. Configure the DATABASE_URL in the local environment 
      $ heroku config

  === fast-sands-40695 Config Vars
  DATABASE_URL:      postgres://<user_name>:<password>@<ipaddress>.compute-1.amazonaws.com:5432/<database_name>
  HEROKUCONNECT_URL: DATABASE_URL:salesforce
  4. Export DATABASE_URL 
        
 $ export DATABASE_URL=postgres://<user_name>:<password>@<ipaddress>.compute-1.amazonaws.com:5432/db

    Open the following URL http://localhost:5000/contacts you should be able see the contacts.

  5. Run the app using the following command 
     $ python app.py

Your app should now be running on localhost:5000

Summary

In this workshop we learnt 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.

Appendix:

Executing using PyCharm

    • Install the Pycharm. And add your directory as a project to PyCharm.

     

    • Set the Environment variable as DATABASE_URL=postgres://<user_name>:<password>@<ipaddress>.compute-1.amazonaws.com:5432/<database_name> by following the path File > Default Settings
    import os
import psycopg2
from flask import Flask, render_template
#urlparse libraryt won\'t work in python 2.7, we need to download urlparse2 and append urlparse library
import urlparse2
#Appending urlparse library from urlparse2
from urlparse2 import urlparse
import logging
from logging.handlers import RotatingFileHandler
from flask import request

url = urlparse2.urlparse(\'xxxxxxxxxxxxxxxx(postgres DB 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 home():
    #return \'Hello World!\'
    return render_template(\'home.html\')

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

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

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

@app.route(\'/create_contact\', methods=[\'POST\',\'GET\'])
def create_contact():

    try:
        if request.method == \"POST\":
            first_name = request.form[\"first-name\"]
            last_name = request.form[\"last-name\"]
            email = request.form[\"email\"]

            app.logger.info(first_name)
            statement = \"insert into salesforce.contact(firstname, lastname, email) values (\'\" \\
                + first_name + \"\',\'\" + last_name + \"\',\'\" + email + \"\');\"
            cur.execute(statement)
            conn.commit()
            errors = []
            return render_template(\'result.html\', errors=errors, firstname=first_name,
                                   lastname=last_name)
    except Exception as e:
        print(e)
        return []

    
if __name__ == \'__main__\':
    handler = RotatingFileHandler(\'foo.log\', maxBytes=10000, backupCount=10)
    handler.setLevel(logging.INFO)
    app.logger.addHandler(handler)
    app.run()

Results:

Click on app.py and run the app.

\"runtheporgm.PNG\"

Open the following URL http://localhost:5000/contacts or the URL which you\’ll get while running program (Viz: http://127.0.0.1:5000/) you should be able see the contacts.

\"herliuflast.PNG\"

Click on ContactList

\"contactlist.PNG\"

Click on Create contact

\"createcontact.PNG\"

Cheers!!

Oh hi there 👋
It’s nice to meet you.

Sign up to receive awesome content in your inbox, every month.

We don’t spam! Please check your mail for confirmation.

Leave a Comment

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

Select Language »