CODEWITHSUNDEEP
pythonpostgresqlgoogle-app-enginepsycopg2google-cloud-sql
Swathi Rai
Swathi Rai

Reputation: 115

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

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.

deployment error

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"

Upvotes: 0

Views: 878

Answers (3)

Doug Mahugh
Doug Mahugh

Reputation: 624

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

Upvotes: 1

Swathi Rai
Swathi Rai

Reputation: 115

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.

Upvotes: 2

Stefan G.
Stefan G.

Reputation: 938

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.

Upvotes: 1

Related Questions