<a href="https://cloud.google.com/blog/topics/developers-practitioners/how-connect-cloud-sql-using-python-easy-way">
    <img src="https://raw.githubusercontent.com/GoogleCloudPlatform/cloud-sql-python-connector/main/docs/images/cloud-sql-python-connector.png" alt="cloud-sql-python-connector image">
</a>

Cloud SQL Python Connector

The Cloud SQL Python Connector is a Cloud SQL connector designed for use with the Python language. Using a Cloud SQL connector provides a native alternative to the Cloud SQL Auth Proxy while providing the following benefits:

• IAM Authorization: uses IAM permissions to control who/what can connect to your Cloud SQL instances
• Improved Security: uses robust, updated TLS 1.3 encryption and identity verification between the client connector and the server-side proxy, independent of the database protocol.
• Convenience: removes the requirement to use and distribute SSL certificates, as well as manage firewalls or source/destination IP addresses.
• (optionally) IAM DB Authentication: provides support for Cloud SQL’s automatic IAM DB AuthN feature.

The Cloud SQL Python Connector is a package to be used alongside a database driver. Currently supported drivers are: - pymysql (MySQL) - pg8000 (PostgreSQL) - asyncpg (PostgreSQL) - pytds (SQL Server)

Installation

You can install this library with pip install, specifying the driver based on your database dialect.

MySQL

pip install "cloud-sql-python-connector[pymysql]"

Postgres

There are two different database drivers that are supported for the Postgres dialect:

pg8000

pip install "cloud-sql-python-connector[pg8000]"

asyncpg

pip install "cloud-sql-python-connector[asyncpg]"

SQL Server

pip install "cloud-sql-python-connector[pytds]"

APIs and Services

This package requires the following to successfully make Cloud SQL Connections:

• IAM principal (user, service account, etc.) with the Cloud SQL Client role. This IAM principal will be used for credentials.
• The Cloud SQL Admin API to be enabled within your Google Cloud Project. By default, the API will be called in the project associated with the IAM principal.

Credentials

This library uses the Application Default Credentials (ADC) strategy for resolving credentials. Please see these instructions for how to set your ADC (Google Cloud Application vs Local Development, IAM user vs service account credentials), or consult the google.auth package.

To explicitly set a specific source for the credentials, see Configuring the Connector below.

Usage

This package provides several functions for authorizing and encrypting connections. These functions are used with your database driver to connect to your Cloud SQL instance.

The instance connection name for your Cloud SQL instance is always in the format "project:region:instance".

How to use this Connector

To connect to Cloud SQL using the connector, inititalize a Connector object and call its connect method with the proper input parameters.

The Connector itself creates connection objects by calling its connect method but does not manage database connection pooling. For this reason, it is recommended to use the connector alongside a library that can create connection pools, such as SQLAlchemy. This will allow for connections to remain open and be reused, reducing connection overhead and the number of connections needed.

In the Connector's connect method below, input your connection string as the first positional argument and the name of the database driver for the second positional argument. Insert the rest of your connection keyword arguments like user, password and database. You can also set the optional timeout or ip_type keyword arguments.

To use this connector with SQLAlchemy, use the creator argument for sqlalchemy.create_engine:

from google.cloud.sql.connector import Connector
import sqlalchemy

# initialize Connector object
connector = Connector()

# initialize SQLAlchemy connection pool with Connector
pool = sqlalchemy.create_engine(
    "mysql+pymysql://",
    creator=lambda: connector.connect(
        "project:region:instance",
        "pymysql",
        user="my-user",
        password="my-password",
        db="my-db-name"
    ),
)

The returned connection pool engine can then be used to query and modify the database.

# insert statement
insert_stmt = sqlalchemy.text(
    "INSERT INTO my_table (id, title) VALUES (:id, :title)",
)

with pool.connect() as db_conn:
    # insert into database
    db_conn.execute(insert_stmt, parameters={"id": "book1", "title": "Book One"})

    # query database
    result = db_conn.execute(sqlalchemy.text("SELECT * from my_table")).fetchall()

    # commit transaction (SQLAlchemy v2.X.X is commit as you go)
    db_conn.commit()

    # Do something with the results
    for row in result:
        print(row)

To close the Connector object's background resources, call its close() method as follows:

connector.close()

[!NOTE]

For more examples of using SQLAlchemy to manage connection pooling with the connector, please see Cloud SQL SQLAlchemy Samples.

Configuring the Connector

If you need to customize something about the connector, or want to specify defaults for each connection to make, you can initialize a Connector object as follows:

from google.cloud.sql.connector import Connector

# Note: all parameters below are optional
connector = Connector(
    ip_type="public",  # can also be "private" or "psc"
    enable_iam_auth=False,
    timeout=30,
    credentials=custom_creds, # google.auth.credentials.Credentials
    refresh_strategy="lazy",  # can be "lazy" or "background"
)

Using Connector as a Context Manager

The Connector object can also be used as a context manager in order to automatically close and cleanup resources, removing the need for explicit calls to connector.close().

Connector as a context manager:

from google.cloud.sql.connector import Connector
import sqlalchemy

# initialize Cloud SQL Python Connector as context manager
with Connector() as connector:
    # initialize SQLAlchemy connection pool with Connector
    pool = sqlalchemy.create_engine(
        "mysql+pymysql://",
        creator=lambda: connector.connect(
            "project:region:instance",
            "pymysql",
            user="my-user",
            password="my-password",
            db="my-db-name"
        ),
    )
    # insert statement
    insert_stmt = sqlalchemy.text(
        "INSERT INTO my_table (id, title) VALUES (:id, :title)",
    )

    # interact with Cloud SQL database using connection pool
    with pool.connect() as db_conn:
        # insert into database
        db_conn.execute(insert_stmt, parameters={"id": "book1", "title": "Book One"})

        # commit transaction (SQLAlchemy v2.X.X is commit as you go)
        db_conn.commit()

        # query database
        result = db_conn.execute(sqlalchemy.text("SELECT * from my_table")).fetchall()

        # Do something with the results
        for row in result:
            print(row)

Configuring a Lazy Refresh (Cloud Run, Cloud Functions etc.)

The Connector's refresh_strategy argument can be set to "lazy" to configure the Python Connector to retrieve connection info lazily and as-needed. Otherwise, a background refresh cycle runs to retrive the connection info periodically. This setting is useful in environments where the CPU may be throttled outside of a request context, e.g., Cloud Run, Cloud Functions, etc.

To set the refresh strategy, set the refresh_strategy keyword argument when initializing a Connector:

connector = Connector(refresh_strategy="lazy")

Specifying IP Address Type

The Cloud SQL Python Connector can be used to connect to Cloud SQL instances using both public and private IP addresses, as well as Private Service Connect (PSC). To specify which IP address type to connect with, set the ip_type keyword argument when initializing a Connector() or when calling connector.connect().

Possible values for ip_type are "public" (default value), "private", and "psc".

Example:

conn = connector.connect(
    "project:region:instance",
    "pymysql",
    ip_type="private"  # use private IP
... insert other kwargs ...
)

[!IMPORTANT]

If specifying Private IP or Private Service Connect (PSC), your application must be attached to the proper VPC network to connect to your Cloud SQL instance. For most applications this will require the use of a VPC Connector.

Automatic IAM Database Authentication

Connections using Automatic IAM database authentication are supported when using Postgres or MySQL drivers. First, make sure to configure your Cloud SQL Instance to allow IAM authentication and add an IAM database user.

Now, you can connect using user or service account credentials instead of a password. In the call to connect, set the enable_iam_auth keyword argument to true and the user argument to the appropriately formatted IAM principal.

Postgres: For an IAM user account, this is the user's email address. For a service account, it is the service account's email without the .gserviceaccount.com domain suffix.

MySQL: For an IAM user account, this is the user's email address, without the @ or domain name. For example, for test-user@gmail.com, set the user argument to test-user. For a service account, this is the service account's email address without the @project-id.iam.gserviceaccount.com suffix.

Example:

conn = connector.connect(
     "project:region:instance",
     "pg8000",
     user="postgres-iam-user@gmail.com",
     db="my-db-name",
     enable_iam_auth=True,
 )

SQL Server (MSSQL)

[!IMPORTANT]

If your SQL Server instance is set to enforce SSL connections, you need to download the CA certificate for your instance and include cafile={path to downloaded certificate} and validate_host=False. This is a workaround for a known issue.

Active Directory Authentication

Active Directory authentication for SQL Server instances is currently only supported on Windows. First, make sure to follow these steps to set up a Managed AD domain and join your Cloud SQL instance to the domain. See here for more info on Cloud SQL Active Directory integration.

Once you have followed the steps linked above, you can run the following code to return a connection object:

conn = connector.connect(
    "project:region:instance",
    "pytds",
    db="my-db-name",
    active_directory_auth=True,
    server_name="public.[instance].[location].[project].cloudsql.[domain]",
)

Or, if using Private IP:

conn = connector.connect(
    "project:region:instance",
    "pytds",
    db="my-db-name",
    active_directory_auth=True,
    server_name="private.[instance].[location].[project].cloudsql.[domain]",
    ip_type="private"
)

Using Advanced Disaster Recovery and DNS domain names to identify instances

The connector can be configured to use DNS to look up an instance. Use a DNS name managed by Cloud SQL [Advanced Disaster Recover