Search code examples
pythonmysqlgoogle-cloud-platformdatabase-connectiongoogle-cloud-sql

Can't connect to Google SQL Database with Python Script run in Google Functions

The purpose of this python script is to pull data from google analytics and add it to a google MySQL database. The script works on my local machine, but not on google functions:

from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
import mysql.connector
from datetime import datetime
from datetime import timedelta
import os
from mysql.connector import errorcode


def main(data, context):

    # MySQL
    DB_NAME = 'GA_Data1'
    cnx = mysql.connector.connect(user=user1, password=password1,
                                  host=hostip, database=DB_NAME)

When I run the code above I'm getting these errors:

I think it might be because google's machine's IP address isn't white listed for my SQL database. I tried adding a cloud NAT with an external IP address so I could whitelist it. That didn't work. Here's a link to an article that helps connect to a SQL database with python. Should I replace some of my code with the code in this article?

I'm resistant to changing anything because it works perfectly well on my computer. Therefore the problem lies somewhere with the running of the script on google's machines.

Solution

  • You need to add a VPC connector so that it can connect directly to the SQL server privately.

    See https://cloud.google.com/sql/docs/mysql/connect-functions

    It's under "Networking" on egress side. You'll have to enable a few things in your Google Cloud Project, but it should walk you through it.

    enter image description here

    I'd recommend enabling the "Private IP" on the SQL database side.

    enter image description here

    And then using that private IP to connect in your function, and checking the box to only route private IPs on the Cloudfunction (in case you're doing a bunch of other stuff in there too):

    enter image description here