`TypeError: argument 2 must be a connection, cursor or None` in Psycopg2

Question

I have a heroku pipeline set up, and have just enabled review apps for it. It is using the same codebase as my staging and production apps, same settings files and everything.

When the review app spins up, it can connect to the created DB and run migrations. When I try to connect to the app in the browser, I get

`TypeError: argument 2 must be a connection, cursor or None` in `psycopg2/_json.py, register_json:139`

Top of stack is:

`django.contrib.sites.models._get_site_by_id`.

I've attached the Opbeat output of the error frame at the bottom of this post.

Settings file is linked.

When I set DEBUG=True, everything works fine. Which might suggest an ALLOWED_HOSTS issue, but when I set ALLOWED_HOSTS to '*' with DEBUG=False, it still errors?

What is wrong with my setup? This works in staging, and production, but not the review apps.

Answer 1

tldr

# example borked
engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: 'asdf')
# example works
engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: conn)

I think this might be an error from one of the following:

• psycopg2.extensions.register_type

• _connect

• cpython

  • static PyObject *psyco_register_type
  • static PyObject * psyco_connect

examples

• https://github.com/psycopg/psycopg2/search?q=register_type&unscoped_q=register_type

Specifcally the error can be raised from :

psycopg2.

_ipaddress.py
_json.py
_psycopg.cpython-37m-darwin.so
_range.py
extensions.py
extras.py

in my case:

_ext.register_type(_ext.UUID, conn_or_curs)

Apparently this is what it does:

• https://github.com/psycopg/psycopg2/blob/master/doc/src/extensions.rst#database-types-casting-functions

the code in my case which caused the issue was the create_engine keyword creator=get_connection in the following example:

 from psycopg2.pool import ThreadedConnectionPool
 from contextlib import contextmanager
 import sqlalchemy
 conn_string = "host='127.0.0.1' dbname='postgres' user='someuser' password='somepassword'"
 top = ThreadedConnectionPool(1, 250, conn_string)

 @contextmanager
 def get_connection():
     try:
         connection = top.getconn()
         yield connection
     finally:
         top.putconn(connection)

 @contextmanager
 def get_cursor(commit=False):
     with get_connection() as connection:
         cursor = connection.cursor(
             cursor_factory=psycopg2.extras.RealDictCursor)
         try:
             yield cursor
             if commit:
                 connection.commit()
         finally:
             cursor.close()

 engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=get_connection)

 @contextmanager
 def get_sqlalchemy_engine():
     yield engine.connect()

what caused the issue:

with get_sqlalchemy_engine() as engine:
    pd.DataFrame([1]).to_sql('asdf', engine, if_exists='replace')

And what fixed the issue:

@contextmanager
def get_sqlalchemy_engine():
    with get_connection() as conn:
        try:
            engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: conn)
            # engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: 'asdf')
            yield engine
        finally:
            engine.dispose()

further research indicates the following:

JSON = new_type((oid, ), name, typecast_json)
if array_oid is not None:
    JSONARRAY = new_array_type((array_oid, ), "%sARRAY" % name, JSON)
else:
    JSONARRAY = None

return JSON, JSONARRAY

basically conn_or_curs is not a connection or cursor, but rather some other thing,

register_type(JSON, not globally and conn_or_curs or None)
register_type(JSONARRAY, not globally and conn_or_curs or None)

• https://github.com/psycopg/psycopg2/blob/f947c0e6be1d2c3ea8d2d8badf683b95bd213444/psycopg/psycopgmodule.c#L189
• https://github.com/psycopg/psycopg2/blob/f947c0e6be1d2c3ea8d2d8badf683b95bd213444/psycopg/psycopgmodule.c#L260