I’m not a Google Cloud Platform expert, and this isn’t a tutorial. I’m just hoping that if people run into the same errors I ran into and search for those exact error messages, that they’ll find this blog post, and maybe it’ll be helpful to them.
What I found
Making the connection
Google’s documentation on connecting a Cloud Function to Cloud SQL isn’t super straightforward. When I tried to implement their examples, I came across all sorts of errors like:
sqlalchemy.exc.InterfaceError: (pg8000.exceptions.InterfaceError) communication error
(Background on this error at: https://sqlalche.me/e/20/rvf5)
File "/workspace/main.py", line 58, in postgres_connect sqlalchemy.engine.url.URL.create(AttributeError: type object 'URL' has no attribute 'create'"
I oddly found that using cloud-sql-proxy would allow the “Cloud” Function to run properly locally (even without a host specified—I guess because the assumption is that the host is 127.0.0.1, and that’s what the
cloud-sql-proxy connects you to?).
I ran into issues connecting with Unix sockets (and it wasn’t because the socket path length was longer than 108 characters, which the documentation already warns about).
Ultimately, (at the urging of a colleague) I ditched trying to use Unix connectors, and I went with the Cloud SQL connector approach outlined in Connect from Cloud Functions, and that worked.
requirements.txt file needed:
Using the connection
I didn’t know a ton about SQL Alchemy, so I had to do a bit of searching around and trial-and-error to get a useful connection.
So, first of all, you get the
pool from the code Google provides you. What do you do with the
I assigned it to a variable called
engine, but then I had to run
conn = engine.connect() in order to make the connection useful.
Even then, it wasn’t exactly clear how to make an actual SQL query. If you have a SQL query (e.g.,
SELECT field1, field2, field3 FROM table WHERE somecriteria = somevalue;) and put that into a variable like
query, you can’t just run
conn.execute(query) on your query, because it will complain about it not being able to figure out the text. So you actually have to use
query = sqlalchemy.text(original_query) before you can run something like
output = connect.execute(query). After that, the output should be an easy list to iterate through.