如何使用python在云函数中返回postgreSQL查询结果？[副本]

Question

我正在使用Python3.6编写一个带有get方法的FlaskAPI，它将创建一个与GCP postgreSQL的连接，并执行一个简单的select查询。这里我希望我的函数应该返回postgreSQL查询的结果（行）。下面是我的代码：

from sqlalchemy.engine import url
import pg8000
import logging
import os
import json
from flask import Flask, Response, request, render_template
import sqlalchemy

app = Flask(__name__)

logger = logging.getLogger()

# Environment specific parameter values to establish DB connection with postgreSQL
db_user = os.environ.get("DB_USER")
db_pass = os.environ.get("DB_PASS")
db_name = os.environ.get("DB_NAME")
cloud_sql_connection_name = os.environ.get("CLOUD_SQL_CONNECTION_NAME")

# Creating a connection with postgresql DB model.
db = sqlalchemy.create_engine(
        url.URL(
            drivername='postgres+pg8000',
            username=db_user,
            password=db_pass,
            database=db_name,
            query={
                'unix_sock': '/cloudsql/{}/.s.PGSQL.5432'.format(
                    cloud_sql_connection_name)
            }
        ),
        pool_size=5,
        max_overflow=2,
        pool_timeout=30,
        pool_recycle=1800,
    )


@app.route('/', methods=['GET'])
def main(request):

    with db.connect() as conn:
        # Execute the query and fetch all usernames
        users = conn.execute("select username from user_table").fetchall()

    return users


if __name__ == '__main__':
    app.run(host='127.0.0.1', port=8080, debug=True)

Answer 1

Fatechall（）从docs返回游标https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.ResultProxy.fetchall.不能将游标对象作为响应返回。您需要将其转换为允许的响应类型。下面的代码可能会帮助您理解这一点。

@app.route('/', methods=['GET'])
def main(request):
    users = []
    with db.connect() as conn:
        # Execute the query and fetch all usernames
        db_users = conn.execute("select username from user_table").fetchall()
        users = list(db_users)
    data = {'users': users}
    resp = Response(json.dumps(data), status=200, mimetype="application/json")
    return resp

Answer 2

我尝试了您的代码并收到了以下错误：

TypeError:view函数未返回有效响应。返回类型必须是字符串、dict、tuple、响应实例或WSGI可调用，但它是一个列表

所以尝试返回str（用户）或先将数据转换为多少。