Configuring pre_ping causes exception on error with psycopg2

[an...@cabine.org]

Hello.

I'm using a connection pool backed by SQLAlchemy (1.2.1) to access a Postgresql (9.6) server using the psycopg2 (2.7.3.2) driver. I have the following pool configuration options set:

from sqlalchemy.dialects import postgresql

DATABASE_POOL_ARGS = {

    'max_overflow': 0,

    'pool_size': 3,

    'recycle': 3600,

    'timeout': 10,

    'pre_ping': True,

    'dialect': postgresql.dialect(),

}

Whenever an error occurs during the initial ping I get an exception:

'NoneType' object has no attribute 'Error'

on line 477 of file sqlalchemy/engine/default.py which is:

except self.dbapi.Error as err:

"self" in this case is an object of type "sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2". Instances of this class do not seem to have the "dbapi" attribute, only the class itself has it. Should I be passing the class and not an instance for the "dialect" option? I've tried that and got a different kind of error:

  File "//anaconda/envs/opp/lib/python3.6/site-packages/sqlalchemy/pool.py", line 812, in _checkout

    result = pool._dialect.do_ping(fairy.connection)

TypeError: do_ping() missing 1 required positional argument: 'dbapi_connection'

Any help would be appreciated, thanks.

André

[Mike Bayer]

On Tue, Feb 6, 2018 at 10:14 AM, <an...@cabine.org> wrote:
> Hello.
>
> I'm using a connection pool backed by SQLAlchemy (1.2.1) to access a
> Postgresql (9.6) server using the psycopg2 (2.7.3.2) driver. I have the
> following pool configuration options set:
>
> from sqlalchemy.dialects import postgresql
> DATABASE_POOL_ARGS = {
> 'max_overflow': 0,
> 'pool_size': 3,
> 'recycle': 3600,
> 'timeout': 10,
> 'pre_ping': True,
> 'dialect': postgresql.dialect(),
> }
>
> Whenever an error occurs during the initial ping I get an exception:
>
> 'NoneType' object has no attribute 'Error'

it is unusual for people to use the pool without an engine like that.
You can always get a pool by creating an engine normally and then
grabbing engine.pool. When the engine is created one of the things
it does is load up the DBAPI for the dialect. if you want to do that
manually, it looks like:

from sqlalchemy import pool
from sqlalchemy.dialects import postgresql
import psycopg2

dialect = postgresql.dialect(dbapi=psycopg2)

DATABASE_POOL_ARGS = {
'max_overflow': 0,
'pool_size': 3,
'recycle': 3600,
'timeout': 10,
'pre_ping': True,

'dialect': dialect
}


def creator():
return psycopg2.connect(user='scott', password='tiger', database='test')

p = pool.QueuePool(creator, **DATABASE_POOL_ARGS)

c = p.connect()

conn = c.connection
c.close()

conn.close()
p.connect()

>
> on line 477 of file sqlalchemy/engine/default.py which is:
>
> except self.dbapi.Error as err:
>
> "self" in this case is an object of type
> "sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2". Instances of
> this class do not seem to have the "dbapi" attribute, only the class itself
> has it. Should I be passing the class and not an instance for the "dialect"
> option? I've tried that and got a different kind of error:
>
> File "//anaconda/envs/opp/lib/python3.6/site-packages/sqlalchemy/pool.py",
> line 812, in _checkout
> result = pool._dialect.do_ping(fairy.connection)
> TypeError: do_ping() missing 1 required positional argument:
> 'dbapi_connection'
>
> Any help would be appreciated, thanks.
>
> André
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[André Cruz]

Hello Mike.

On 6 Feb 2018, at 16:24, Mike Bayer <mik...@zzzcomputing.com> wrote:

it is unusual for people to use the pool without an engine like that.
You can always get a pool by creating an engine normally and then
grabbing engine.pool.   When the engine is created one of the things
it does is load up the DBAPI for the dialect.  if you want to do that
manually, it looks like:

Thanks for the help. I'm using the package django_postgrespool2 so I'm currently not in control of how the pool is instantiated. The package does this:

In https://github.com/lcd1232/django-postgrespool2/blob/master/django_postgrespool2/base.py

from sqlalchemy.pool import manage

...
POOL_SETTINGS = 'DATABASE_POOL_ARGS'
pool_args = getattr(settings, POOL_SETTINGS, {})
db_pool = manage(Database, **pool_args)

So it's only using SQLAlchemy''s pool functionality to manage Django DB connections. I guess I just have to pass the "dbapi=psycopg2" parameter when creating the dialect?

Best regards,

André

[Mike Bayer]

yes or report it as a bug over to that project, they can just fix it.


>
> Best regards,

[André Cruz]

Hello Mike.

On 6 Feb 2018, at 16:36, Mike Bayer <mik...@zzzcomputing.com> wrote:

So it's only using SQLAlchemy''s pool functionality to manage Django DB
connections. I guess I just have to pass the "dbapi=psycopg2" parameter when
creating the dialect?

yes or report it as a bug over to that project, they can just fix it.

Apparently this is not enough. The ping command will throw a "django.db.utils.OperationalError", which is not caught by the except clause "except self.dbapi.Error as err:".

It seems I will not be able to use the "pre_ping" functionality with this pool of Django DB connections.

Can I just subclass postgresql.dialect and override the do_ping() method or will that leave me with even more problems?

Thank you and best regards,

André

[Mike Bayer]

On Tue, Feb 6, 2018 at 1:36 PM, André Cruz <an...@cabine.org> wrote:
> Hello Mike.
>
>
> On 6 Feb 2018, at 16:36, Mike Bayer <mik...@zzzcomputing.com> wrote:
>
> So it's only using SQLAlchemy''s pool functionality to manage Django DB
> connections. I guess I just have to pass the "dbapi=psycopg2" parameter when
> creating the dialect?
>
>
> yes or report it as a bug over to that project, they can just fix it.
>
>
> Apparently this is not enough. The ping command will throw a
> "django.db.utils.OperationalError", which is not caught by the except clause
> "except self.dbapi.Error as err:".

this sounds like some kind of integration package so perhaps they can
catch and re-raise that? but yes, pool_pre_ping is designed with
SQLAlchemy dialects in mind :)

>
> It seems I will not be able to use the "pre_ping" functionality with this
> pool of Django DB connections.
>
> Can I just subclass postgresql.dialect and override the do_ping() method or
> will that leave me with even more problems?
>
> Thank you and best regards,
> André
>