[sqlalchemy] Issue with Sqlalchemy and inserting array of jsonb

[Brian Clark]

So i'm trying to insert an array of jsonb values into my database but I 
can't seem to format it right, here's my code:

updated_old_passwords = []
updated_old_passwords.append({"index": 1, "password": hashed_password})
user.old_passwords = updated_old_passwords
user.last_password_reset = datetime.datetime.utcnow()
db.session.commit()

And here's the error:

ProgrammingError: (psycopg2.ProgrammingError) column "old_passwords" is 
of type jsonb[] but expression is of type text[]
LINE 1: ...-01-05T06:18:24.992968'::timestamp, 
old_passwords=ARRAY['"\"...
 ^
HINT:  You will need to rewrite or cast the expression.
 [SQL: 'UPDATE users SET password=%(password)s, 
last_password_reset=%(last_password_reset)s, 
old_passwords=%(old_passwords)s WHERE users.id = %(users_id)s'] 
[parameters: {'users_id': 1, 'password': 
'$6$rounds=656000$b.LVoVb7T0WNbT.n$l9uUb1a1qk2Z5ugfpI7B.3D02sUVqhES5VhM1TvwUnMd/iZZL3gn4/zExB47/ZQYPcTMRxO1iaL4/yjXda2.P1',
 
'last_password_reset': datetime.datetime(2017, 1, 5, 6, 18, 24, 992968), 
'old_passwords': ['"\\"{\\"index\\": 1, \\"password\\": 
hashed_password}\\""']}]

Any idea how I format my insert for this to work?

Here's my db table

from sqlalchemy.dialects.postgresql import JSONB, ARRAY

class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key = True)
email = db.Column(db.String(255), index = True)
password = db.Column(db.String(255))
last_password_reset = db.Column(db.DateTime())
old_passwords = db.Column(ARRAY(JSONB))


I also tried this:

updated_old_passwords.append(cast('{"index": 1, "password": 
hashed_password}', JSONB))

but got the error

StatementError: (exceptions.TypeError)  is not JSON serializable [SQL: u'UPDATE users SET 
password=%(password)s, last_password_reset=%(last_password_reset)s, 
old_passwords=%(old_passwords)s WHERE users.id = %(users_id)s'] 
[parameters: [{'users_id': 1, 'password': 
'$6$rounds=656000$WYOiWMAYDSag9QIX$YSDtZle6Bd7Kz.cy7ejWq1NqgME.xUPiDHfV31FKobGu2umxoX34.ZP2MrUDxyym0X4fyzZNEIO//yS6UTPoC.',
 
'last_password_reset': datetime.datetime(2017, 1, 5, 6, 26, 35, 610703), 
'old_passwords': []}]]

-- 
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.

[sqlalchemy] Enum in Array

[Tim-Christian Mundt]

Hi,

I've been using an array of enums with postgres and SQLAlchemy successfully 
over the past year like so:

class MyModel(BaseModel):
enum_field = Column(postgresql.ARRAY(EnumField(MyEnum, native_enum=False)))
The EnumField is from the sqlalchemy_enum34 
 library, a small wrapper around 
the builtin enum that uses Python enums as Python representation instead of 
strings.

Although the docs say 
,
 array of enum is not supported, I guess it worked, because I chose 
'native_enum=False'. Recently I noticed that it doesn't work anymore, I think 
it's due to the upgrade from SQLA 1.0 to 1.1, but I'm not sure.

The problem is, that it generates invalid DDL:

CREATE TABLE my_model (
enum_field VARCHAR(5)[3] NOT NULL CHECK (contexts IN ('ONE', 'TWO', 
'THREE'))
)
The error I get is:

ERROR:  malformed array literal: "ONE"
DETAIL:  Array value must start with "{" or dimension information.
Any idea how I can get back my enum array?
By the way: when it worked, no CHECK constraint was actually created, just an 
array of varying. I'm ok with that as long as I can use enums in my Python code 
(e.g. query.filter(enum_field==MyEnum.ONE))

Regards
Tim

-- 
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.

[sqlalchemy] Please mention ilike more prominently in the documentation

[Andrew M]

Hi Mike,

Thanks for SQLAlchemy. As a relatively new user I wanted case-insensitive 
querying but the only reference for this that I could find in the 
documentation was custom comparators. I spent a lot of time trying to get 
these to work when, it turns out, ilike suited my needs perfectly. 

Could you please mention ilike more prominently in the documentation (and 
in the section on custom comparators include a note which lets people know 
that ilike might be all they need)?

Cheers,
Andrew


-- 
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] Limiting queries from single-table inheritance declarative models

[Tucker Beck]

Mike:

Actually, the classproperty I was using was a custom decorator that I made.
It doesn't work quite the same as the example you provided, so I will have
to compare the two approaches.

Here is the one I had:

class classproperty(property):
"""
This defines a decorator that can be used to describe a read-only
property
that is attached to the class itself instead of an instance.
"""

def __get__(self, cls, owner):
return classmethod(self.fget).__get__(None, owner)()

I will try your method and see how it works for my project.

Thanks for the response!


On Mon, Jan 2, 2017 at 9:22 AM, mike bayer  wrote:

> the usage of "classproperty" here suggests you're using SQLAlchemy's
> internal function, which has special meaning in that it gets treated like
> declared_attr, and you can see in INSERT it is using HybridModel for
> everyone.   Using a different classproperty approach below the test case
> passes.
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base, declared_attr
> from sqlalchemy.ext.hybrid import hybrid_property, Comparator
>
> Base = declarative_base()
>
> class classproperty(property):
> """A decorator that behaves like @property except that operates
> on classes rather than instances.
>
> The decorator is currently special when using the declarative
> module, but note that the
> :class:`~.sqlalchemy.ext.declarative.declared_attr`
> decorator should be used for this purpose with declarative.
>
> """
>
> def __init__(self, fget, *arg, **kw):
> super(classproperty, self).__init__(fget, *arg, **kw)
> self.__doc__ = fget.__doc__
>
> def __get__(desc, self, cls):
> return desc.fget(cls)
>
>
> class ModelBase(Base):
> __abstract__ = True
>
> def __repr__(self):
> return "{} ({}:{})".format(type(self).__name__, self.name, self.id
> )
>
>
> class HybridType(ModelBase):
> __tablename__ = 'hybrid_types'
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
>
> class HybridModel(ModelBase):
> __tablename__ = 'hybrids'
>
> id = Column(Integer, primary_key=True)
> name = Column(Text)
> hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id'),
> nullable=False)
> hybrid_type = relationship('HybridType')
>
> def __init__(self, *args, **kwargs):
> self.hybrid_type_name = self.hybrid_type_identity
> return super().__init__(*args, **kwargs)
>
> @classproperty
> def hybrid_type_identity(cls):
> return cls.__name__
>
> @declared_attr
> def __mapper_args__(cls):
> return dict(
> polymorphic_on=cls.hybrid_type_name_subquery(),
> polymorphic_identity=cls.hybrid_type_identity,
> )
>
> @hybrid_property
> def hybrid_type_name(self):
> return self.hybrid_type.name
>
> @hybrid_type_name.setter
> def hybrid_type_name(self, value):
> self.hybrid_type_id = (
> select([HybridType.id]).
> where(HybridType.name == value)
> )
>
> @hybrid_type_name.expression
> def hybrid_type_name(cls):
> return cls.hybrid_type_name_subquery()
>
> @classmethod
> def hybrid_type_name_subquery(cls):
> return select([HybridType.name]).where(HybridType.id ==
> cls.hybrid_type_id).as_scalar()
>
> class HybridComparator(Comparator):
>
> def operate(self, op, other):
> return op(HybridType.hybrid_type_id,
> select([HybridType.id]).where(HybridType.name == other).as_scalar())
>
> @hybrid_type_name.comparator
> def hybrid_type_name(cls):
> return cls.HybridComparator(cls)
>
>
> class HybridAlpha(HybridModel):
> pass
>
>
> class Hybrita(HybridModel):
> pass
>
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
> session = Session(e)
>
>
> session.add(HybridType(name=HybridAlpha.hybrid_type_identity))
> session.add(HybridType(name=Hybrita.hybrid_type_identity))
> session.add(HybridAlpha(name='alpha_instance'))
> session.add(Hybrita(name='beta_instance'))
>
>
> all_alphas = session.query(HybridAlpha).all()
> assert session.query(HybridModel).count() == 2
> print("--")
> assert session.query(HybridAlpha).count() == 1
> assert session.query(Hybrita).count() == 1
>
>
>
>
> On 12/29/2016 11:09 AM, Tucker Beck wrote:
>
>> Mike,
>>
>> Here's the solution I came up with. It shows the 'inverted select' used
>> in the comparator vs the hybrid expression
>>
>> class ModelBase(db.Model):
>> __abstract__ = True
>>
>> def __repr__(self):
>> return "{} ({}:{})".format(type(self).__name__, self.name
>> , self.id )
>>
>>
>> class HybridType(ModelBase):
>> __tablename__ = 'hybrid_types'
>> id = db.Column(db.Integer, primary_key=True)
>> name = db.Column(db.Text)
>>
>>
>> class HybridModel(ModelBase):
>> __tablename__