Google Cloud Function with Google Cloud SQL: Python SQLalchemy engine works, but connection fails

Question

I have a simple test script to ensure I can connect to and read/write using GCF to Google Cloud SQL instance.

def update_db(request):

    import sqlalchemy

    # connect to GCP SQL db
    user = 'root'
    pw = 'XXX'
    conn_name = 'hb-project-319121:us-east4:hb-data'
    db_name = 'Homebase'

    url = 'mysql+pymysql://{}:{}@/{}?unix_socket=/cloudsql/{}'.format(user,pw,db_name,conn_name)
    engine = sqlalchemy.create_engine(url,pool_size=1,max_overflow=0)

    # Returns
    return f'Success'

This is successful but when I try to add a connection:

conn = engine.connect()

I get the following error:

pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'cloudsqlproxy~' (using password: YES)")

Seems odd that engine can be created but no connection can be made to it? Worth noting that any kind of execute using the engine, e.g. engine.execute('select * from table limit 3') will also lead to the error above.

Any thoughts are appreciated. Thanks for your time

Cheers, Dave

Which IAM roles are assigned to the Cloud Functions service account? — John Hanley
– John Hanley, Commented Jul 16, 2021 at 20:11

Drew · Accepted Answer · 2021-07-16 23:51:33Z

1

When the create_engine method is called, it creates the necessary objects, but does not actually attempt to connect to the database.

Per the SQLAlchemy documentation:

[create_engine] creates a Dialect object tailored towards [the database in the connection string], as well as a Pool object which will establish a DBAPI connection at [host]:[port] when a connection request is first received.

The error message you receive from MySQL is likely due to the fact that the special user account for the Cloud SQL Auth proxy has not yet been created.

You need to create a user in CloudSQL (MySQL) ('[USER_NAME]'@'cloudsqlproxy~%' or '[USER_NAME]'@'cloudsqlproxy~[IP_ADDRESS]' ).

answered Jul 16, 2021 at 23:51

Drew

6,7275 gold badges48 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Yang Over a year ago

That's strange considering I'm able to connect to, read, and write to the db through mysql in python. Only difference here is that I'm attempting to do it through a cloud function as opposed to locally...

David Yang Over a year ago

does that change your answer at all or do you think still it's a Cloud SQL Auth proxy issue? The documentation is confusing (aka i'm just stupid), but I'm a bit unsure how to interpret these directions:

To create a user account for Cloud SQL Auth proxy connections, specify the hostname as 'cloudsqlproxy~[IP_ADDRESS]'. You can also use the IP address wildcard, which would result in 'cloudsqlproxy~%'. The full user account name would be

So to create a user account, I just have to specify a hostname? What does that mean?

Collectives™ on Stack Overflow

Google Cloud Function with Google Cloud SQL: Python SQLalchemy engine works, but connection fails

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related