CODEWITHSUNDEEP
pythonpostgresqlgoogle-cloud-platformsqlalchemy
Riley Hun
Riley Hun

Reputation: 2775

Having Trouble Connecting to Cloud SQL (PostgreSQL) using Python's SQLALCHEMY

I set up the Cloud SQL instance on Google Cloud Platform and followed the official instructions, but don't seem to be able to connect to the Cloud SQL instance. When I try to do a sanity check and access the PostgreSQL db through Cloud Shell, I'm able to connect successfully though.

Could someone please help - I would be much obliged.

Code:

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://<user>:<pass>@<public IP Address/<table>')

engine.connect()

Error:

Is the server running on host "XX.XX.XXX.XX" and accepting
TCP/IP connections on port XXXX?

Upvotes: 3

Views: 6273

Answers (4)

Jos&#233; Luis Chiquete
Jos&#233; Luis Chiquete

Reputation: 21

This worked to me using the Cloud SQL Proxy on my personal computer and uploading the code to Google App Engine standard.

db_user = os.environ.get('CLOUD_SQL_USERNAME')
db_pass = os.environ.get('CLOUD_SQL_PASSWORD')
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')
if os.environ.get('GAE_ENV') == 'standard':
    db_uri = f'postgresql+psycopg2://{db_user}:{db_pass}@/{db_name}?host=/cloudsql/{db_connection_name}'
else:
    db_uri = f'postgresql+psycopg2://{db_user}:{db_pass}@127.0.0.1:1234/{db_name}'
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = db_uri
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

Upvotes: 2

alexanderdavide
alexanderdavide

Reputation: 1675

Depending on the database client library, the socket (/cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432) needs to be specified.

The docs have this example for SQLAlchemy:

db_user = os.environ["DB_USER"]
db_pass = os.environ["DB_PASS"]
db_name = os.environ["DB_NAME"]
db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")
cloud_sql_connection_name = os.environ["CLOUD_SQL_CONNECTION_NAME"]

pool = sqlalchemy.create_engine(

    # Equivalent URL:
    # postgresql+pg8000://<db_user>:<db_pass>@/<db_name>
    #                         ?unix_sock=<socket_path>/<cloud_sql_instance_name>/.s.PGSQL.5432
    sqlalchemy.engine.url.URL.create(
        drivername="postgresql+pg8000",
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        database=db_name,  # e.g. "my-database-name"
        query={
            "unix_sock": "{}/{}/.s.PGSQL.5432".format(
                db_socket_dir,  # e.g. "/cloudsql"
                cloud_sql_connection_name)  # i.e "<PROJECT-NAME>:<INSTANCE-REGION>:<INSTANCE-NAME>"
        }
    ),
    **db_config
)

Be aware that this example is with pg8000 that uses unix_sock instead of unix_socket as socket identifier.

Upvotes: 0

David LiCause
David LiCause

Reputation: 31

I found another way to connect to a PostgreSQL GCP instance without using the Cloud SQL Proxy.

Code: 

import sqlalchemy

username = ''  # DB username
password = ''  # DB password
host = ''  # Public IP address for your instance
port = '5432'
database = ''  # Name of database ('postgres' by default)

db_url = 'postgresql+psycopg2://{}:{}@{}:{}/{}'.format(
    username, password, host, port, database)

engine = sqlalchemy.create_engine(db_url)

conn = engine.connect()

I whitelisted my IP address before trying to connect. (https://cloud.google.com/sql/docs/postgres/connect-external-app#appaccessIP)

Upvotes: 3

Daniel Ocando
Daniel Ocando

Reputation: 3764

Use the Cloud SQL proxy to connect to Cloud SQL from external applications.

In order to achieve this please follow the relevant documentation.

The steps described would consist of:

  1. Enabling the Cloud SQL Admin API on your Cloud Console.
  2. Installing the relevant proxy client according to your OS.
  3. Use any of the available methods to authenticate the Cloud SQL Proxy.
  4. Invoke the proxy with ./cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:5432 & ond your terminal and connect the proxy by changing your code and using SQLALCHEMY:
from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://DATABASE_USER:PASSWORD@localhost:5432/')

NOTE: the code above assumes you are not trying to connect to the proxy in a production environment and are using an authenticated Cloud SDK client in order to connect to the proxy.

Upvotes: 2

Related Questions