Reputation: 9461
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?
Upvotes: 19
Views: 43977
Reputation: 9
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)
Upvotes: 0
Reputation: 9461
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'
)
Upvotes: 32