Sqlalchemy with a pgpass file

Question

Normally an SQLalchemy connection to postgres is something like this:

postgresql://scott:tiger@localhost/mydatabase

I was wondering how, ideally in flask, you could connect SQLalchemy to a file containing the password, such as the pgpass file.

Is such as thing possible?

Answer 1

.pgpass files have records in the form:

host:port:database:user:password

If you knew you had exactly one entry in your .pgpass file, you could read in the values like this:

from os.path import expanduser

with open(expanduser('~/.pgpass'), 'r') as f:
    host, port, database, user, password = f.read().split(':')

database_uri = 'postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port, database)

Sometimes the database name might be listed as * (meaning all) in the file though and then you would have to specify the database explicitly. Also, if you have multiple records, you can read them all in using f.readlines() and select the one you need based on your personal criteria.