CODEWITHSUNDEEP
python-3.xpostgresqlgoogle-cloud-platform
IMozes
IMozes

Reputation: 131

Connecting to postgresql from python 3, running in Cloud Shell: password authentication failed

I try to run locally (from GCP terminal) python 3 tutorial program to connect to my postgresql dsatabase.

I run proxy, as it is suggested in source:

./cloud_sql_proxy -instances=xxxxxxxx:us-central1:testpg=tcp:5432

it works, I can connect to it with:

psql "host=127.0.0.1 sslmode=disable dbname=guestbook user=postgres

Unfortunately when I try to connect from python:

   cnx = psycopg2.connect(dbname=db_name, user=db_user,
                           password=db_password, host=host)

host is 121.0.0.1 -as I run it locally, I get this error: psycopg2.OperationalError: connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "postgres"

I can't get around what I miss? Thanks in advance ...

Upvotes: 2

Views: 747

Answers (1)

Jack Wotherspoon
Jack Wotherspoon

Reputation: 1979

I'd recommend using the Cloud SQL Python Connector to manage your connections and best of all you won't need to worry about running the proxy manually. It supports the pg8000 postgresql driver and can run from Cloud Shell.

Here is an example code snippet showing how to use it:

from google.cloud.sql.connector import connector
import sqlalchemy

# configure Cloud SQL Python Connector properties
def getconn() ->:
    conn = connector.connect(
        "xxxxxxxx:us-central1:testpg",
        "pg8000",
        user="YOUR_USER",
        password="YOUR_PASSWORD",
        db="YOUR_DB"
    )
    return conn

# create connection pool to re-use connections
pool = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
)

# query or insert into Cloud SQL database
with pool.connect() as db_conn:
    # query database
    result = db_conn.execute("SELECT * from my_table").fetchall()

    # Do something with the results
    for row in result:
        print(row)

For more detailed examples refer to the README of the repository.

Upvotes: 1

Related Questions