What is the form of my local postgresql database url?

Question

I am going through a flask/sqlalchemy tutorial https://pythonhosted.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application and I configured my database url to be: postgres://username:password@localhost:5432/dbname

when I run db.create_all() in the interactive python shell it doesn't throw any errors, but it doesn't do anything either.

From what I understand it is supposed to create the User table with three columns; id, username, and email .

from flask import Flask, url_for, render_template 
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://username:password@localhost:5432/dbname'

db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

app.debug = True



@app.route("/")
def hello():
    return render_template('hello.html')

if __name__ == "__main__":
    app.run()

Br

Which driver are you using? The driver isn't always optional. – Maternity

@SpencerCooley Can you post your psql query, before that Have you done create_db() – Pyridine

docs.sqlalchemy.org/en/13/core/engines.html#database-urls – Basia

Answer 1

It should be the exact format.

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://username:password@localhost:port/DBNAME"

Pyridine

Although 'postgresql:///DBNAME' works if you are connecting locally over a Unix domain socket. – Drum

Answer 2

Once you install PostgreSQL you can do this:

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://<username>:<password>@localhost/<database_name>"

Where:

• <username> is your username (by default postgres)
• <password> is your password (by default postgres)
• <database_name> the name of your database (by deafault postgres if you are using the DB created once you installed PostgreSQL).

If you have created a role (a user) for example admin with password admin and a database named posts then you can do this:

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://admin:admin@localhost/posts"

more info here https://flask-sqlalchemy.palletsprojects.com/en/2.x/config/

Polyclinic

Answer 3

Try using postgresql in the URL instead of postgres.

That's the form that's used both for JDBC and in the SQLAlchemy doc (http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html).

Diageotropism

Yes, I did try that, but then I go in to the psql console and check to see if any new tables were created and don't see anything. – Br

Hmm... so you can connect with those same params via psql... Anything in the postgres log? – Diageotropism

need to check that. I am using postgres.app on mac os, not sure if that makes some sort of difference. Just found this link codeinthehole.com/writing/configuring-logging-for-postgresapp – Br

Ah. Yeah it looks like from that link logging isn't enabled by default. I use Postgres on Ubuntu, usually compiled from source, so I'm not familiar with postgres.app specifically. It essentially seems to be a boxed-up version of Postgres with a few add-ons. – Diageotropism

Spencer Cooley - you have to commit in python to make the changes really happen (autocommit should be off by default) – Kilometer

As of v1.4.0b postgresql[+driver]://... is required. The postgres[+driver]://... form is no longer supported by SQLAlchemy. – Drum

Answer 4

I faced the same issue when I ran celery with the Postgres backend URL and solved it with the below steps!

pip install psycopg2-binary

Added Celery backend URL like below

db+postgresql://postgres:1234@localhost/mydb

Shir