Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Connecting psycopg2 with Python in Heroku

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

like image 901
J0ANMM Avatar asked Jan 23 '17 17:01

J0ANMM


2 Answers

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)
like image 147
J0ANMM Avatar answered Oct 07 '22 23:10

J0ANMM


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()
like image 33
shehabeldin.21 Avatar answered Oct 08 '22 00:10

shehabeldin.21