How to return postgreSQL query result in cloud function using python?

Question

I am writing a flask api with get method using python 3.6 which will create a connection with GCP postgreSQL and execute a simple select query. Here I want that my function should return me a result of postgreSQL query(rows). Below is my code :

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)

score 1 · Answer 1 · answered Nov 13 '19 at 16:27

I tried your code and received this error:

TypeError: The view function did not return a valid response. The return type must be a string, dict, tuple, Response instance, or WSGI callable, but it was a list

So try returning str(users) or convert data to dict first.

score 0 · Answer 2 · answered Nov 13 '19 at 16:49

fetchall() returns the cursor from docs https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.ResultProxy.fetchall. You can't return the cursor object as a response. You need to convert it to allowed response types. Below code might help you understand the same.


@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

How to return postgreSQL query result in cloud function using python?

2 Answers2