aio-libs / aio-libs/aiopg

improper handling of DDL in aiopg.sa

Aberta
#636 0 comentários 0 reações 0 responsáveis Ver no GitHub
Linguagem predominante
Python
Estrelas
1.4k
Forks
170
Métricas de merge de PRs
Nenhum PR com merge em 30d

Descrição

Python 3.7.3 (default, Apr 3 2019, 19:16:38)
aiopg: 1.0.0

Using some DDLs with aiopg fails. Same thing works correctly when using plain SQLAlchemy or Alchimia (Twisted).

```
import asyncio
from aiopg.sa import create_engine
import sqlalchemy as sa

sp = """CREATE OR REPLACE FUNCTION bogus(target regclass)
RETURNS void as $$
DECLARE
query text;
BEGIN
query = 'SELECT 1';
RAISE NOTICE '%%', query;
EXECUTE query;
END;
$$
language 'plpgsql';"""

async def main():
engine = await create_engine(host='127.0.0.1',
user='test',
password='test',
database='test')
async with engine.acquire() as conn:
await conn.execute(sa.DDL(sp))

loop = asyncio.get_event_loop()
loop.run_until_complete(main())
```

```
$ python bogus.py
Traceback (most recent call last):
File "bogus.py", line 27, in
loop.run_until_complete(main())
File "/usr/lib/python3.7/asyncio/base_events.py", line 579, in run_until_complete
return future.result()
File "bogus.py", line 24, in main
await conn.execute(sa.DDL(sp))
File ".venv/lib/python3.7/site-packages/aiopg/sa/connection.py", line 120, in _execute
await cursor.execute(str(compiled), post_processed_params[0])
File ".venv/lib/python3.7/site-packages/aiopg/cursor.py", line 113, in execute
await self._conn._poll(waiter, timeout)
File ".venv/lib/python3.7/site-packages/aiopg/connection.py", line 207, in _poll
await asyncio.wait_for(self._waiter, timeout, loop=self._loop)
File "/usr/lib/python3.7/asyncio/tasks.py", line 442, in wait_for
return fut.result()
File ".venv/lib/python3.7/site-packages/aiopg/connection.py", line 106, in _ready
state = self._conn.poll()
psycopg2.errors.SyntaxError: too many parameters specified for RAISE
CONTEXT: compilation of PL/pgSQL function "bogus" near line 6

.venv/lib/python3.7/site-packages/aiopg/pool.py:310: ResourceWarning: Unclosed 1 connections in
```

Tracked down to [_execute (aiopg/sa/connection.py#L72)](https://github.com/aio-libs/aiopg/blob/ea9c039682a2354fdb4feb09c23781d32bbf0611/aiopg/sa/connection.py#L72) where the code basically does something like this:
```
import sqlalchemy as sa
import sqlalchemy.dialects.postgresql as p

sp = """CREATE OR REPLACE FUNCTION bogus(target regclass)
RETURNS void as $$
DECLARE
query text;
BEGIN
query = 'SELECT 1';
RAISE NOTICE '%%', query;
EXECUTE query;
END;
$$
language 'plpgsql';"""

compiled = p.base.PGDDLCompiler(dialect=p.psycopg2.PGDialect_psycopg2(),
statement=sa.DDL(sp))
print(str(compiled))
```
result:
```
CREATE OR REPLACE FUNCTION bogus(target regclass)
RETURNS void as $$
DECLARE
query text;
BEGIN
query = 'SELECT 1';
RAISE NOTICE '%%', query;
EXECUTE query;
END;
$$
language 'plpgsql';
```
Notice the double '%%' - this should be already converted to '%'. Result of _str(compiled)_ is passed directly to [cursor.execute (aiopg/sa/connection.py#L120)](https://github.com/aio-libs/aiopg/blob/ea9c039682a2354fdb4feb09c23781d32bbf0611/aiopg/sa/connection.py#L120). DDLs should be passed to DDL constructor with doubled '%' - see [sqlalchemy/ddl.py#259](https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/sql/ddl.py#L259)

Guia de contribuição

Abrir o guia de contribuição

Avaliação

Esta issue ainda não foi avaliada.

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.