用户在Ubuntu机器上通过cloud SQL代理连接GCP posrgreSQL cloud SQL，并在python

Question

我在GCP中创建了一个postgreSQL云SQL实例，并为它创建了一个用户和一个DB。我可以通过cloud_sql_proxy工具连接到它:

$ cloud_sql_proxy -instances=project_name:REGION:instance_name=tcp:5432 -credential_file=/path/to/key.json

然后我可以通过psql成功连接到实例，并在命令行中运行查询和插入数据等:

$ psql "host=127.0.0.1 port=5432 sslmode=disable dbname=myDBname user=myUser"
Password: 
psql (10.18 (Ubuntu 10.18-0ubuntu0.18.04.1), server 13.3)
WARNING: psql major version 10, server major version 13.
Some psql features might not work.
Type "help" for help.
myDBname=>SELECT * FROM MyTable;

我的问题是，当我尝试使用sqlalchemy库并使用sqlalchemy示例代码中提供的示例代码时，如下所示:

import sqlalchemy
import os
db_config = {        
"pool_size": 5,        
"max_overflow": 2,
"pool_timeout": 30,  # 30 seconds
"pool_recycle": 1800,  # 30 minutes
}
def init_tcp_connection_engine(db_config):    
db_user = "myUser"
db_pass = "myPassword"
db_name = "myDBname"
db_hostname = "127.0.0.1"
db_port = 5432   
pool = sqlalchemy.create_engine(
# Equivalent URL:
# postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
sqlalchemy.engine.url.URL.create(
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
)
# [END cloud_sql_postgres_sqlalchemy_create_tcp]
pool.dialect.description_encoding = None
return pool
def main():
db = init_tcp_connection_engine(db_config)
with db.connect() as conn:
rows = conn.execute("SELECT * FROM MyTable;").fetchall()
for row in rows:
print(row)

if __name__ == "__main__":
main()

我得到的错误是

Exception has occurred: ProgrammingError       (note: full exception trace is shown but execution is paused at: <module>)
(pg8000.dbapi.ProgrammingError) {'S': 'FATAL', 'V': 'FATAL', 'C': '28P01', 'M': 'password authentication failed for user "myUser"', 'F': 'auth.c', 'L': '347', 'R': 'auth_failed'}
(Background on this error at: https://sqlalche.me/e/14/f405)

知道什么是错的，我该如何解决这个问题吗?

Answer 1

我通过web更改了密码，然后将其粘贴到代码中，并成功了。