0

I am currently connecting to a PostgreSQL DB using Dbeaver.

However I want to connect to DB directly with Pyhton, so that I can automatize the data flow.

I have the following information field given (Just giving the headers here):

SSH:

  • Host/IP:
  • User Name:
  • Authentication Method: Public Key
  • Private Key: (have a file)

Main PostgreSQL DB connection settings:

  • Host: [....]amazonaws.com
  • Database:
  • Authentication: Database Native
  • Username:
  • Password:

I have only READ permission.

How to connect setup connection to DB using python?

I have tried with pyodbc, but am getting an interface error

import pyodbc
server = '[...] amazonaws.com' 
database = 'something' 
username = 'user_name' 
password = 'xyz' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

OUT:

--> 17 cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)
     18 cursor = cnxn.cursor()

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Leo
  • 1,176
  • 1
  • 13
  • 33
  • What have you tried? There are several good Python packages for Postgres, including `psycopg2`. If you need an ssh tunnel, that will have to be separate. – Tim Roberts Nov 18 '22 at 23:49
  • @TimRoberts I think my confusion is how I enter Parameters to connect. I have insalled pyodbc, but have struggled to get further. – Leo Nov 19 '22 at 00:01
  • Ok so SSH tunnel needs to be separate, with a different package – Leo Nov 19 '22 at 00:01
  • You're not thinking about what you're doing. You are trying to open a connection to SQLServer, but you said you were using Postgres. If you intend to use `pyodbc`, then you need to read about connection strings for Postgres. – Tim Roberts Nov 19 '22 at 01:36

0 Answers0