'PGDDLCompiler' object has no attribute '_bind_processors'

[wonderbeyond]

My envs

- Python 3.6.3
- asyncpgsa==0.18.1
- asyncpg==0.12.0
- sqlalchemy==1.1.15

My code sample

from asyncpgsa import PG
from app import app

DB_CONFIG = {
    'host': '172.17.0.3',
    'port': 5432,
    'database': '***',
    'user': 'xtpids',
    'password': '***',
    'min_size': 5,
    'max_size': 10,
}

@app.listener('before_server_start')
async def init_db(*args, **kwargs):
    # Initializing a db singleton before server start
    pg = PG()
    await pg.init(**DB_CONFIG)
    app.db = pg
    return app.db

# Create tables in some script
tables = Base.metadata.tables
for name, table in tables.items():
    create_expr = CreateTable(table)
    await app.db.execute(create_expr)

The exception

...
File "/home/wonder/PyEnvs/xtpids-BKbQCeJj/lib/python3.6/site-packages/asyncpgsa/pgsingleton.py", line 82, in execute
return await conn.execute(*args, **kwargs)
File "/home/wonder/PyEnvs/xtpids-BKbQCeJj/lib/python3.6/site-packages/asyncpgsa/connection.py", line 105, in execute
script, params = compile_query(script, dialect=self._dialect)
File "/home/wonder/PyEnvs/xtpids-BKbQCeJj/lib/python3.6/site-packages/asyncpgsa/connection.py", line 83, in compile_query
params = _get_keys(compiled)
File "/home/wonder/PyEnvs/xtpids-BKbQCeJj/lib/python3.6/site-packages/asyncpgsa/connection.py", line 43, in _get_keys
processors = compiled._bind_processors
AttributeError: 'PGDDLCompiler' object has no attribute '_bind_processors'

My analysis

In sqlalchemy's source, only SQLCompiler has _bind_processors property.
However create_expr.compile(dialect=asyncpgsa.connection._dialect) generates an instance of type PGDDLCompiler(DDLCompiler), of which SQLCompiler isn't a base class.

[nhumrich]

Interesting.. I use alembic for all my table creations and migrations, so I havent tested using sqlalchemy migration methods. Would you be willing to write a PR to fix this?

[thedrow]

I can confirm this problem. Happened to me as well.

Is there a suggested fix I should try?

[thedrow]

create_expr.compile(dialect=asyncpgsa.connection._dialect) now fails with 'PGDialect_pypostgresql' object has no attribute 'dialect'

[nhumrich]

You could try changing the dialect to psycopg dialect (slqalchemys default) and see if that helps:
create_pool(dialect=sa.dialects.postgresql.psycopg2)

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.psycopg2

[nhumrich]

Closing as no more activity. Please reopen with a comment if this still needs to be looked at for some reason. Currently lacking actionable info for me.

[thedrow]

The psycopg2 dialect does not work with asyncpg (at least with asyncpg 0.12.0. I haven't checked other versions).

The problem is the way that psycopg2 outputs query parameters. The default dialect uses :param. psycopg2 uses {param} or something similar.

[nhumrich]

Ive never really used createTable outside of using alembic. So its possible there are just some things that are missing in this library. I would accept a PR that added support for this.

[Pehat]

I've noticed that

query = CreateSchema(MyModel.__table_args__["schema"])
await db.execute(str(query))

works fine to me. So maybe you just need to cast an SQL expression to string.

[WGH-]

str(query) seems to return incorrect results where there are more complex datatypes, like arrays. sa.Column(ARRAY(TEXT)) results in ARRAY (blatantly incorrect, the correct datatype string is text[])

[WGH-]

This snippet seems to work better (at least it compiled ARRAY(TEXT) correctly):

from sqlalchemy.dialects.postgresql.base import PGDialect

for table in models.Base.metadata.sorted_tables:
    query = CreateTable(table)
    query = str(query.compile(dialect=PGDialect()))
    await conn.execute(query)

[WGH-]

DDL is actually broken in many ways.

class Foo(Base):
    __tablename__ = "foo"

    id = sa.Column(TEXT, primary_key=True, nullable=False)
    nodes = sa.Column(ARRAY(TEXT), nullable=True)

async def test_bug():
    pool = await asyncpgsa.create_pool(database="test")
    async with pool.transaction() as conn:
        await conn.execute("DROP TABLE IF EXISTS foo")

        for table in Base.metadata.sorted_tables:
            await conn.execute(CreateTable(table))

will result in the following stack trace:

Traceback (most recent call last):
  File "check_revision.py", line 32, in comain
    await test_bug()
  File "check_revision.py", line 28, in test_bug
    await conn.execute(CreateTable(table))
  File "/tmp/asyncpgsa/asyncpgsa/connection.py", line 89, in execute
    script, params = compile_query(script, dialect=self._dialect)
  File "/tmp/asyncpgsa/asyncpgsa/connection.py", line 60, in compile_query
    compiled_params = sorted(compiled.params.items())
AttributeError: 'NoneType' object has no attribute 'items'

This error is trivially fixable (check for None), but even after the fix the error originally reported in this issue will manifest.

Moreover, DDL is used to be not broken. The snippet above used to work as it is in asyncpgsa==0.13.0. I bisected the history and found the first bad commit: 75078a4. (here's bisect scripts for reference: https://gist.github.com/WGH-/cb61522716f2555901855121fd35c458)

[nhumrich]

Thats weird... Not sure why that commit would have broken DDL