How to access Cloud SQL from Google Colab

Question

I have a Google Cloud SQL instance with a public IP, only accessible to whitelisted IP and through an SSL connection.

I'd like to know how I can connect to this database from Google Colab with Python.

If I try to connect like any external application, the connection is refused since the ip of the "client" is not whitelisted (and I can't whitelist it since I don't it and it's highly probable it's volatile)

Is there a shortcut, like with Google App Engine to connect to the database using its instance and a google client?

Thanks

user_6625815 · Answer

A little late to answer, but I think I have a solution and it involved using the Cloud SQL Proxy. Overall, you first need to use the Gcloud SDK (included with Colab) to authenticate, then install the proxy, then spin it up. I did this in two blocks

# gcloud login and check the DB
!gcloud auth login
!gcloud config set project [YOUR PROJECT ID]
!gcloud sql instances describe [YOUR CLOUDSQL INSTANCE ID]

This last line will output a dump of info and we want connectionName in particular. The next block then downloads the proxy and tells it to proxy for that CloudSQL instance:

# download and initialize the psql proxy
!wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
!chmod +x cloud_sql_proxy
# "connectionName" is from the previous block
!nohup ./cloud_sql_proxy -instances="[connectionName]"=tcp:5432 &
!sleep 30s

Later on you can (and I've found it helpful) to check the proxy's logs with

!cat nohup.out

And finally, you can construct a connection with the address 127.0.0.1:5432 (or whatever port you set above. I did so with psycopg2 like this

conn = psycopg2.connect(
    host='127.0.0.1', port='5432', database=[YOUR DB NAME],
    user=[USERNAME], password=[PASSWORD])

It seems to work, though it's definitely a bit slower than a direct connection.

user_14344260 · Answer

Go to the Cloud SQL Instances page in the Google Cloud Console. Go to the Cloud SQL Instances page

Click the instance name to open its Instance details page. Select the Connections tab. Select the Public IP checkbox. Click Add network. In the Network field, enter the IP address or address range you want to allow connections from.(for colab add two networks:-34.0.0.0/8 and 35.0.0.0/8) Use CIDR notation.

Optionally, enter a name for this entry. Click Done. Click Save to update the instance.

and i used pymysql module to connect to database and it worked!!!

in colab,

`pymysql.connect(host="enter publicIP present in overview of sql instance", user="root",passwd="", db="your database name") `

user_14840093 · Answer

The most simple way for SQL SERVER, as long as you allow the two colab networks already mentioned: 34.0.0.0/8 and 35.0.0.0/8

    !pip install pymssql
    import pymssql
    conn = pymssql.connect(server='public IP',user='user',password='pass',database='dbname') 
    cursor = conn.cursor()  
    cursor.execute('SELECT TOP 10 * FROM table;')  
    row = cursor.fetchone()  
    while row:
     print(str(row))
     row = cursor.fetchone()

How to access Cloud SQL from Google Colab

3 Answers