Python error while connecting to cloud sql from cloud functions

Question

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

Samuel Romero · Accepted Answer · 2021-02-08 23:02:48Z

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.

Luis Vargas · Accepted Answer · 2021-03-04 03:56:21Z

1

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()

answered Mar 4, 2021 at 3:56

Luis Vargas

3913 silver badges8 bronze badges

Collectives™ on Stack Overflow

Python error while connecting to cloud sql from cloud functions

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