Python Google Cloud Postgres SQL sqlalchemy.engine.base.Engine SELECT with last row id as return value

Question

I am creating an application in Python that will run on Google Cloud Run. In Google Cloud I have created a Google Cloud SQL Postgres SQL database.

To connect to the database I have used Googles code https://github.com/GoogleCloudPlatform/python-docs-samples/blob/HEAD/cloud-sql/postgres/sqlalchemy/connect_connector.py.

The manual I have followed for Google Run and Google SQL Postgres is located here: https://cloud.google.com/sql/docs/postgres/connect-run. This does NOT use psycopg2, so cursor.fetchone()[0] does not give the last row.

Everything is working, however I am struggeling with fetching the last Row ID after an insert statement.

This is how I used to do it with psycopg2 library for PostgreSQL:

query = 'INSERT INTO mytable (first_name, last_name) VALUES (%s,%s)'
record = ('Jack', 'Anderson')

cursor = conn.cursor()
cursor.execute(query, record)
conn.commit()
cursor.execute('SELECT LASTVAL()')
id_of_new_row = cursor.fetchone()[0]

Now with Google Cloud sqlalchemy I have tried the following with out any luck:

 query = 'INSERT INTO mytable (first_name, last_name) VALUES (%s,%s)'
 record = ('Jack', 'Anderson')

 result = pool.execute(query, record)
 new_id = result.fetchone()[0]

This gives me:

This result object does not return rows. It has been closed automatically.

My complete code:

import os

from google.cloud.sql.connector import Connector, IPTypes
import pg8000

import sqlalchemy


# connect_with_connector initializes a connection pool for a
# Cloud SQL instance of Postgres using the Cloud SQL Python Connector.
def connect_with_connector() -> sqlalchemy.engine.base.Engine:
    # Note: Saving credentials in environment variables is convenient, but not
    # secure - consider a more secure solution such as
    # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
    # keep secrets safe.

    instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"]  # e.g. 'project:region:instance'
    db_user = os.environ["DB_USER"]  # e.g. 'my-db-user'
    db_pass = os.environ["DB_PASS"]  # e.g. 'my-db-password'
    db_name = os.environ["DB_NAME"]  # e.g. 'my-database'

    ip_type = IPTypes.PRIVATE if os.environ.get("PRIVATE_IP") else IPTypes.PUBLIC

    # initialize Cloud SQL Python Connector object
    connector = Connector()

    def getconn() -> pg8000.dbapi.Connection:
        conn: pg8000.dbapi.Connection = connector.connect(
            instance_connection_name,
            "pg8000",
            user=db_user,
            password=db_pass,
            db=db_name,
            ip_type=ip_type,
        )
        return conn

    # The Cloud SQL Python Connector can be used with SQLAlchemy
    # using the 'creator' argument to 'create_engine'
    pool = sqlalchemy.create_engine(
        "postgresql+pg8000://",
        creator=getconn,
        # ...
    )
    return pool


# Connect
pool = connect_with_connector()

# Insert
query = 'INSERT INTO mytable (first_name, last_name) VALUES (%s,%s)'
record = ('Jack', 'Anderson')

result = pool.execute(query, record)
new_id = result.fetchone()[0]