Connect to remote Postgres server via SQLAlchemy

1

I am trying to send some commands to a remote Postgres server using SQLAlchemy but each time I receive an error.

Please note that I can connect to the remote Postgres using SSH username and password to login to the server. For that I have used my local terminal, PuTTY and WinSCP so the problem appears to be in the Python code I have written

    # create postgres engine to connect to the database
    engine = create_engine('postgres://server_username:server_password@server_name:port/database')

    with engine.connect() as conn:
        ex = conn.execute("SELECT version();")
        conn.close() # not needed but keep just in case
        print(ex)

Running the code above yields the following error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SSL SYSCALL error: Connection reset by peer (0x00002746/10054)
expected authentication request from server, but received S

I have also tried adding the SSL verification parameter as follows

create_engine('postgres://server_username:server_password@server_name:port/database?sslmode=verify-full')

which returned the error

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) root certificate file "C:\Users\aris.pavlides\AppData\Roaming/postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

at which point I had nothing to lose so I disabled certificate verification altogether

create_engine('postgres://server_username:server_password@server_name:port/database?sslmode=disable')

which returned the initial error message.

Do you have any ideas on how I can modify the code to make it work?

python
postgresql
sqlalchemy
asked on Stack Overflow Sep 5, 2019 by agrajag_42 • edited Sep 5, 2019 by agrajag_42

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0