How to setup psycopg2 with Google App Engine PostgreSQL database

Question

I have an application that is being run on Google's App Engine and I want it to use the associated PostgreSQL database. I'm using psycopg2 to help me with my SQL queries. However I am not sure how I can set up the connection. This is what I currently have:

con = psycopg2.connect(
    host=HOST_NAME, # the IP address of the SQL database
    database=DATABASE_NAME, # the name of the database (I'm using the default, so this is "postgres"
    user=USER_NAME, # just the user name that I created
    password=PASSWORD # the password associated to that user
)

However, when I try to make a request, I get the error psycopg2.OperationalError: could not connect to server: Connection timed out when creating this connection. Is there something I am missing?

Andrei Cusnir · Accepted Answer · 2019-03-28 10:35:56Z

1

It is a little bit tricky, but here is what worked for me. I will help you to set up the Quickstart App Engine with psycopg2 and after that you will get the idea.

Use the Quickstart for Python in the App Engine Flexible Environment documentation to set up and deploy your app.

Use the Connecting from App Engine documentation to connect to your App Engine app to the Cloud SQL Postgre SQL.

I have did slightly modifications in order to make that work:

In app.yaml add:

beta_settings:
  cloud_sql_instances: [INSTANCE_CONNECTION_NAME]=tcp:5432
#[INSTANCE_CONNECTION_NAME] = [PROJECT_NAME]:[INSTANCE_ZONE]:[INSTANCE_NAME]
#[INSTANCE_CONNECTION_NAME] can be found at Google Cloud Console Cloud SQL's instance page, under "Instance connection name".

In requirements.txt add:

psycopg2
psycopg2-binary

In main.py add:

@app.route('/connect')
def connect():
    try:
        #host='172.17.0.1' is the defult IP for the docker container that it is being created during the deployment of the App Engine
        conn = psycopg2.connect("dbname='postgres' user='postgres' host='172.17.0.1' password='test'")
        return "Connection was established!"
    except:
        return "I am unable to connect to the database"

Use the gcloud app deploy command to deploy your app.

After the deployment, use the gcloud app browse command to open the app in the browser.

When accessing the link https://[PROJECT_ID].appspot.com/connect It should respond with Connection was established!

answered Mar 28, 2019 at 10:35

Andrei Cusnir

2,8131 gold badge17 silver badges21 bronze badges

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

2 Comments

kbunarjo Over a year ago

i tried the steps you stated here but im still getting a connection timeout error. should the host be the public IP address of the sql instance or is it always going to be 172.17.0.1?

Andrei Cusnir Over a year ago

I have tried all the steps above and they worked for me. Yes it is going to be always 172.17.0.1 because you are using the Cloud SQL proxy setup in the app.yaml file. Therefore, you have to connect to the default IP address and port of the container that it is being generated by default. I would suggest start the same process again from the beginning, creating a new SQL instance with a new App Engine Python app. And then see the differences .

Collectives™ on Stack Overflow

How to setup psycopg2 with Google App Engine PostgreSQL database

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related