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) <sqlalchemy.sql.elements.Cast object at 0x10f3ed150> 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.sql.elements.Cast object at 0x10f3ed150>]}]]

[mike bayer]

I will add a new section for this but we need to use a workaround
similar to
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#using-enum-with-array
(but simpler). Just the CAST is needed, see below.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import ARRAY, JSONB

Base = declarative_base()


class CastingArray(ARRAY):

def bind_expression(self, bindvalue):
return cast(bindvalue, self)


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(CastingArray(JSONB))

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

s.add(A(data=[{"foo": "bar"}, {"bat": "hoho"}]))

s.commit()

a1 = s.query(A).first()
print a1.data

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Brian Clark]

Great thanks!!

> <mailto:sqlalchemy+unsub...@googlegroups.com>.