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)')
psycopg2. If you need an ssh tunnel, that will have to be separate.pyodbc, then you need to read about connection strings for Postgres.