How to connect to posgres in Heroku from Python?-CodePudding

I have followed the documentation and in my Python code I've added:

import os
import psycopg2

DATABASE_URL = os.environ['postgres://sficoXXX:[email protected]:5432/database-name']

conn = psycopg2.connect(DATABASE_URL, sslmode='require')

The URL I took from the session Config Vars (I used: heroku pg:credentials:url).

When I check the connection to the database using heroku pg:psql everything seems to be working fine.

But after deploying it shows the following error:

Failed to create session: 'postgres://sficoXXX:[email protected]:5432/database-name' Traceback (most recent call last): File "/Users/spoleto/.pyenv/versions/3.8.13/lib/python3.8/site-packages/otree/session.py", line 447, in create_session_traceback_wrapper return create_session(**kwargs) File "/Users/spoleto/.pyenv/versions/3.8.13/lib/python3.8/site-packages/otree/session.py", line 418, in create_session func(subsession) File "/Users/spoleto/PycharmProjects/upstream-reciprocity/prototypes/Consent/init.py", line 35, in creating_session DATABASE_URL = os.environ[postgres://sficoXXX:[email protected]:5432/database-name'] File "/Users/spoleto/.pyenv/versions/3.8.13/lib/python3.8/os.py", line 675, in getitem raise KeyError(key) from None

Am I doing this right? Where does the error come from? How is the connection supposed to be established?

This is almost a copy/paste from the documentation.

CodePudding user response：

The whole point of putting a connection string into an environment variable is so it doesn't need to be in your source code.

Instead of looking up the value of DATABASE_URL manually and pasting it into your source code, use the name of the environment variable:

DATABASE_URL = os.environ['DATABASE_URL']

Now your code will look for an environment variable with that name dynamically every time it runs, and set your DATABASE_URL variable to the value of the DATABASE_URL environment variable.

(The code you show in your question looks for an environment variable named postgres://..., which is very unlikely to exist.)

Note that this will fail with an IndexError if an environment variable named DATABASE_URL cannot be found. A safer way of doing this might be to use the .get() method (make sure to use round parentheses instead of square brackets):

DATABASE_URL = os.environ.get('DATABASE_URL')

Now you can even provide a fallback, e.g. for local development:

DATABASE_URL = os.environ.get('DATABASE_URL') or "postgres://postgres:postgres@localhost:5432/postgres"

Note: You didn't leak your whole connection string in your question, but I suggest you rotate your credentials anyway:

heroku pg:credentials:rotate

This will invalidate the old connection string and generate a new one.

The good news is that your DATABASE_URL environment variable will automatically be updated, and since your code now reads that value at runtime, it will continue to work!