Can't connect to Cloud SQL using PyMySQL

Question

I'm trying to connect to Cloud SQL from a Python application (using PyMySQL 0.7.9) running on top of Google App Engine.

My connection string looks like this (credentials are fake of course):

pymysql.connect(unix_socket='/cloudsql/gae_project_name:cloudsql_instance_name', 
                user='user', password='', db='database_name')

The error message I receive is:

OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 97] Address family not supported by protocol)")

It's like PyMySQL doesn't recognize that I'm trying to connect through a Unix socket and tries the default value for the host argument instead (which I presume is localhost)

I am able to connect with MySQLdb with the same connection string.

Rorie

Answer 1

Apparently, PyMySQL is not currently supported on the Google App Engine Standard environment, which only runs Python 2.7 (as of June 2018). This is from the maintainers of the GCP python project:

I can confirm that pymysql is not supported in the python27 runtime. However, for most use cases, it's possible to use pymysql locally and mysqldb in production by using a try: / except ImportError: to import one or the other conditionally. As they share the same interface, you can use import as to make the two different libraries share the same name for ease of use in your code.

See this Github thread for details

Contagious

Answer 2

Why don't use MySQLdb instead then ?

I just had the same problem deploying a Flask application with PyMySQL, I tried a lot of fixes without success. My workaround was to use MySQLDb instead aha..!

Lotion

Hehe that's what I do :) To my best of knowledge, the mysqldb library doesn't support Python 3. Hence the more future-proof option to go with PyMySQL these days (and as it's mysqldb API compatible, migrating between the two is easy) – Rorie

Answer 3

I got it to connect using a Python3 Flex App Engine and PyMySQL.

Here's what my app.yaml looks like:

runtime: python
env: flex
entrypoint: gunicorn -b :$PORT api:app --timeout 180

runtime_config:
  python_version: 3

env_variables:
  SQLALCHEMY_DATABASE_URI: >-
    mysql+pymysql://user:password@/database?unix_socket=/cloudsql/project-name:us-central1:instance-name

beta_settings:
  cloud_sql_instances: us-central1:instance-name

# This sample incurs costs to run on the App Engine flexible environment. 
# The settings below are to reduce costs during testing and are not appropriate
# for production use. For more information, see:
# https://cloud.google.com/appengine/docs/flexible/python/configuring-your-app-with-app-yaml
manual_scaling:
  instances: 1
resources:
  cpu: 1
  memory_gb: 0.5
  disk_size_gb: 10

Make sure to replace the user, password, database, and instance connection in both the env_variables and the beta_settings.

Here's my python:

import pymysql.cursors
import pymysql
connection = pymysql.connect(unix_socket='/cloudsql/project-name:us-central1:instance-name',
        user='user',
        password='password',
        db='database',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)

Corneliuscornell