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