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](https://cloud.google.com/python/getting-started/using-cloud-sql)? Specifically what is `[DATA_BACKEND]` and `[CLOUDSQL_CONNECTION_NAME]`? — Dustin Ingram, Nov 06 '19 at 16:53
@DustinIngram Yes it is as mentioned in the link. DATA_BACKEND is cloudsql & CLOUDSQL_CONNECTION_NAME is my cloudsql_instance name. Edited the question as well with required data. Please have a look and help me out. — Swathi Rai, Nov 07 '19 at 09:15
I think LIVE_SQLALCHEMY_DATABASE_URI = ( 'postgres://{user}:{password}@localhost/{database}' '?host=/cloudsql/{connection_name}'). You should replace unix_socket with host — marian.vladoi, Nov 07 '19 at 09:41

score 2 · Accepted Answer · answered Nov 07 '19 at 11:09

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 07 '19 at 11:09

Swathi Rai

95
8

Glad you made it work, I was about to link you to [this](https://stackoverflow.com/questions/54967660/connect-to-a-database-over-a-unix-socket-using-sqlalchemy) – Stefan G. Nov 07 '19 at 11:14

Stefan G. · Answer 2 · 2019-11-06T14:48:22.577

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 06 '19 at 14:48

answered Nov 06 '19 at 12:49

Stefan G.

890
5
10

Let me try that. Yes I used this tutorial. Also this is the link for Bookshelf app cloud sql tutorial: https://cloud.google.com/python/getting-started/using-cloud-sql – Swathi Rai Nov 06 '19 at 13:13
[https://stackoverflow.com/users/11928097/stefan-g] I tried your solution. Still facing the same issue. – Swathi Rai Nov 06 '19 at 13:30
Can you edit your question and add in your config.py without revealing any sensible information please? – Stefan G. Nov 06 '19 at 14:33
Have a look at the edit in my answer. Hope that helps. – Stefan G. Nov 06 '19 at 14:49
I added the role as well, still facing same issue. – Swathi Rai Nov 07 '19 at 09:16

score 1 · Answer 3 · answered Nov 08 '19 at 15:03

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,
)

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

3 Answers3