Comments (23)
The proper fix is dropping aiopg.sa
subsystem at all.
You can 'apply' this fix right now by just switching from aiopg.sa
usage to sqlalchemy in async mode.
Years ago, when sqlalchemy didn't support async, aiopg.sa
made a value.
Now it is just garbage, please use upstream.
from aiopg.
- Second happens due to adding caching layer to sqlalchemy 1.4.2 https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#all-in-expressions-render-parameters-for-each-value-in-the-list-on-the-fly-e-g-expanding-parameters
In order to use cache effectively literal values are precompiled to [POSTCOMPILE_\d+] and substituted before execution, after cache lookup
from aiopg.
Not at the moment. Need to get familiar with changes introduced in new SQLAlchemy release
from aiopg.
Hi ✋
Could you explain a little more about the crazy incompatibility errors? maybe some example or something like that
from aiopg.
@Arfey excuse my overly simplified explanation - provided more details in an edit. I hope it helps.
from aiopg.
@krkd thanks. Do you have any ideas how to fix it?
from aiopg.
@krkd feel free to raise a PR 😉
from aiopg.
@Velikolay @Arfey @krkd Could you try latest beta please(with fix from @AVOstap, 1.3.0b1) and confirm that it works for you?
from aiopg.
@Pliner Hi! I get the same error on 1.3.0b3.
async with self.engine.acquire() as conn:
async with conn.execute(query, params) as proxy:
rows = await proxy.fetchall()
rows[0].items()
Gets me
def __getitem__(self, key):
try:
processor, obj, index = self._keymap[key]
except KeyError:
processor, obj, index = self._result_proxy._key_fallback(key)
# Do we need slicing at all? RowProxy now is Mapping not Sequence
# except TypeError:
# if isinstance(key, slice):
# l = []
# for processor, value in zip(self._processors[key],
# self._row[key]):
# if processor is None:
# l.append(value)
# else:
# l.append(processor(value))
# return tuple(l)
# else:
# raise
if index is None:
> raise exc.InvalidRequestError(
f"Ambiguous column name {key!r} in result set! "
f"try 'use_labels' option on select statement."
)
E aiopg.sa.exc.InvalidRequestError: Ambiguous column name None in result set! try 'use_labels' option on select statement.
from aiopg.
@Pliner Hi! I get the same error on 1.3.0b3.
async with self.engine.acquire() as conn: async with conn.execute(query, params) as proxy: rows = await proxy.fetchall() rows[0].items()
Gets me
def __getitem__(self, key): try: processor, obj, index = self._keymap[key] except KeyError: processor, obj, index = self._result_proxy._key_fallback(key) # Do we need slicing at all? RowProxy now is Mapping not Sequence # except TypeError: # if isinstance(key, slice): # l = [] # for processor, value in zip(self._processors[key], # self._row[key]): # if processor is None: # l.append(value) # else: # l.append(processor(value)) # return tuple(l) # else: # raise if index is None: > raise exc.InvalidRequestError( f"Ambiguous column name {key!r} in result set! " f"try 'use_labels' option on select statement." ) E aiopg.sa.exc.InvalidRequestError: Ambiguous column name None in result set! try 'use_labels' option on select statement.
It should be fixed by #870.
from aiopg.
@Pliner Hi, I don't think it is fixed. The PR fixes an issue with string_or_unprintable
, but doesn't fix the my example. I've just tested it on aiopg 1.3.2b1 and sqlalchemy 1.4.20, and the issue persists.
from aiopg.
@WouldYouKindly Sad news.
Could you create a minimal example that reproduces the issue, please?
I've updated a lot of services in our company and 1.3.1 works fine. It looks like we use aiopg+sqlalchemy in a bit different way.
from aiopg.
@Pliner it seems that apply_labels
is the culprit. We have to use it when joining two tables, as they have the same id
column. The following example doesn't throw any errors if I remove apply_labels
.
import pytest
import sqlalchemy
from aiopg.sa import InvalidRequestError
from sqlalchemy import select, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.pool import NullPool
Base = declarative_base()
class Model(Base):
__tablename__ = "sa_tbl"
id = Column(Integer, primary_key=True)
code = Column(String(50))
@pytest.mark.asyncio
async def test_apply_labels():
engine = sqlalchemy.create_engine(dsn)
async with engine.acquire() as conn:
await conn.execute(
"CREATE TABLE sa_tbl (id serial primary key, code text)"
)
await conn.execute(
"insert into sa_tbl (code) values ('a')"
)
query = select(Model).apply_labels()
async with conn.execute(query) as proxy:
rows = await proxy.fetchall()
with pytest.raises(InvalidRequestError):
dict(rows[0])
with pytest.raises(InvalidRequestError):
for _, __ in rows[0].items():
pass
with pytest.raises(AttributeError):
rows[0].sa_tbl_code
(You have to provide your own dsn, but other than that the example should work)
from aiopg.
I have the same error. Simple snippet to reproduce it:
import asyncio
import logging
import sqlalchemy as sa
from aiopg.sa import create_engine
logging.basicConfig(level=logging.INFO)
metadata = sa.MetaData()
tbl = sa.Table('tbl', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('val', sa.String(255)))
dsn = ''
async def main():
async with create_engine(dsn, echo=True) as engine:
async with engine.acquire() as conn:
await conn.execute('DROP TABLE IF EXISTS tbl')
await conn.execute('''CREATE TABLE tbl (
id serial PRIMARY KEY,
val varchar(255))''')
await conn.execute('INSERT INTO tbl (val) VALUES (\'test1\'), (\'test2\')')
query = sa.select((tbl,), use_labels=True)
async for row in conn.execute(query):
print(row)
asyncio.run(main())
Running with SQLAlchemy 1.3.24:
INFO:aiopg:SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
INFO:aiopg:None
INFO:aiopg:SELECT tbl.id AS tbl_id, tbl.val AS tbl_val
FROM tbl
INFO:aiopg:{}
(1, 'test1')
(2, 'test2')
Running with SQLAlchemy 1.4.20:
INFO:aiopg:SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
INFO:aiopg:None
INFO:aiopg:SELECT tbl.id AS tbl_id, tbl.val AS tbl_val
FROM tbl
INFO:aiopg:{}
Traceback (most recent call last):
File "/home/decaz/workspace/xyz/xyz.py", line 32, in <module>
asyncio.run(main())
File "/home/decaz/.pyenv/versions/3.9.6/lib/python3.9/asyncio/runners.py", line 44, in run
return loop.run_until_complete(main)
File "/home/decaz/.pyenv/versions/3.9.6/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
return future.result()
File "/home/decaz/workspace/xyz/xyz.py", line 29, in main
print(row)
File "/home/decaz/workspace/xyz/.venv/lib/python3.9/site-packages/aiopg/sa/result.py", line 79, in __repr__
return repr(self.as_tuple())
File "/home/decaz/workspace/xyz/.venv/lib/python3.9/site-packages/aiopg/sa/result.py", line 76, in as_tuple
return tuple(self[k] for k in self)
File "/home/decaz/workspace/xyz/.venv/lib/python3.9/site-packages/aiopg/sa/result.py", line 76, in <genexpr>
return tuple(self[k] for k in self)
File "/home/decaz/workspace/xyz/.venv/lib/python3.9/site-packages/aiopg/sa/result.py", line 44, in __getitem__
raise exc.InvalidRequestError(
aiopg.sa.exc.InvalidRequestError: Ambiguous column name None in result set! try 'use_labels' option on select statement.
from aiopg.
@Pliner after some research I guess I've found the root of the error:
Line 174 in 7e4c8ad
elem[2][0]
is an instance of sqlalchemy.sql.compiler._CompileLabel
which in SQLAlchemy 1.3 was inherited from sqlalchemy.sql.visitors.Visitable
but in version 1.4 it is inherited from sqlalchemy.sql.elements.ColumnElement
which is always has the key
attribute and in current case its value is None
so the value of priority_name
will be None
also.
Maybe @zzzeek can help us here with some workaround to make this work with SQLAlchemy 1.4, please?
from aiopg.
it looks like you're peeking in the result map, the format of this map has changed but still has the same information inside of it so just pdb and take a look how to get the info you need.
bigger picture, I've always thought it was a pretty bad idea to copy internal source code from SQLAlchemy and then try to keep it in sync. The way statement execution and results work in 1.4 is completely changed from 1.3, IMO it is not really worth the effort to keep chasing SQLAlchemy internals. aiopg should work on ripping all of that reimplementation out and just creating the async API they want using the approach our own asyncio adapter now uses. You will have vastly less code to deal with, little to no need to track our internal implementations and vastly more future proof.
from aiopg.
Hi, guys!
Have the same error as @decaz
If I set param use_labels
to True then the KeyError
has appear
import logging
import asyncio
from aiopg.sa import create_engine
import sqlalchemy as sa
logging.basicConfig(level=logging.INFO)
metadata = sa.MetaData()
tbl = sa.Table('t1', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('val', sa.String(255)))
async def go():
async with create_engine(user='aiopg',
database='aiopg',
host='127.0.0.1',
password='aiopg', echo=True) as engine:
async with engine.acquire() as conn:
await conn.execute(tbl.insert().values(val='abc'))
async for row in conn.execute(tbl.select(use_labels=True).where(tbl.c.val=='abc')):
print(row.t1_id, row.t1_val)
loop = asyncio.get_event_loop()
loop.run_until_complete(go())
runing with SQLAlchemy-1.4.25 and aiopg-1.3.1 I have got the following:
INFO:aiopg:SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
INFO:aiopg:None
INFO:aiopg:INSERT INTO t1 (val) VALUES (%(val)s) RETURNING t1.id
INFO:aiopg:{'val': 'abc'}
INFO:aiopg:SELECT t1.id AS t1_id, t1.val AS t1_val
FROM t1
WHERE t1.val = %(val_1)s
INFO:aiopg:{'val_1': 'abc'}
Traceback (most recent call last):
File "/home/mamonov/env-gateway/lib64/python3.8/site-packages/aiopg/sa/result.py", line 27, in __getitem__
processor, obj, index = self._keymap[key]
KeyError: 't1_id'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "test.py", line 27, in <module>
loop.run_until_complete(go())
File "/usr/lib64/python3.8/asyncio/base_events.py", line 616, in run_until_complete
return future.result()
File "test.py", line 23, in go
print(row.t1_id, row.t1_val)
File "/home/mamonov/env-gateway/lib64/python3.8/site-packages/aiopg/sa/result.py", line 55, in __getattr__
return self[name]
File "/home/mamonov/env-gateway/lib64/python3.8/site-packages/aiopg/sa/result.py", line 29, in __getitem__
processor, obj, index = self._result_proxy._key_fallback(key)
File "/home/mamonov/env-gateway/lib64/python3.8/site-packages/aiopg/sa/result.py", line 200, in _key_fallback
f"Could not locate column in row for column "
AttributeError: module 'sqlalchemy.sql.expression' has no attribute '_string_or_unprintable'
If I set param use_labels
to False then no error has appear
import logging
import asyncio
from aiopg.sa import create_engine
import sqlalchemy as sa
logging.basicConfig(level=logging.INFO)
metadata = sa.MetaData()
tbl = sa.Table('t1', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('val', sa.String(255)))
async def go():
async with create_engine(user='aiopg',
database='aiopg',
host='127.0.0.1',
password='aiopg', echo=True) as engine:
async with engine.acquire() as conn:
await conn.execute(tbl.insert().values(val='abc'))
async for row in conn.execute(tbl.select(use_labels=False).where(tbl.c.val=='abc')):
print(row.id, row.val)
loop = asyncio.get_event_loop()
loop.run_until_complete(go())
runing with SQLAlchemy-1.4.25 and aiopg-1.3.1 I have got the following:
INFO:aiopg:SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
INFO:aiopg:None
INFO:aiopg:INSERT INTO t1 (val) VALUES (%(val)s) RETURNING t1.id
INFO:aiopg:{'val': 'abc'}
INFO:aiopg:SELECT t1.id, t1.val
FROM t1
WHERE t1.val = %(val_1)s
INFO:aiopg:{'val_1': 'abc'}
1 abc
2 abc
3 abc
4 abc
5 abc
6 abc
7 abc
8 abc
9 abc
10 abc
11 abc
12 abc
13 abc
14 abc
15 abc
16 abc
17 abc
18 abc
19 abc
20 abc
21 abc
22 abc
23 abc
24 abc
Any ideas, when this will be fixed?
from aiopg.
It requires a lot of code rewriting, i think )
from aiopg.
You can 'apply' this fix right now by just switching from aiopg.sa usage to sqlalchemy in async mode.
Or by switching from python at all)
from aiopg.
Anyway, here is a new version https://pypi.org/project/aiopg/1.3.2b2/ with a fix from @AVOstap.
@serg666 Could you test it please?
from aiopg.
Hello, @Pliner ! Sure!
pip install aiopg==1.3.2b2
Collecting aiopg==1.3.2b2
Downloading https://files.pythonhosted.org/packages/4a/f1/1b9a64e90dae1c3ede539d9e665c26f0b721f0fb1ad84ef85fc5b9342126/aiopg-1.3.2b2-py3-none-any.whl
Requirement already satisfied: async-timeout<4.0,>=3.0 in ./lib/python3.8/site-packages (from aiopg==1.3.2b2) (3.0.1)
Requirement already satisfied: psycopg2-binary>=2.8.4 in ./lib/python3.8/site-packages (from aiopg==1.3.2b2) (2.8.5)
Installing collected packages: aiopg
Found existing installation: aiopg 1.3.1
Uninstalling aiopg-1.3.1:
Successfully uninstalled aiopg-1.3.1
Successfully installed aiopg-1.3.2b2
with the code below
import logging
import asyncio
from aiopg.sa import create_engine
import sqlalchemy as sa
logging.basicConfig(level=logging.INFO)
metadata = sa.MetaData()
tbl = sa.Table('t1', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('val', sa.String(255)))
async def go():
async with create_engine(user='aiopg',
database='aiopg',
host='127.0.0.1',
password='aiopg', echo=True) as engine:
async with engine.acquire() as conn:
await conn.execute(tbl.insert().values(val='abc'))
async for row in conn.execute(tbl.select(use_labels=True).where(tbl.c.val=='abc')):
print(row.t1_id, row.t1_val)
loop = asyncio.get_event_loop()
loop.run_until_complete(go())
running with SQLAlchemy-1.4.25
and aiopg-1.3.2b2
there is no problem
INFO:aiopg:SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
INFO:aiopg:None
INFO:aiopg:INSERT INTO t1 (val) VALUES (%(val)s) RETURNING t1.id
INFO:aiopg:{'val': 'abc'}
INFO:aiopg:SELECT t1.id AS t1_id, t1.val AS t1_val
FROM t1
WHERE t1.val = %(val_1)s
INFO:aiopg:{'val_1': 'abc'}
1 abc
2 abc
3 abc
from aiopg.
Hello, @Pliner ! Sure!
pip install aiopg==1.3.2b2 Collecting aiopg==1.3.2b2 Downloading https://files.pythonhosted.org/packages/4a/f1/1b9a64e90dae1c3ede539d9e665c26f0b721f0fb1ad84ef85fc5b9342126/aiopg-1.3.2b2-py3-none-any.whl Requirement already satisfied: async-timeout<4.0,>=3.0 in ./lib/python3.8/site-packages (from aiopg==1.3.2b2) (3.0.1) Requirement already satisfied: psycopg2-binary>=2.8.4 in ./lib/python3.8/site-packages (from aiopg==1.3.2b2) (2.8.5) Installing collected packages: aiopg Found existing installation: aiopg 1.3.1 Uninstalling aiopg-1.3.1: Successfully uninstalled aiopg-1.3.1 Successfully installed aiopg-1.3.2b2
with the code below
import logging import asyncio from aiopg.sa import create_engine import sqlalchemy as sa logging.basicConfig(level=logging.INFO) metadata = sa.MetaData() tbl = sa.Table('t1', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('val', sa.String(255))) async def go(): async with create_engine(user='aiopg', database='aiopg', host='127.0.0.1', password='aiopg', echo=True) as engine: async with engine.acquire() as conn: await conn.execute(tbl.insert().values(val='abc')) async for row in conn.execute(tbl.select(use_labels=True).where(tbl.c.val=='abc')): print(row.t1_id, row.t1_val) loop = asyncio.get_event_loop() loop.run_until_complete(go())running with
SQLAlchemy-1.4.25
andaiopg-1.3.2b2
there is no problemINFO:aiopg:SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore'; INFO:aiopg:None INFO:aiopg:INSERT INTO t1 (val) VALUES (%(val)s) RETURNING t1.id INFO:aiopg:{'val': 'abc'} INFO:aiopg:SELECT t1.id AS t1_id, t1.val AS t1_val FROM t1 WHERE t1.val = %(val_1)s INFO:aiopg:{'val_1': 'abc'} 1 abc 2 abc 3 abc
Thanks. Then, let's release it :)
from aiopg.
https://pypi.org/project/aiopg/1.3.2/
from aiopg.
Related Issues (20)
- Cursor.execute() freezes + Pool.wait_closed() blocks after Pool.close() HOT 2
- aiopg1.2 incompatible with peewee-async HOT 1
- executing a query on a closed SAConnection throws a non-indicative exception HOT 2
- Update "table" with inverse (logic) doesn't work
- FileNotFoundError when connecting to postgres if fd is closed and then reopened HOT 6
- Replace dependency psycopg2-binary with psycopg2 HOT 1
- unable to perform operation on <UVPoll closed=True 0x7fe55db27270>; the handler is closed
- Incompatibility with SQLAlchemy 1.4
- SAConnection twophase methods are broken
- Roadmap after psycopg3 release HOT 2
- Incompatible with SQLAlchemy 1.4.38 and higher HOT 7
- AttributeError: 'Connection' object has no attribute 'send' HOT 1
- Incompatible with SQLAlchemy 2.0.0 HOT 7
- 1.4.0: documentation cannot be build using latest sphinx
- Sometimes got error
- Allow just-in-time compution of credentials for new connections in a pool
- Let's put `aiopg` under the `aio-libs` org on PyPI HOT 3
- NotImplementedError 😢
- aiopg times out while attempting to connect if falling back to an IPv4 address HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from aiopg.