4

Looking for relevant python code to be used in the GCP AI Platform notebook which will be able to query GCP Cloud SQL (specifically Postgresql.) Unfortunately, I haven't found any relevat resources/tutorials from GCP official or even unaffiliated resources.

jbuddy_13
  • 902
  • 2
  • 12
  • 34

1 Answers1

8

I work on the Developer Relations team for Cloud SQL at Google. The quickest and simplest way to connect to a Cloud SQL instance from an AI Platform Notebook is to use our newly released Cloud SQL Python Connector. To install, run pip install cloud-sql-python-connector[pg8000]. This will also install the pg8000 driver which is used to connect to Postgres.

Then, you can use the following code to get a connection object which you can use to run queries:

conn = connector.connect(
    "project_name:region-name:instance-name", 
    "pg8000",
    user="postgres",
    password="password",
    db="db_name"
)

An alternative, which is language-independent, is to download and run the Cloud SQL Auth Proxy in a separate terminal from your notebook.

For either of these solutions, make sure to enable the Cloud SQL Admin API first.

gogasca
  • 9,283
  • 6
  • 80
  • 125
Shubha Rajan
  • 325
  • 1
  • 7
  • This connector is great, but still in alpha. Use it with caution! – guillaume blaquiere May 06 '21 at 07:18
  • 1
    FYI, we're working to get it from preview status to a GA release soon. Please let us know if you run into any issues. Thanks! – Doug Mahugh May 06 '21 at 16:37
  • Quick follow up, if I need to stream in a csv file, I see that postgresql's copy function is generally preferred. Is there an analogous function/method in the Cloud SQL Python Connector? – jbuddy_13 May 27 '21 at 19:17
  • The python connector isn't a SQL client itself. It's a sort of wrapper around other SQL clients (pg8000 for postgres). You can use the returned connection from the python connector just as you would use a pg8000 connection object. Here's an example of how to import a csv using a pg8000 connection: https://stackoverflow.com/questions/47757873/pg8000-copy-from-csv – Shubha Rajan Jun 02 '21 at 04:39