Connecting Heroku Database to SQLAlchemy

Question

so as part of a Web Programming MOOC, I have to connect to a PostgreSQL Database hosted on Heroku using Python and SQLalchemy. I have spent a lot of hours trying to do this to no avail. My main problem is that I'm not being able to connect to the Database, because every time a try to run a script to update or just check the Database I get "Is the server running on host "ec2-174-129-35-61.compute-1.amazonaws.com" (174.129.35.61) and accepting TCP/IP connections on port 5432?" in the command prompt.

I've done a lot of research trying to figure it out and it seems that maybe the URI of my Database is not set to accept public connections. But I tried setting ssl = require and still haven't been able to solve the problem. Maybe I didn't do it right?

I have come to think that maybe I am doing something wrong with the code, or that I should try to connect through Heroku's CLI and not the command prompt but I am not sure. This is my simple test code:

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

if not os.getenv("DATABASE_URL"):
    raise RuntimeError("DATABASE_URL is not set")

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

db.execute("INSERT INTO books VALUES (1, 1, 1, 1)")
db.commit()

I set the DATABASE_URL environment variable in the command prompt to the Database's URI but maybe I did it wrong? I also tried just using the URI in the code itself but still could not make it work.

This is my first time using Databases with Heroku so I am complete neewbie, maybe I am doing something else wrong?. I appreciate all the help that you guys can give. Thanks in advance

Answer 1

1) Check, did you copied all link, including postgres://

2) Maybe you had made a mistake during setting DATABASE_URL. So, try to add your URL directly to the script:

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

engine = "postgres://ennjrjkrfsb____all_code_of_key_.....___compute.amazonaws.com:5432/ddo7541ka3hio7"
db = scoped_session(sessionmaker(bind=engine))
db.execute("INSERT INTO books VALUES (1, 1, 1, 1)")
db.commit()

3) if you are using linux, try to open your database, using command psql postgres://ennjrjkrfsb____all_code_of_key_.....___compute.amazonaws.com:5432/ddo7541ka3hio7

4) Try to access to your database in any way via other internet provider (may be your provider or sys admin in your office restricted access to Heroku)

Update: Try to use free online IDE with interpreter (based on Linux), for example, https://cs50.io - from this site you can access to Heroku postgres

Answer 2

postgres dialect does not support any more in SQLAlchemy v1.4.x above, and it turns into postgresql, so the final connection string must be postgresql://username:password@host:port/database

Here is my working connection from SQLAlchemy to Heroku Postgresql

database_url = os.environ('DATABASE_URL')

if database_url.startswith('postgres://'):
    database_url.replace('postgres://', 'postgresql://')

engine = create_engine(database_url)

# rest of your codes...

here is my reference Heroku Issue