sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres when using flask to access Heroku postgres database

Question

I've seen similar questions asked and the answer is typically to simply change "postgres" to "postgresql" in the database URL. However, I still see this error.

In app.py:

app = Flask(__name__)
db = setup_db(app)

def get_times():
  date=request.get_json()[:10]
  raw_date_data = OpenAppointments.query.filter_by(date=date).first()
  ...

In models.py:

from decouple import config
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()
def setup_db(app):
    app.config["SQLALCHEMY_DATABASE_URI"] = config('DATABASE_URL')
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.app = app
    db.init_app(app)
    return db

class OpenAppointments(db.Model):
  __tablename__ = 'open_appointments'
  date = db.Column(db.Text, primary_key=True)
  time_1 = db.Column(db.Text, unique=True, nullable=False)
  time_2 = db.Column(db.Text, unique=True, nullable=False)
  time_3 = db.Column(db.Text, unique=True, nullable=False)
  time_4 = db.Column(db.Text, unique=True, nullable=False)
  time_5 = db.Column(db.Text, unique=True, nullable=False)
  time_6 = db.Column(db.Text, unique=True, nullable=False)

  def __init__(self, date, time_1, time_2, time_3, time_4, time_5, time_6):
    self.date = date
    self.time_1 = time_1
    self.time_2 = time_2
    self.time_3 = time_3
    self.time_4 = time_4
    self.time_5 = time_5
    self.time_6 = time_6

  def insert(self):
      db.session.add(self)
      db.session.commit()
  def delete(self):
      db.session.delete(self)
      db.session.commit()
  def update(self):
      db.session.commit()

And finally, the .env:

DATABASE_URL = postgresql://iuifkgiqsykklf:xxxxxxxxxxxxxxdb5a6125844793db8dc1623f43dce7de6889eb256@ec2-54-156-110-139.compute-1.amazonaws.com:5432/dfbcrvv8dikopj

UPDATE:

I've updated the imports on models.py. Also, I tried importing os and using os.environ.get instead of config but I get the same error.

UPDATE 2:

I should mention I did not have python-dotenv installed but when I did install it I got the same error. Here is the updated code for models.py:

import os
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def setup_db(app):
    app.config["SQLALCHEMY_DATABASE_URI"] = os.environ.get('DATABASE_URL')
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.app = app
    db.init_app(app)
    return db

class OpenAppointments(db.Model):
  __tablename__ = 'open_appointments'
  date = db.Column(db.Text, primary_key=True)
  time_1 = db.Column(db.Text, unique=True, nullable=False)
  time_2 = db.Column(db.Text, unique=True, nullable=False)
  time_3 = db.Column(db.Text, unique=True, nullable=False)
  time_4 = db.Column(db.Text, unique=True, nullable=False)
  time_5 = db.Column(db.Text, unique=True, nullable=False)
  time_6 = db.Column(db.Text, unique=True, nullable=False)

  def __init__(self, date, time_1, time_2, time_3, time_4, time_5, time_6):
    self.date = date
    self.time_1 = time_1
    self.time_2 = time_2
    self.time_3 = time_3
    self.time_4 = time_4
    self.time_5 = time_5
    self.time_6 = time_6

  def insert(self):
      db.session.add(self)
      db.session.commit()
  def delete(self):
      db.session.delete(self)
      db.session.commit()
  def update(self):
      db.session.commit()

When I print out the variable for both os and config, for some reason it drops the "ql":

print(os.environ.get('DATABASE_URL'))
print(config('DATABASE_URL'))

yields:

2022-02-07T17:36:36.227545+00:00 app[web.1]: postgres://iuifkgiqsykklf:xxxxxxxxxxxxxxxxd40a28adb5a6125844793db8dc1623f43dce7de6889eb256@ec2-54-156-110-139.compute-1.amazonaws.com:5432/dfbcrvv8dikopj
2022-02-07T17:36:36.227840+00:00 app[web.1]: postgres://iuifkgiqsykklf:xxxxxxxxxxxxxxxxd40a28adb5a6125844793db8dc1623f43dce7de6889eb256@ec2-54-156-110-139.compute-1.amazonaws.com:5432/dfbcrvv8dikopj

Answer 1

DATABASE_URL is a "system" environment variable provided by Heroku, it overrides the one in your .env. You need to replace postgres by postgresql programmatically.