sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: database does not exist

Question

I am trying to populate a database with two tables in SQLAlchemy. I have already created the database test_database and now I am trying to create 2 tables inside this. I have already checked that this database is successfully created using \l. Following is the code for the file create.py which creates a database with two tables:

import os

from flask import Flask, render_template, request
from models import *

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'postgresql://shammun:my_password@localhost:5432/test_database.db' 
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

# db = SQLAlchemy()
db.init_app(app)


def main():
    db.create_all()

if __name__ == "__main__":
    with app.app_context():
        main()

This file create.py imports model.py which generates two tables, the code of which is given below:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Flight(db.Model):
    __tablename__ = "flights"
    id = db.Column(db.Integer, primary_key=True)
    origin = db.Column(db.String, nullable=False)
    destination = db.Column(db.String, nullable=False)
    duration = db.Column(db.Integer, nullable=False)


class Passenger(db.Model):
    __tablename__ = "passengers"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)
    flight_id = db.Column(db.Integer, db.ForeignKey("flights.id"), nullable=False)

Now, in the terminal when I run the file create.py, I get the following error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: database "test_database.db" does not exist

Actually, this is almost the same question that I asked in this post db.create_all() doesn't create a database a month ago. The only difference is that I asked the wrong question that database was not created. Actually the question would be why the database wasn't found and why would it throw an error. As this question was closed and as I have tried so many times for a long time to resolve it and couldn't find any solution, I am asking almost the same question again. I will much appreciate if someone can help me to lift me from this bottleneck where I am stuck for a long time.

Answer 1

Check on what port is your postgres running using this command \conninfo cause I doubt your PostgreSQL database is running on some different port.

Default port of PostgreSQL is 5432, but if it is already occupied by some other application then it tries next empty port and starts running on 5433

So in your app config variable of SQLALCHEMY_DATABASE_URI, try changing 5432 to 5433 and see if it works.

Edit 1:

Try removing .db from your database name test_database.db, and just put test_database

Change this:

app.config["SQLALCHEMY_DATABASE_URI"] = 'postgresql://shammun:my_password@localhost:5432/test_database.db' 

To this:

app.config["SQLALCHEMY_DATABASE_URI"] = 'postgresql://shammun:my_password@localhost:5432/test_database'