sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres

Question

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)

Davy

Answer 1

The URI should start with postgresql:// instead of postgres://. SQLAlchemy used to accept both, but has removed support for the postgres name.

Survive

"but has removed support for the postgres name" Do you know when? – Wellordered

How many thousands of apps did they break for no reason? Ridiculous. en.wikipedia.org/wiki/Robustness_principle – Orwin

"postgres" was deprecated in 2009, and SQLAlchemy had emitted deprecation warnings from v0.6 to v1.3. It was finally removed in v1.4 in 2021. See github.com/sqlalchemy/sqlalchemy/issues/… There was over 10 years of support for a deprecated command. – Reconnoitre

Thanks, this worked! My URL was starting from postgressql. – Cuenca

Answer 2

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.

Dudleyduds

if by variables you mean config variables, I tried doing so & keep on getting Item could not be updated: Cannot overwrite attachment values DATABASE_URL – Groundsill

@Daniyaldehleh I cannot really tell what the real issue is because you do not give me enough information about what exactly you did. Kindly let me know what exactly you tried and I may help – Dudleyduds

@Daniyaldehleh I worked around with a simple replace method in your Python script, and not in Heroku dashboard, since that won't work, as you mentioned. So you could do: SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL?sslmode=require').replace('postgres://', 'postgresql://'). – Bim

Thanks guys! i actually ended up contacting heroku & they gave me this doc which solved the problem (help.heroku.com/ZKNTJQSK/…). Code: uri = os.getenv("DATABASE_URL") # or other relevant config var if uri.startswith("postgres://"): uri = uri.replace("postgres://", "postgresql://", 1) – Crinkly

After adding the code above and trying to run app.py, the follow error is outputed: AttributeError: 'NoneType' object has no attribute 'replace' – Fabianfabianism

Do they make changes like this just to annoy us? – Gearwheel

This answer is still applicable today. You'll also find that you cannot simply edit the Config Vars in your Heroku dashboard with the postgresql:// change to the URL (which would be more secure) so I had to hard code the URL. – Klemm

@Gearwheel "postgres" has never been a valid dialect and has been deprecated for 11 years before being removed from SQLAlchemy. In other words, you have had 11 years of warnings telling you to stop using it and switch to "postgresql". If you’re still annoyed by that, then don’t use SQLAlchemy 1.4. – Brutus

Answer 3

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

Phia

Answer 4

To fix this, rename postgres:// in the URL to postgresql+psycopg2://.

Annabell

Converting the dialect name from postgres to postgresql is covered by the existing answers. Specifying the driver does no harm, but it isn't relevant to the question. – Austin

I've got this error ''sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql'' when i was dealing with the existing solution but when i try to specify the driver as recommend here docs.sqlalchemy.org/en/14/dialects/…, i resolved the issue – Annabell

psycopg2 is SQLAlchemy's default driver for Postgresql; create_engine('postgresql://...) will use it automatically if it's installed and no driver is specified. Perhaps there was a typo in your code and rewriting the statement fixed it. – Austin

This answer resolved my issue. I'm using PyCharm with sqlalchemy/postgresql. – Hyohyoid

this one worked for me , i'm using alembic with sqlalchemy inside an alpine docker container – Packthread

Answer 5

All recipies I found tell the same "use postresql instead of postgres". But it does't work for me since my DB prefix is already ok "postresql".

After some survey I managed to find the problem. The thing is "alembic.ini" contains general template for db url sqlalchemy.url = driver://user:pass@localhost/dbname Use sqlalchemy.url = postgresql://user:pass@localhost/dbname instead. It works.

Overstay

Answer 6

# 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.

Oudh

Answer 7

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

Adductor

Answer 8

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.

Involute

...but now you're using an old version of SQLAlchemy. Wouldn't it be better to use a current versoin? – Until