Python Bookshelf App giving error on deployment to GCP using postgresql cloud SQL DB

Question

I am deploying the GCP Python Bookshelf application using postgresSQL cloud SQL DB. My app is not running properly. The error log is as seen in the image below.

I have done all the required changes in app.yaml, requirements.txt and config.py files. The project is running fine locally using cloud-sql-proxy but not working properly in the deployed version. Could someone guide me how to resolve this?

My env variables in app.yaml are:

runtime: python
env: flex
entrypoint: gunicorn -b :$PORT main:app

runtime_config:
  python_version: 3

#[START cloudsql_settings]
beta_settings:
    cloud_sql_instances: "<my_project_id>:<region_name>:<cloudsql_instance>"
    # cloud_sql_instances: "your-cloudsql-connection-name"
#[END cloudsql_settings]

The config.py is:

import os

DATA_BACKEND = 'cloudsql'
PROJECT_ID = '<my_project_id>'
CLOUDSQL_USER = '<username>'
CLOUDSQL_PASSWORD = '<password>'
CLOUDSQL_DATABASE = '<db_name>'
CLOUDSQL_CONNECTION_NAME = '<my_project_id>:<region_name>:<cloudsql_instance>'
LOCAL_SQLALCHEMY_DATABASE_URI = (
    'postgresql+psycopg2://{user}:{password}@127.0.0.1:3306/{database}').format(
        user=CLOUDSQL_USER, password=CLOUDSQL_PASSWORD,
        database=CLOUDSQL_DATABASE)
LIVE_SQLALCHEMY_DATABASE_URI = (
    'postgres://{user}:{password}@localhost/{database}'
    '?unix_socket=/cloudsql/{connection_name}').format(
        user=CLOUDSQL_USER, password=CLOUDSQL_PASSWORD,
        database=CLOUDSQL_DATABASE, connection_name=CLOUDSQL_CONNECTION_NAME)

if os.environ.get('GAE_INSTANCE'):
    SQLALCHEMY_DATABASE_URI = LIVE_SQLALCHEMY_DATABASE_URI
else:
    SQLALCHEMY_DATABASE_URI = LOCAL_SQLALCHEMY_DATABASE_URI

The requirements.txt is:

Flask>=1.0.0
google-cloud-datastore==1.7.1
gunicorn==19.9.0
Flask-SQLAlchemy==2.3.2
psycopg2==2.8.4
PyMySQL==0.9.2
Flask-PyMongo>=2.0.0
oauth2client==4.1.2
PyMongo==3.7.2
six==1.11.0

The detailed error is:

Traceback (most recent call last):
  File "/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2275, in _wrap_pool_connect
    return fn()
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 363, in connect
    return _ConnectionFairy._checkout(self)
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
    self._dec_overflow()
  File "/env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
    return self._create_connection()
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/env/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/env/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 481, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/env/lib/python3.6/site-packages/psycopg2/__init__.py", line 125, in connect
    dsn = _ext.make_dsn(dsn, **kwargs)
  File "/env/lib/python3.6/site-packages/psycopg2/extensions.py", line 174, in make_dsn
    parse_dsn(dsn)
psycopg2.ProgrammingError: invalid dsn: invalid connection option "unix_socket"

What is the contents of your config.py file as specified here? Specifically what is [DATA_BACKEND] and [CLOUDSQL_CONNECTION_NAME]? — Dustin Ingram
– Dustin Ingram, Commented Nov 6, 2019 at 16:53
I think LIVE_SQLALCHEMY_DATABASE_URI = ( 'postgres://{user}:{password}@localhost/{database}' '?host=/cloudsql/{connection_name}'). You should replace unix_socket with host — marian.vladoi
– marian.vladoi, Commented Nov 7, 2019 at 9:41

Swathi Rai · Accepted Answer · 2019-11-07 11:09:17Z

2

LIVE_SQLALCHEMY_DATABASE_URI = ( 'postgres://{user}:{password}@localhost/{database}' '?host=/cloudsql/{connection_name}').

I had to replace unix_socket with host in LIVE_SQLALCHEMY_DATABASE_URI. Also only postgres was required.

I don't know if it is required but I also added Cloud SQL Client role to my app engine service account. Thanks all for your help guys.

answered Nov 7, 2019 at 11:09

Swathi Rai

1151 silver badge10 bronze badges

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

1 Comment

Stefan G. Over a year ago

Glad you made it work, I was about to link you to this

Stefan G. · Accepted Answer · 2019-11-06 14:48:22Z

1

Are you using postgresql+psycopg2://username:password@/db?unix_socket=/cloudsql/project-name:region:dbinstance?

Try using postgres:// instead of postgresql+psycopg2//:

The more info you provide us, about your connection or anything the more we can attempt to help you. Is this the tutorial you based yourself on?

EDIT:

Hey,if you double checked that every connection is OK, please add Cloud SQL Client role to your app engine service account and let me know if that fixed it for you.

edited Nov 6, 2019 at 14:48

answered Nov 6, 2019 at 12:49

Stefan G.

9385 silver badges10 bronze badges

5 Comments

Swathi Rai Over a year ago

Let me try that. Yes I used this tutorial. Also this is the link for Bookshelf app cloud sql tutorial: cloud.google.com/python/getting-started/using-cloud-sql

Swathi Rai Over a year ago

[stackoverflow.com/users/11928097/stefan-g] I tried your solution. Still facing the same issue.

Stefan G. Over a year ago

Can you edit your question and add in your config.py without revealing any sensible information please?

Stefan G. Over a year ago

Have a look at the edit in my answer. Hope that helps.

Swathi Rai Over a year ago

I added the role as well, still facing same issue.

Doug Mahugh · Accepted Answer · 2019-11-08 15:03:43Z

1

FYI, this syntax works for me on an App Engine deployment that uses a PostgreSQL instance in Cloud SQL, using the pg8000 driver:

connection_string = sqlalchemy.engine.url.URL(
   drivername="postgres+pg8000",
   username=username,
   password=password,
   database=database,
   query="unix_sock": "/cloudsql/{}/.s.PGSQL.5432".format(instance),
)

connection = sqlalchemy.create_engine(
    connection_string,
    pool_size=50,
    max_overflow=2,
    pool_timeout=30,
    pool_recycle=1800,
)

answered Nov 8, 2019 at 15:03

Doug Mahugh

6241 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

Python Bookshelf App giving error on deployment to GCP using postgresql cloud SQL DB

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related