CODEWITHSUNDEEP
python-3.xpostgresqlgoogle-cloud-functionspsycopg2google-cloud-sql
Zama Ques
Zama Ques

Reputation: 1544

Python error while connecting to cloud sql from cloud functions

I am trying to use Python code to connect from GCP Cloud functions to PostgreSQL instance of CloudSQL . I am new to python , so struggling with some error and need help to fix the error

My code is as follows:

# Don't add the "/.s.PGSQL.5432 suffix" because it will already be added back automatically by the library...
import psycopg2
import sqlalchemy
from sqlalchemy import create_engine

engine = sqlalchemy.create_engine('postgresql+psycopg2://postgres:[email protected]/postgres_dbase')
print("connected")

The function is deployed fine but the URL is failing with below error

  Error: could not handle the request

It is showing below error in the log

  Details:'Request' object has no attribute '_instantiate_plugins'

It works fine when I execute the code directly

   $ python3 open.py
    connected

I am using Python 3.7

UPDATE

It looks like I have to set DATABASE_URL but not sure how to set it in CLoud functions . Added below Runtime environmental variable in Cloud functions but no luck

 DATABASE_URL="sqlite://"

https://github.com/sqlalchemy/sqlalchemy/issues/5330

Upvotes: 1

Views: 752

Answers (2)

Luis Vargas
Luis Vargas

Reputation: 381

This has worked for my connecting Google App Engine to PostgreSQL instance from Google Cloud Platform.

import psycopg2

try:
    conn = psycopg2.connect(dbname='your_data_base_name', user='your_data_base_user', password='your_data_base_user_password', host='/cloudsql/project_name:region:database_instance_name')

except psycopg2.Error as e:
    print("Error: Could not make connection to the Postgres database")
    print(e)
cur = conn.cursor()

Upvotes: 1

Samuel Romero
Samuel Romero

Reputation: 1263

Not sure if you are trying to connect from Public IP or from Private. However, here you can find an example about how to perform your concern using Public IP. Your code should looks like

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:
    # postgres+pg8000://<db_user>:<db_pass>@/<db_name>
    #                         ?unix_sock=<socket_path>/<cloud_sql_instance_name>/.s.PGSQL.5432
    sqlalchemy.engine.url.URL(
        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
)

On the other hand, if you are using Private IP, you should have in your code something like this

db_user = os.environ["DB_USER"]
db_pass = os.environ["DB_PASS"]
db_name = os.environ["DB_NAME"]
db_host = os.environ["DB_HOST"]

# Extract host and port from db_host
host_args = db_host.split(":")
db_hostname, db_port = host_args[0], int(host_args[1])

pool = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgres+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
    sqlalchemy.engine.url.URL(
        drivername="postgresql+pg8000",
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        host=db_hostname,  # e.g. "127.0.0.1"
        port=db_port,  # e.g. 5432
        database=db_name  # e.g. "my-database-name"
    ),
    **db_config
)

Make sure you have configured your Cloud Functions depending on if you are using Public IP or Private IP.

Upvotes: 1

Related Questions