Re: [sqlalchemy] postgresql tuple as function argument
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
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
Re: [sqlalchemy] [alembic] Batch migrations with sqlite and naming conventions
this area of batch migrations is not well developed, has to deal with
some impossible situations due to SQLite's poor design (allowing unnamed
constraints, not to mention that they refuse to implement ALTER), and is
also in flux (as Alembic is relying upon a buggy behavior in SQLAlchemy
w.r.t the naming convention feature here).
IIRC the naming convention linkage with batch migrations only has to do
with constraints that are unnamed when reflected from the database.
That might also be buggy.
I do notice that you have some reflect_args set up to try helping it
with the Boolean datatype and that might be throwing things off, not
sure. Or, you can add the constraint_name argument to each of those
Boolean types which would hopefully force it to be the right name (see
http://alembic.zzzcomputing.com/en/latest/batch.html#changing-the-type-of-boolean-enum-and-other-implicit-check-datatypes)
Current SQLAlchemy (1.1) *will* reflect CHECK constraints on SQLite, the
alembic docs are out of date on this. In theory, your naming
convention should have no impact because these constraints are coming
back with names already. I'd confirm that these names include the
naming convention already in the database, then try running without any
of the reflect_args added. in theory these boolean datatypes /
constraints should work without any intervention.
for further help I'd need to build this up here and there are likely
more bugs on the Alembic side to be fixed.
On 04/11/2017 10:58 PM, Peter Erickson wrote:
I'm sure that I'm missing a subtle point with batch migrations and
naming conventions for SQLite databases, but I can't figure out what I'm
doing wrong. After renaming a table, I'm using a batch migration to
update a foreign key in a child table. However, after recreating the
child table 2 CHECK constraints (being created from a Boolean) aren't
being named according to the specified naming convention.
To ensure that this post doesn't get too long, here are the parts I
think are relevant. I can add more if needed.
naming_convention = {
"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
}
class RulesetMatch(Base):
__tablename__ = 'ruleset_matches'
...
viewed = Column(Boolean(name='view_bool'), default=False)
hide = Column(Boolean(name='hide'), default=False)
# sqlite3 data-dev.sqlite .schema
CREATE TABLE ruleset_matches (
...
viewed BOOLEAN,
hide BOOLEAN,
CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
CONSTRAINT ck_ruleset_matches_view_bool CHECK (viewed IN (0, 1)),
CONSTRAINT ck_ruleset_matches_hide CHECK (hide IN (0, 1))
);
# migration script
def upgrade():
with op.batch_alter_table(
'ruleset_matches',
naming_convention=naming_convention,
reflect_args=[
Column('viewed', Boolean(name='view_bool')),
Column('hide', Boolean(name='hide'))]) as batch_op:
# drop_constraint
# create_foreign_key
# sqlite3 data-dev.sqlite .schema
CREATE TABLE IF NOT EXISTS "ruleset_matches" (
...
viewed BOOLEAN,
hide BOOLEAN,
CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
CONSTRAINT view_bool CHECK (viewed IN (0, 1)),
CONSTRAINT hide CHECK (hide IN (0, 1)),
...
);
As shown in the last schema, the CHECKS are named "view_bool" and "hide"
unlike what it was previously "ck_ruleset_matches_view_bool" and
"ck_ruleset_matches_hide." If I remove the "name" attribute in
reflect_args or remove reflect_args all together, the CHECK constraints
are unnamed. Removing the naming_convention doesn't seem to affect
anything. What do I need to do to ensure that the constraints are named
appropriately?
alembic 0.9.1
sqlalchemy 1.1.9
Thanks in advance.
--
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.
Re: [sqlalchemy] postgresql tuple as function argument
Ok, I'l try it, thank you.
вторник, 11 апреля 2017 г., 17:26:18 UTC+3 пользователь Mike Bayer написал:
>
> I will say that the psycopg2 driver is not supporting this, however, it
> isn't parsing out the tuple. Using postgresql.ARRAY we get an answer
> like:
>
> ['{', '"', '(', 'x', ',', 'y', ')', '"', ',', '"', '(', 'x', ',', 'y',
> ')', '"', '}']
>
> that is, the string coming back is being interpreted as an array by
> SQLAlchemy, which here it is not.you might need to use some of the
> psycopg2 extension points to support this like
>
> http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.new_array_type.
>
>
>
>
>
> On 04/11/2017 09:43 AM, mike bayer wrote:
> > 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
> >> .
> >> To post to this group, send email to sqlal...@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.
Re: [sqlalchemy] postgresql tuple as function argument
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
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 -
>
[sqlalchemy] Re: insertion into association table, giving integrityerror
Sorry not the first one, but the second one -- 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.
