Connecting to remote PostgreSQL database with SQLAlchemy using SSH tunneling with public key and passphrase, all that from a Windows machine

Question

So, I am using Windows to connect to remote Postgres database with a tool like Navicat. I am trying to achieve the same thing using Python (2.7) and SQLAlchemy (0.9) but with no success.

So, my Navicat setup looks like this:

enter image description here

I tried to set up tunnels in PuTTY, connect to server, leave connection opened and experimenting with IPs and ports but with no success at all. I'm not sure if all that's enough since I have this public key and passphrase, so I wonder is it possible to connect to this database from Windows machine using Python and SQLAlchemy (and this beautiful PuTTY app also, of course) in any way?
Thanks!

user590028 · Accepted Answer · 2014-08-07 16:52:21Z

9

You can pass the server and port to your create_engine() statement, ala:

create_engine('postgres://username:password@server:port/database')

http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html

So assuming you've FIRST established an SSH tunnel to the remote host, and port forwarded local port 1111 to the remote server's port 5432 (which is the default postgres port), your connect string would be something like:

create_engine('postgres://username:password@localhost:1111/database')

answered Aug 7, 2014 at 16:52

user590028

11.8k5 gold badges43 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

errata Over a year ago

Thanks! I was always trying to connect to this first IP (from 'general' tab) instead of 127.0.0.1... Works like a charm now :)

TD1 Over a year ago

Note that the correct command is now: create_engine('postgresql://username:password@localhost:1111/database')

Collectives™ on Stack Overflow

Connecting to remote PostgreSQL database with SQLAlchemy using SSH tunneling with public key and passphrase, all that from a Windows machine

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related