your imports can't provide ARRAY class, I've added: from
sqlalchemy.dialects.postgresql import ARRAY. My version is 1.0.14.
Output:
/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py:185:
SAWarning: Unicode type received non-unicode bind param value 'y'. (this
warning may be suppressed after 10 occurrences)
(util.ellipses_string(value),))
/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py:185:
SAWarning: Unicode type received non-unicode bind param value 'x'. (this
warning may be suppressed after 10 occurrences)
(util.ellipses_string(value),))
['{', '"', '(', 'x', ',', 'y', ')', '"', '}']
And about jsonb_object_agg. I use PostgreSQL 9.5. If you use older version,
you can try to replace it with json_object_agg.
I wrote such code:
from sqlalchemy import *
from sqlalchemy.dialects.postgresql import ARRAY
from hasoffers.core.model import Base
from hasoffers.core.model import Session
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True, autoincrement=True)
x = Column(Unicode)
y = Column(Unicode)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True, autoincrement=True)
a_keys = Column(ARRAY(Integer))
col1 = Column(Unicode)
col2 = Column(Unicode)
Base.metadata.bind = Session.bind
Base.metadata.create_all()
Session.add(A(x="x", y="y"))
Session.add(A(x="f", y="j"))
Session.add(A(x="b", y="s"))
Session.add(B(a_keys=[1], col1="qaz", col2="qwe"))
Session.add(B(a_keys=[2,3], col1="zaq", col2="fds"))
Session.add(B(a_keys=[2,3], col1="gtr", col2="ascs"))
Session.commit()
for row in Session.query(A, func.jsonb_object_agg(B.col1, B.col2)).join(B,
A.id == func.any(B.a_keys)).group_by(A.id):
print row
After execution I got such traceback:
Traceback (most recent call last):
File "/home/anton/Projects/.../core/hasoffers/core/run/stuff.py", line
33, in <module>
for row in Session.query(A, func.jsonb_object_agg(B.col1,
B.col2)).join(B, A.id == func.any(B.a_keys)).group_by(A.id):
File
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
line 86, in instances
util.raise_from_cause(err)
File
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
line 77, in instances
rows = util.unique_list(rows, filter_fn)
File
"/home/anton/Projects/.venv/lib/python2.7/site-packages/sqlalchemy/util/_collections.py",
line 757, in unique_list
if hashfunc(x) not in seen
TypeError: unhashable type: 'dict'
вторник, 11 апреля 2017 г., 16:43:20 UTC+3 пользователь Mike Bayer написал:
>
> can't reproduce (though the ARRAY(unicode) type is not what psycopg2
> returns, and there seems to be a difference in behavior between
> sqlalchemy.ARRAY and sqlalchemy.dialects.postgresql.ARRAY).
>
> please provide a complete example based on the below test script and
> stack traces
>
> also my PG database doesn't know about the jsonb_object_agg function
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
> x = Column(Unicode)
> y = Column(Unicode)
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> s = Session(e)
> s.add(A(x="x", y="y"))
> s.commit()
>
> row = s.query(func.array_agg(tuple_(A.x, A.y),
> type_=ARRAY(Unicode))).scalar()
> print row
>
>
>
>
>
> On 04/11/2017 09:04 AM, Антонио Антуан wrote:
> > Hi
> > I want to build such query with sqlalchemy:
> > |
> > SELECT array_agg((column1,column2))fromtable
> > |
> >
> > Using psql it works perfectly and returns such result:
> > |
> > {"(col1_row1_value, col2_row1_value)","(col1_row2_value,
> > col2_row2_value)"...}
> > |
> >
> > I tried several forms of SQLA-query:
> >
> > |>> from sqlalchemy.dialects.postgresql import ARRAY
> >>> from sqlalchemy.sql.elements import Tuple
> >>> ...
> func.array_agg(Tuple(Model.col1,Model.col2),type_=ARRAY(Unicode))...
> > ...
> > TypeError: unhashable type: 'list'
> >>> ... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Unicode,
> > as_tuple=True))...
> > ...# returns value like this: ('{', '"', '(', 'c',...)
> >>> ... func.array_agg(Tuple(Model.col1, Model.col2), type_=ARRAY(Tuple,
> > as_tuple=True))...
> > ...
> > AttributeError: Neither 'Tuple' object nor 'Comparator' object has an
> > attribute 'dialect_impl'
> > |
> >
> > At first, I wanted to use /`func.jsonb_object_agg(Model.col1,
> > Model.col2)`/, but it raises */"unhashable type: dict"/*
> > */
> > /*
> > Could you point to solution?
> >
> > --
> > 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 <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
