Great thanks!! On Thursday, January 5, 2017 at 7:06:26 AM UTC-8, Mike Bayer wrote: > > 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 > > > > On 01/05/2017 02:00 AM, Brian Clark wrote: > > 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>]}]] > > > > -- > > 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.
