Previously in this series, I went over what I used to build my first Python web app. In this post, we're taking a look at how to locally test a Flask app with Cloud SQL Proxy.
Locally testing a Flask app that's connected to a Cloud SQL instance can be a little tricky. It's not as simple as calling python3 main.py.
As an example, let's walk through creating a Cloud SQL instance and testing my Pokédex project locally.
Clone my repo to get started.
A look at Cloud SQL Proxy
Cloud SQL Proxy allows you to locally communicate securely with your Cloud SQL instance.
The proxy handles data encryption to and from your database using Transport Layer Security (TLS). If you're not familiar with TLS, you can see it in action as the padlock in your address bar. SSL certificates establish authority, verifying client and server identities.
It also handles authentication with Cloud SQL, removing the need to provide static IP addresses.
When you eventually deploy your app to App Engine, your app uses the Cloud SQL Proxy that’s built in to the App Engine environment.
When testing things locally, you need to download your own copy of the Cloud SQL Proxy.
Enable Google Cloud products
Since this app uses Google Cloud Platform (GCP) products, we need to create a GCP project.
Get access to GCP by creating a new project with billing enabled. If this is your first time trying out GCP, you can start a free trial.
You also need to install the Google Cloud SDK and enable the following APIs:
Once you’ve done all that, open a terminal and enter the following command to get credentials for authenticating with GCP services.
$ gcloud auth application-default login
Create a MySQL Cloud SQL instance
Let’s start by creating a second generation MySQL instance in your terminal.
The following command takes a few minutes to complete. Check up on the creation status at the instances page.
$ gcloud sql instances create <MY-INSTANCE> --root-password=<ROOT_PASSWORD> --database-version=MYSQL_5_7 --region=us-central
Now that you have an instance, add a new database named pokemon. This is where we store all pokemon and user info.
$ gcloud sql databases create pokemon --instance=<MY-INSTANCE>
Your new database now shows up in your Cloud SQL instance.
You can connect to your database with your root user account and password. Optionally, create a new user. A host set to % means the user has an unrestricted host name.
$ gcloud sql users create USERNAME \
--host=% --instance=<MY-INSTANCE> --password=PASSWORD
Connect to your database with Cloud SQL Proxy
Open a terminal at the sample code folder and download an executable Cloud SQL Proxy with the following commands. For non-linux commands, check out the Cloud SQL Proxy quickstart.
$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy`
$ chmod +x cloud_sql_proxy
Run the proxy and connect to your instance. You can find your instance connection name here.
./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306
Keep the Cloud SQL Proxy running in the current terminal and continue along in a new one. Without this connection, the app won’t be able to retrieve data from the pokemon database.
Configure your environment
For local testing, update your config.py. It’s best to store these variables as environment variables in app.yaml, however, app.yaml variables aren’t reflected until deploying to App Engine. I’ll go over app.yaml and the deployment process later in this series.
A hot new product Google Cloud added recently is Secret Manager. You should check it out if you want to store sensitive info in the cloud instead of in a file in your code.
Create a virtual environment
Since the Python packages and modules you’ve installed probably aren’t the same as what my app needs, a virtual environment gives my app the perfect place to run.
To get one up and running, enter the virtualenv command and activate it.
$ virtualenv -p python3 venv
$ source env/bin/activate
You should now see (venv) next to your file path in the terminal.
Now install the necessary packages and modules.
$ pip install -r requirements.txt
Load MySQL data with a .csv
Since the database is currently empty, testing my app now will fail.
In my repo I have all pokemon data stored in the pokemon-db.csv file. Use this to initially populate the database.
Call the following command to create your relational tables. At this point they’ll be empty and ready for some data.
$ python3 pokeview/models.py
In a new terminal, connect to your MySQL instance and load the provided pokemon-db.csv file. Your Cloud SQL Proxy must be running in a different terminal for this to work.
$ mysql -u username -p --host 127.0.0.1
LOAD DATA LOCAL INFILE 'pokemon-db.csv' INTO TABLE pokedex FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
After loading the CSV, your pokedex table should now have the original 151 pokemon in it.
Run it!
It’s time for all that setup work to pay off!
Run your app to start up a local webserver.
$ python3 main.py
In your web browser, go to http://localhost:8080 where the app is running. The port is configured via the main.py file.
Top comments (0)