Search code examples
mysqlgoogle-cloud-platformgoogle-cloud-sqlpymysql

GCP Cloud SQL (mySQL) - connect to DB in local environment

I am having trouble with connecting my python3 - flask app (run on Google App Engine) with mySQL database instance. To be specific - I cannot connect when I run development server via 'python main.py' in shell command line on Google Cloud.

I use pymysql package and connect via create_engine function.

I succesfully connected to the DB in the deployed app: create_engine('mysql+pymysql://root:<pass>@/<instance>?unix_socket=/cloudsql/<project>:<region>:<database>')

Also I managed to connect from external environment, by authorizing my local network IP address in Google SQL Cloud and running: create_engine('mysql+pymysql://root:<pass>@<publicIP>/<database>')

In development server, I tried with 127.0.0.1, localhost, with and without :3306 port, etc.. and nothing worked...

I am able connect to the DB directly from Google Shell via gcloud sql connect <instance> --user=root, so it seems that the connection is authorised between cloud Google Shell console and SQL Cloud. But how do I do that with create_engine('mysql+pymysql://...')? and then run the python main.py in cloud shell?

Thanks in advance for your help! Cheers J.

Solution

  • You can solve this with 2 solutions:

    1. Don't touch the code/param that you use on App Engine

    It's my preferred solution, because you test the same thing locally and on the cloud. But you need the same environment. That starts by using Unix compliant environment (if you use Windows, skip this solution).

    Then, you need to open a unix socket connected to the Cloud SQL instance. For that, download Cloud SQL proxy binary and open a tunnel following these steps. Eventually, run your app in another terminal.

    1. Use custom DB connection string

    In this case, you will use the pattern create_engine('mysql+pymysql://root:<pass>@<publicIP>/<database>'). But for that you need to authorize your public IP to access to your Cloud SQL instance. For that, get your public IP (many website give you that on the internet) and add it in the authorized networks of your Cloud SQL instance (add /32 at the end of your IP because it's an IP range that is required). And it sould work!