5

Connecting to Cloud SQL using Python is not always straightforward.

Depending on the context, sometimes you have to connect to a Unix domain socket, allow-list IP addresses for TCP connections, run the Cloud SQL Auth proxy locally. Making these connections secure is yet another challenge: you might have to manage SSL certificates, firewalls rules, IP addresses, etc.

Is there a recommended way to Connect to Cloud SQL in a secure and easy way using Python?

Jack Wotherspoon
  • 1,131
  • 3
  • 13

1 Answers1

10

Yes there indeed is, the Cloud SQL Python Connector, a Python package that makes connecting to Cloud SQL both easy and secure for all three supported database engines (Postgres, MySQL, and SQL Server), from anywhere (local machine, Cloud Run, App Engine, Cloud Functions, etc.)

The Python Connector is one of the Cloud SQL connector libraries (also available in Java, Go, and Node).

How is a connector different from the other methods?

The Cloud SQL connector libraries provide the following benefits:

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

How do I use the Python Connector ... what does the code look like?

Basic Usage (using SQLAlchemy)

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

# initialize Cloud SQL Connector
connector = Connector()

# SQLAlchemy database connection creator function
def getconn():
    conn = connector.connect(
        "project:region:instance-name", # Cloud SQL Instance Connection Name
        "pg8000",
        user="my-user",
        password="my-password",
        db="my-db-name",
        ip_type=IPTypes.PUBLIC # IPTypes.PRIVATE for private IP
    )
    return conn

# create SQLAlchemy connection pool
pool = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
)

# interact with Cloud SQL database using connection pool
with pool.connect() as db_conn:
    # query database
    result = db_conn.execute("SELECT * from my_table").fetchall()

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

# close Cloud SQL Connector
connector.close()

There are interactive "Getting Started" Colab notebooks that show you how to use the Cloud SQL Python Connector – all without needing to write a single line of code yourself! The notebooks will automatically use a supported database driver based on the database engine you are using with Cloud SQL.

Does it work with popular web frameworks?

Yes, the Python Connector can easily be used in web frameworks such as Flask-SQLAlchemy (and Flask), FastAPI, etc.

Jack Wotherspoon
  • 1,131
  • 3
  • 13
  • Thanks for this. I'd like to create bindings to multiple Cloud SQL databases. How would I pass e.g. a different password to the `getconn` function? I've tried passing a `connect_args={"password": my_cloudsql_pw}` parameter to the `create_engine` function, but can't figure out how to pass it to the `getconn` function. – antti Feb 14 '23 at 12:40
  • @antti if you want multiple connection pools with varying configs (user and passwords) I would recommend wrapping a function around the `getconn` and engine initialization. Here is an example of what I mean: https://gist.github.com/jackwotherspoon/46c30043f5875869995e40da94009f91 – Jack Wotherspoon Feb 14 '23 at 18:34
  • Thanks! I'm actually using Flask-SQLAlchemy and configuring the app like this: `app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://"` `app.config['SQLALCHEMY_ENGINE_OPTIONS'] = { "creator": getconn }` `app.config['SQLALCHEMY_BINDS'] = { "dev": { "url": "mysql+pymysql://","creator": getconn } }` so not sure how that would work in this case? I'm not sure if I'm doing something wrong here. – antti Feb 15 '23 at 07:59
  • If you are building an app with Flask-SQLAlchemy what would be the need for dynamic users and password to be passed in? Just wondering the use case? Feel free to create a separate question here on Stackoverflow for this or open a [Github issue](https://github.com/GoogleCloudPlatform/cloud-sql-python-connector/issues) on our repo of type "question". Would be happy to discuss more :) – Jack Wotherspoon Feb 15 '23 at 15:01
  • 1
    Thanks for the help! I don't actually need to do it dynamically, but I have a service running on cloudrun that needs to move data between two Cloud SQL databases. I ended up just declaring two instances of the getconn -function i.e. getconn1 and getconn2 and then referring to those in the respective app configs. – antti Feb 17 '23 at 11:51
  • Is there any difference doing it this way versus using `cloud-sql-proxy` which is the first thing popping up when googling? – Pithikos Mar 29 '23 at 13:22
  • @Pithikos great question, TLDR both perform the same job, `cloud-sql-proxy` is language agnostic while Python Connector is native to Python. See https://github.com/GoogleCloudPlatform/cloud-sql-proxy/issues/1715#issuecomment-1487465436 for detailed answer. – Jack Wotherspoon Mar 30 '23 at 14:06