I’m experiencing a problem with SQLAlchemy in a Google Cloud Function where I receive the error: “This result object does not return rows. It has been closed automatically.” The error occurs when trying to fetch results from a query execution.

Here’s a code snippet where the error occurs:

from sqlalchemy import create_engine, text

# ... [Other necessary imports]

# Environment configuration and secret management setup

# Database connection pool creation function
def get_db_pool():
    pool = sqlalchemy.create_engine(
        sqlalchemy.engine.url.URL.create(
            drivername="postgresql+pg8000",
            username=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=5432,
            database=DB_NAME,
            ),
        pool_size=10,
        max_overflow=9,
        echo=True
    )
    return pool

def main(event, context):

    def callback(message: Message) -> None:
        message_data = json.loads(message.data, strict=False)
        print(message_data)
        if message_data["recipient"]:
            try:
                result = db_conn.execute(
                    sqlalchemy.text(
                        """
                        SELECT
                            ml.*
                        FROM
                            table_1 ml
                        JOIN
                            table_2 mm
                        ON
                            ml.table_1_id = mm.table_2_id
                        WHERE
                            mm.name = :campaign_name
                        AND adresse_email = :email
                        AND sent = true;
                        """
                    ),
                    {
                        "campaign_name": message_data["campaign_name"],
                        "email": message_data["recipient"],
                    },
                ).first()
            except Exception as e:
                print(
                    f"Error accessing the database: {e} "
                    f"nThis error occurred for campaign "{message_data['campaign_name']}""
                    f" and email "{message_data['recipient']}""
                )
            # Rest of the callback function
            return
    
    # Get database pool
    db_pool = get_db_pool()
    response = subscriber.subscribe(
        subscription=subscriber_path,
        callback=callback,
        flow_control=pubsub_v1.types.FlowControl(max_messages=100),
    )
    with db_pool.connect() as db_conn:
        timeout = 60
        with subscriber:
            try:
                response.result(timeout=timeout)
            except TimeoutError:
                response.cancel()
                response.result()
    pool.dispose()
    return

The purpose of this function is to process messages from a Pub/Sub subscription. Each message triggers an email sending operation if certain conditions in our Cloud SQL instance are met. Here are some specifics about the scenario:

• The function checks if an email has already been sent by querying a PostgreSQL table using SQLAlchemy.
• If not already sent, it sends out emails using Mailgun.
• After sending emails, it logs this action in our database.

During testing/debugging, before calling execute(), I’ve confirmed that db_conn.closed returns False, indicating that the connection should be open. However, when trying to execute my SELECT statement and fetch results with .fetchall(), I encounter the aforementioned error.

Here’s what I have tried so far:

• Checked that environment variables are correctly configured.
• Ensured that secret credentials are being correctly retrieved.
• Verified that SQL queries run as expected directly against the database.
• Debugged to check if db_conn.closed is False before running .execute().

I am looking for guidance on why this error might be occurring and how to resolve it.

Additional Information:

• Using SQLAlchemy version 2.0.21
• Running on Google Cloud Functions Python runtime version 3.11
• Connecting to Google Cloud SQL – PostgreSQL private instance

Any help would be greatly appreciated!