Using psycopg2 directly on Google AppEngine

Question

When using Google Appengine Flexible + Python, how to use Psycopg2 directly (without SQLAlchemy) to access a CloudSQL PostgreSQL database?

Answer 1

Hello myselfhimself,

Here is a solution:

1. in your app.yaml, add an environment variable, imitating Google Appengine Flexible Python CloudSQL documentation's SQLAlchemy's URI but without the psycopg2+ prefix:

env_variables:
    PSYCOPG2_POSTGRESQL_URI: postgresql://user:password@/databasename?host=/cloudsql/project-name:region:database-instance-name

2. in any python file to be deployed and run, pass that environment variable to psycopg2's connect statement directly. This leverages psycopg2.connect's ability to pass the URI directly to the psql client library (this might not work with older PostgreSQL versions..).

import os
import psycopg2
conn = psycopg2.connect(os.environ['PSYCOPG2_POSTGRESQL_URI'])

3. when working locally with the google cloud proxy tool, make sure you set the URI environment variable first, if your local server is not aware of app.yaml:

export PSYCOPG2_POSTGRESQL_URI="postgresql://user:password@/databasename?host=/cloudsql/project-name:region:database-instance-name"
./cloud_sql_proxy -instances=project-name:region:database-instance-name=tcp:5432
#somewhat later:
python myserver.py

I hope it will work for your too : )