Re: [sqlalchemy] Issue with Sqlalchemy and inserting array of jsonb
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) > 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': [ 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
> > .
> > To post to this group, send email to sqlal...@googlegroups.com
>
> > .
> > 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
Re: [sqlalchemy] Issue with Sqlalchemy and inserting array of jsonb
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) 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 -
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] Issue with Sqlalchemy and inserting array of 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 = []
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.
