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
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)
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With