Connecting psycopg2 with Python in Heroku

Question

I've been trying for some days to connect my python 3 script to PostgresSQL database(psycopg2) in Heroku, without Django.

I found some article and related questions, but I had to invest a lot of time to get something that I thought should be very straightforward, even for a newbie like me.

I eventually made it work somehow but hopefully posting the question (and answer) will help other people to achieve it faster.

Of course, if anybody has a better way, please share it.

---

As I said, I had a python script that I wanted to make it run from the cloud using Heroku. No Django involved (just a script/scraper).

Articles that I found helpful at the beginning, even if they were not enough:

• Running Python Background Jobs with Heroku

• Simple twitter-bot with Python, Tweepy and Heroku

Schexnayder

Answer 1

Main steps:

1. Procfile

Procfile has to be:

worker: python3 folder/subfolder/myscript.py

2. Heroku add-on

Add-on Heroku Postgres :: Database has to be added to the appropriate personal app in the heroku account.

To make sure this was properly set, this was quite helpful.

3. Python script with db connection

Finally, to create the connection in my python script myscript.py, I took this article as a reference and adapted it to Python 3:

import psycopg2
import urllib.parse as urlparse
import os

url = urlparse.urlparse(os.environ['DATABASE_URL'])
dbname = url.path[1:]
user = url.username
password = url.password
host = url.hostname
port = url.port

con = psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            host=host,
            port=port
            )

To create a new database, this SO question explains it. Key line is:

con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

Schexnayder

This article proposes an alternative way (I did not try it) – Schexnayder

Answer 2

You can do it using the SQLALCHEMY library. First, you need to install the SQLALCHEMY library using pip, if you don't have pip on your computer install, you will know-how using a simple google search

pip install sqlalchemy

Here is the code snippet that do what you want:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
import os

# Put your URL in an environment variable and connect.
engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

# Some variables you need.
var1 = 12
var2 = "Einstein"
# Execute statements
db.execute("SELECT id, username FROM users WHERE id=:id, username=:username"\
           ,{"id": var1, "username": var2}).fetchall()

# Don't forget to commit if you did an insertion,etc...
db.commit()

Barehanded

Answer 3

I wasn't able to parse the DATABASE_URL provided by Heroku with the urllib.parse as suggested above, but the following worked for me:

The URL I retrieved from Heroku was in the format:

postgres://username:password@host:port/database

for example:

postgres://jticiuimwernbk:ff78903549d4c6ec13a53a8ffefcd201b937d54c35d976 @ec2-52-123-182-987.compute-1.amazonaws.com:5432/dbsd4fdf6c1awq

So I manually dissected it as follows:

user = 'jticiuimwernbk'
password = 'ff78903549d4c6ec13a53a8ffefcd201b937d54c35d976'
host = 'ec2-52-123-182-987.compute-1.amazonaws.com'
port = '5432'
database = 'dbsd4fdf6c1awq'
    
#Then created the connection using the above:
    
con = psycopg2.connect(database=database,
user=user,
password=password,
host=host,
port=port)

# and now I was able to perform queries:

cur = conn.cursor()
results = cur.execute("<some SQL query>;").fetchall()
cur.close()
conn.close()

Dominoes