Search code examples
python-3.xsqlalchemyjupyter-notebookpsycopg2

connect to DB from nb: could not translate host name to address error

I'm having trouble connecting to a DB from a jupyter nb on a mac. psycopg2 complains about an OperationalError. My code:

import sqlalchemy as db

# build connection string
connection_string = f"postgresql://{db_cred['username']}:{db_cred['password']}@{db_cred['host']}/{db_cred['database']}"

# create engine
engine = db.create_engine(connection_string)

# connect
conn = engine.connect()

returns

OperationalError: (psycopg2.OperationalError) could not translate host name "db.production.info" to address: nodename nor servname provided, or not known

Needless to say, the DB credentials are correct. The documentation on these errors isn't very helpful, and most proposed solutions revolve around Docker, which I'm not using in this case.

The only exception was this thread, which proposes using the IP address instead of the host name. I did that, and get the following different error:

OperationalError: (psycopg2.OperationalError) connection to server at "192.XXX.XX.XX", port XXXX failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?

Any idea what am I doing wrong?

Solution

  • So the problem turned out to be that an SSH tunnel was necessary.

    From a shell script, execute the command:

    ssh -L 9999:db.production.info:[port number] -N [username]@192.XXX.XX.XX

    this will tunnel db.production.info:[port number] to your localhost on port 9999.

    You can then adapt the connection string as

    'postgresql://username:password@localhost:9999/[the DB name]'