Sqlalchemy问题并将jsonb数组插入到Postgresql

问题描述

所以我正在尝试将jsonb值数组插入我的数据库，但是我似乎无法正确设置其格式，这是我的代码:

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.append({"index": 1, "password": hashed_password})
user.old_passwords = updated_old_passwords
user.last_password_reset = datetime.datetime.utcnow()
db.session.commit()

这是错误:

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?

这是我的数据库表

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

我也尝试过:

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

但出现错误

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>]}]]

推荐答案

使用以下方法添加缺少的演员表:

Add the missing cast, using:

class CastingArray(ARRAY):

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

在定义模块时，请使用此类代替ARRAY(old_passwords = db.Column(CastingArray(JSONB))

And when defining the module use this class instead of ARRAY (old_passwords = db.Column(CastingArray(JSONB))

(答案取自 https://groups.google.com/forum /#！topic/sqlalchemy/oB4zVgUEMgA )

这篇关于Sqlalchemy问题并将jsonb数组插入到Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！