Connect POSTGRES to python inside the virtual box

[LTMico]

How to?

[znmeb]

Looks like this will fail in the VirtualBox / Vagrant box. It is failing on my workstation in Jupyter:

import psycopg2
conn = psycopg2.connect("dbname=znmeb user=znmeb")
<ipython-input-4-d2de8feff34d> in <module>()
----> 1 conn = psycopg2.connect("dbname=znmeb user=znmeb")

/home/znmeb/miniconda3/envs/data-science/lib/python3.5/site-packages/psycopg2/__init__.py in connect(dsn, database, user, password, host, port, connection_factory, cursor_factory, async, **kwargs)
    162                 for (k, v) in items])
    163 
--> 164     conn = _connect(dsn, connection_factory=connection_factory, async=async)
    165     if cursor_factory is not None:
    166         conn.cursor_factory = cursor_factory

OperationalError: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Yes, said server is up and running - I'm connected to it with pgadmin3. I think it may be an authentication issue - a lot of things that connect to PostgreSQL refuse to risk a passwordless connect. I need to set passworded / localhost connections up anyhow for Windows - I'll try to get this running on Windows tomorrow and then backport it to Linux.

[znmeb]

Source for test case: http://initd.org/psycopg/docs/usage.html#

[znmeb]

I've verified that it works on Linux if

1. The user has a password set.
2. You set the host=localhost
3. You supply the password (sadly, in plaintext!) on the call to psycopg2.connect

That's not very secure if you do it in a Jupyter notebook or a Python script in GitHub ... looking at some other options.

[znmeb]

And here it is - ~/.pgpass!!

https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html

[znmeb]

OK ... this is done! See https://github.com/hackoregon/getting-started/blob/master/datascience/linux-laptop-setup/Using_the_VirtualBox_OVA.md#obtaining-and-configuring-the-base-box for the instructions.

If you still have the OVA from last night you don't need to download it again. Just start from the git checkout master step to get the latest code with the configure-postgresql script. There's a test notebook you can run to verify that it's working.