I'm trying to connect to a Postgres database with SQLAlchemy. I've installed psycopg2. However, I get the error sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres. How do I configure SQLAlchemy to connect to PostgreSQL?
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "postgres://username@localhost:5432/template1"
db = SQLAlchemy(app)
The URI should start with postgresql:// instead of postgres://. SQLAlchemy used to accept both, but has removed support for the postgres name.
SQLAlchemy 1.4 removed the deprecated postgres dialect name, the name postgresql must be used instead now. The dialect is the part before the :// in the URL. SQLAlchemy 1.3 and earlier showed a deprecation warning but still accepted it.
To fix this, rename postgres:// in the URL to postgresql://.
This error currently shows up when working with Heroku, which uses postgres in the DATABASE_URL they provide, which you probably use for SQLALCHEMY_DATABASE_URI. To work around this until they update it, update the variable in the Heroku dashboard to use postgresql.
The problem in heroku have been resolved by using simple python url replace code
import os
import re
uri = os.getenv("DATABASE_URL") # or other relevant config var
if uri and uri.startswith("postgres://"):
uri = uri.replace("postgres://", "postgresql://", 1)
# rest of connection code using the connection string `uri`
source : https://help.heroku.com/ZKNTJQSK/why-is-sqlalchemy-1-4-x-not-connecting-to-heroku-postgres
# production or dev DB
try:
prodURI = os.getenv('DATABASE_URL')
prodURI = prodURI.replace("postgres://", "postgresql://")
app.config['SQLALCHEMY_DATABASE_URI'] = prodURI
except:
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql:///MYDATABASE'
I tried all the above answers, but only this worked for me. I followed Heroku's official suggestion (mentioned in previous comments), but no luck. I think taking off the 1 at the end of replace() may have helped.
As @Kaitlin Berryman said the Heroku official answer is not working, to avoid putting your DB credentials add this before db.create_all():
# create the database and the db table
import os
import re
uri = os.getenv("DATABASE_URL")
# or other relevant config var
if uri.startswith("postgres://"):
uri = uri.replace("postgres://", "postgresql://", 1)
app.config['SQLALCHEMY_DATABASE_URI'] = uri
after reading @gitau-harrison answer and knowing that it is a SQLAlchemy issue, instead of updating the variable in the Heroku dashboard to use postgresql, I changed the version of SQLAlchemy in 'requirements.txt' to 1.3.7 (was 1.4.18) and this fixed the issue for me.