Cloud SQL and Python

Cloud SQL and Python

Build SQL access from your machine via Python.

To authenticate your Python script using that specific service account, you need to

point your environment to a JSON key file. This file acts as the "ID card" for your service account.

Generate the JSON Key

If you haven't already generated the key for

p1093417627142-xhnsce@gcp-sa-cloud-sql.iam.gserviceaccount.com:

Go to the IAM & Admin > Service Accounts page in your GCP Console.

Select your service account.

1. Click the Keys tab > Add Key > Create new key.

2. Choose JSON and download the file

3. (e.g., service-account-key.json).

Go to cloud shell

Install library needed for cloud sql python naccess

john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$

pip install cloud-sql-python-connector sqlalchemy pymys

Set the Environment Variable

You must tell the Google libraries where to find this file. You can do this in two ways:

Option A: Set via Terminal (Recommended for local dev)

Before running your Python script, run this command in your terminal:

Linux / macOS:

Bash

export GOOGLE_APPLICATION_CREDENTIALS=

"/path/to/your/service-account-key.json"

Option B: Set inside the Python Script

If you want the script to handle it automatically (useful for quick testing), add these

two lines to the very top of your Python code:

Python

import os

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your/service-account-key.json"

Upload from local machine to directory

Click the 3 dots on right hand side of Open Editor

Then select upload

Choose file (your local download directory)

Click folder to select your destination directory (where you are working from)

Click Upload

Export to environment

john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$

export GOOGLE_APPLICATION_CREDENTIALS="/home/john_iacovacci1/yf/sentiment-analysis-379200-af742fd5f9d7.json"

Create a requirements.txt file

google-cloud-sql-connector==1.15.0

sqlalchemy==2.0.25

pymysql==1.1.0

cryptography==42.0.2

Note: Created a user named john password uconnstamford

Create a main.py program

==================================================

import os

from google.cloud.sql.connector import Connector

import sqlalchemy

# 1. Initialize the Connector

connector = Connector()

def getconn():

    conn = connector.connect(

        "sentiment-analysis-379200:us-east1:financial",

        "pymysql",

        user="john", # Create this in GCP Console > SQL > Users

        password="uconnstamford",

        db="trading",

        enable_iam_auth=False

    )

    return conn

# 2. Create pool with a longer timeout to prevent the 'ServerDisconnected' error

pool = sqlalchemy.create_engine(

    "mysql+pymysql://",

    creator=getconn,

    pool_pre_ping=True, # Checks if connection is alive before using it

)

try:

    with pool.connect() as db_conn:

        result = db_conn.execute(sqlalchemy.text("SELECT * FROM security LIMIT 10"))

        for row in result:

            print(row)

except Exception as e:

    print(f"Connection Failed: {e}")

finally:

    connector.close()

=======================================================

Python returns values from SQL commands inside the program.

john_iacovacci1@cloudshell:~/yf (sentiment-analysis-379200)$ python3 main.py

('BBG000BPH459', 'MICROSOFT CORP', 'MSFT', 'US', 'BBG000BPH459',

'Common Stock', 'Equity', 'BBG000BPH459', 'BBG001S5TD05')