On 04/12/2017 09:19 AM, Антонио Антуан wrote:
your imports can't provide ARRAY class, I've added: from
sqlalchemy.dialects.postgresql import ARRAY. My version is 1.0.14.
for ARRAY + array_agg, explicit support for these things has improved in
the 1.1 series, and if you're doing this kind of stuff I'd recommend
moving to 1.1 so you are at least in better shape to get to 1.2, 1.3,
etc. as we continue to adjust the API to improve upon these things,
there's lots of related Postgresql syntaxes we are not yet on track to
explicitly support until at least 1.3:
http://docs.sqlalchemy.org/en/rel_1_1/changelog/migration_11.html#new-function-features-within-group-array-agg-and-set-aggregate-functions
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', ')', '"', '}']
|
yes that is because psycopg2 is not recognizing the type of data.
However, we can improve upon the situation by adding a CAST, which seems
to send psycopg2 a little more information:
row = s.query(
cast(
func.array_agg(tuple_(A.x, A.y)), ARRAY(Unicode)
)
).scalar()
print row
then we get back:
[u'(x,y)']
which... is what you wanted? not sure. I think overall if you can
provide a CAST to the type you want, it will be passed to the psycopg2
driver so that it has a clue what it should do with the string data its
getting back.
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
<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
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.
