从云函数连接到云sql时Python错误

Question

我试图使用Python代码从GCP Cloud functions连接到CloudSQL的PostgreSQL实例。我是python的新手，所以遇到了一些错误，需要帮助来修复错误

我的代码如下:

# 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:a1b2c3d4e190@X.X.X.X/postgres_dbase')
print("connected")

功能部署正常，但URL失败，错误如下

Error: could not handle the request

日志

显示如下错误

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

当我直接执行代码时，它工作正常

$ python3 open.py
connected

我使用Python 3.7

看起来我必须设置DATABASE_URL，但不确定如何在云功能中设置它。在Cloud functions中Runtime environmental variable以下添加，但没有运气

DATABASE_URL="sqlite://"

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

Answer 1

不确定您是尝试从公共IP连接还是从私有IP连接。但是，您可以在这里找到一个关于如何使用公共IP执行关注点的示例。你的代码应该看起来像

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
)

另一方面，如果你使用私有IP，你应该在你的代码中有这样的东西

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
)

确保你已经配置了云功能，这取决于你是使用公共IP还是私有IP。

Answer 2

这已经为我连接谷歌应用引擎到PostgreSQL实例从谷歌云平台。

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