209

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)
davidism
  • 121,510
  • 29
  • 395
  • 339
Maharsh Gheewala
  • 2,091
  • 2
  • 10
  • 6

8 Answers8

377

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

davidism
  • 121,510
  • 29
  • 395
  • 339
Leandro Lima
  • 4,363
  • 1
  • 13
  • 17
  • 1
    "but has removed support for the postgres name" Do you know when? – Dorian Turba Aug 09 '22 at 07:56
  • 2
    How many thousands of apps did they break for no reason? Ridiculous. https://en.wikipedia.org/wiki/Robustness_principle – Dogweather Aug 25 '22 at 20:59
  • 4
    "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 https://github.com/sqlalchemy/sqlalchemy/issues/6083#issuecomment-801478013 There was over 10 years of support for a deprecated command. – Jonathan Vanasco Aug 25 '22 at 22:10
  • Thanks, this worked! My URL was starting from `postgressql`. – Rahul Chouhan Oct 27 '22 at 14:01
140

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.

davidism
  • 121,510
  • 29
  • 395
  • 339
Gitau Harrison
  • 3,179
  • 1
  • 19
  • 23
  • 6
    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` –  Apr 01 '21 at 19:37
  • @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 – Gitau Harrison Apr 03 '21 at 15:18
  • 12
    @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://')`. – Dijkie85 Apr 10 '21 at 19:38
  • 25
    Thanks guys! i actually ended up contacting heroku & they gave me this doc which solved the problem (https://help.heroku.com/ZKNTJQSK/why-is-sqlalchemy-1-4-x-not-connecting-to-heroku-postgres). Code: `uri = os.getenv("DATABASE_URL") # or other relevant config var if uri.startswith("postgres://"): uri = uri.replace("postgres://", "postgresql://", 1)` – Daniyal dehleh Apr 11 '21 at 20:05
  • 2
    After adding the code above and trying to run app.py, the follow error is outputed: AttributeError: 'NoneType' object has no attribute 'replace' – minTwin Sep 24 '21 at 12:56
  • Do they make changes like this just to annoy us? – Stonecraft Apr 11 '22 at 12:17
  • 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. – Olney1 Aug 19 '22 at 10:54
  • @Stonecraft "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. – bfontaine Jul 03 '23 at 09:28
27

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

davidism
  • 121,510
  • 29
  • 395
  • 339
5

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

Souvenir MEGNIGBETO
  • 61
  • 1
  • 2
  • 2
    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. – snakecharmerb Apr 16 '22 at 12:16
  • 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 https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.psycopg2, i resolved the issue – Souvenir MEGNIGBETO Apr 17 '22 at 08:04
  • psycopg2 is SQLAlchemy's [default driver](https://docs.sqlalchemy.org/en/14/core/engines.html#postgresql) 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. – snakecharmerb Apr 17 '22 at 08:33
  • This answer resolved my issue. I'm using PyCharm with sqlalchemy/postgresql. – James Jul 19 '23 at 15:30
2
# 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.

Kaitlin Berryman
  • 31
  • 2
2

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.

Alexander Vilnin
  • 183
  • 2
  • 10
1

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
DgrinderHZ
  • 84
  • 4
-2

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.

Yasser Yahia
  • 1
  • 1