2

We can run the following python code to execute a Cloud SQL (postgresql) query:

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=self.db_user,  # e.g. "my-database-user"
        password=self.db_pass,  # e.g. "my-database-password"
        host=self.db_hostname,  # e.g. "127.0.0.1"
        port=self.db_port,  # e.g. 5432
        database=self.db_name  # e.g. "my-database-name"
    )
)
pool.dialect.description_encoding = None
self.connection = pool.connect()

result = self.connection.execute(query)

I would like to deploy this function in a Cloud Function with HTTP endpoint, that then sends a request to this endpoint and returns the result.

However, as result is of the type sqlalchemy.engine.cursor.LegacyCursorResult, this is not possible as it yields:

 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 LegacyCursorResult.

What is the best way therefore to package this result in a return (e.g. convert to JSON or bytes?) and then unpack it at the source?

WJA
  • 6,676
  • 16
  • 85
  • 152
  • 1
    Relevant https://stackoverflow.com/questions/5022066/how-to-serialize-sqlalchemy-result-to-json, https://stackoverflow.com/questions/1958219/how-to-convert-sqlalchemy-row-object-to-a-python-dict (at first glance, has some better answers) . – snakecharmerb Apr 06 '22 at 16:00
  • 1
    [This answer](https://stackoverflow.com/a/1958228/5320906) was updated by the SQLAlchemy maintainer recently, so it's probably the canonical method for a single table query. – snakecharmerb Apr 09 '22 at 08:58

0 Answers0