Connecting to Cloud SQL Postgres from App engine with pyscopg2 only

user_7758963 0
Viewed 2761

I can not figure out what is going wrong here.

I have developed a Python/Flask web app using pure psycopg2. initially using a local instance of Postgres, then I started a Postgres Cloud SQL on google and connected to that using 

   conn = psycopg2.connect(database="********", user = "********", password = "**********", host = "104.***.***.**", port = "5432")

Now I need to deploy the app but from what I understand from the documentation, I can't just use the IP. However the documentation example is all based on SQLAlchamy which I have not used before and prefer not to rewrite my app in it.

from what I understand, Cloud SQL loads the instance in a proxy based on the entry in app.yaml specifying the instance. it does so in a. unix socket. so I tried 

conn = psycopg2.connect(database="********", user = "********", password = "**********", host = "/cloudsql/[my instance name here]")

I tried the cloud console, and used the development proxy there loaded to a unix socket and previewed the app and it seemed to work just fine. but when I deployed this working version. it still wouldn't connect to the database.

So what am I doing wrong here

psycopg2 google-app-engine postgresql python
1 Answers

I was able to figure out the problem. It was quite a learning process for an amateur like me but anyway. My assumption was correct. Cloud SQL loads the instance in a proxy based on the entry in app.yaml specifying the instance. it does so in a unix socket (for amateurs like me, that is a path to a folder not an IP). so the connection string should look like this

conn = psycopg2.connect(database="********", user = "********", password = "**********", host = "/cloudsql/my instance name here")

Note the absence of port setting as that would just confuse psycopg2 and the underlying Postgres library.

However, the reason this wasn't working before was because the Cloud SQL API was not enabled. when I read the documentation at first, I thought that just mean to set up Cloud SQL database within your project which I already did. I was wrong, it was a separate step and basically consist of clicking enable in the API section of your project. once that was enabled. everything worked fine.

user_77589630
Related