Connect to AWS RDS Postgres database with python

Question

I have an existing postgres table in RDS with a database name my-rds-table-name

I've connected to it using pgAdmin4 with the following configs of a read-only user:

host_name = "my-rds-table-name.123456.us-east-1.rds.amazonaws.com"
user_name = "my_user_name"
password = "abc123def345"

I have verified that I can query against the table.

However, I cannot connect to it using python:

SQLAlchemy==1.2.16
psycopg2-binary==2.7.6.1
mysqlclient==1.4.1

With:

import psycopg2
engine = psycopg2.connect(
    database="my-rds-table-name",
    user="my_user_name",
    password="abc123def345",
    host="my-rds-table-name.123456.us-east-1.rds.amazonaws.com",
    port='5432'
)

It fails with

psycopg2.OperationalError: FATAL:  database "my-rds-table-name" does not exist

Similarly, if I try to connect to it with sqlalchemy:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  database "my-rds-table-name" does not exist

What am I missing?

my-rds-table-name is the name of the database instance (server). Within the database instance, there is a logical database, which does not have the name my-rds-table-name. You can use the RDS console to discover the actual name of the database. — John Rotenstein
– John Rotenstein, Commented Jan 22, 2019 at 4:00

Roman · Accepted Answer · 2019-01-22 17:54:44Z

32

Thank's John Rotenstein for your comment.

As he pointed out, my-rds-table-name is the database instance name, not the database name, the default database name is postgres.

import psycopg2
engine = psycopg2.connect(
    database="postgres",
    user="my_user_name",
    password="abc123def345",
    host="my-rds-table-name.123456.us-east-1.rds.amazonaws.com",
    port='5432'
)

answered Jan 22, 2019 at 17:54

Roman

9,50111 gold badges72 silver badges113 bronze badges

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

Comments

andGarc · Accepted Answer · 2021-05-09 13:25:27Z

0

Using sqlalchemy you can do the following:

engine = create_engine('postgresql://postgres:postgres@<AWS_RDS_end-point>:5432/postgres')

Then you can update your database. For example:

df = pd.DataFrame({'A': [1,2], 'B':[3,4]})
df.to_sql('tablename', engine, schema='public', if_exists='append', index=False)

answered May 9, 2021 at 13:25

andGarc

91 bronze badge

Collectives™ on Stack Overflow

Connect to AWS RDS Postgres database with python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related