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
&
[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.
[sqlalchemy] Re: Possible to bulk update with different values?
Got it, thanks!
On Friday, December 23, 2016 at 12:11:12 PM UTC-8, Brian Clark wrote:
>
> Is there an update equivalent of this insert statement?
>
> inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
> session.execute(
> People.__table__.insert().values(
> inserts
> )
> )
>
> I have this right now but it's still slower than I'd like because it's
> using executemany, hoping to have it be one big query
>
> updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"}, {"b_id": 2,
> "b_name": "ashley", "b_age": 25"}]
> stmt = People.__table__.update().\
> where(People.id == bindparam('b_id')).\
> values(name=bindparam('b_name'), age=bindparam('b_age'))
> session.execute(stmt, updated_people)
>
> Thanks!
>
--
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] Re: Possible to bulk update with different values?
Seems doable in raw SQL (using postgresql btw)
http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql
On Friday, December 23, 2016 at 12:11:12 PM UTC-8, Brian Clark wrote:
>
> Is there an update equivalent of this insert statement?
>
> inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
> session.execute(
> People.__table__.insert().values(
> inserts
> )
> )
>
> I have this right now but it's still slower than I'd like because it's
> using executemany, hoping to have it be one big query
>
> updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"}, {"b_id": 2,
> "b_name": "ashley", "b_age": 25"}]
> stmt = People.__table__.update().\
> where(People.id == bindparam('b_id')).\
> values(name=bindparam('b_name'), age=bindparam('b_age'))
> session.execute(stmt, updated_people)
>
> Thanks!
>
--
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] Possible to bulk update with different values?
Is there an update equivalent of this insert statement?
inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
session.execute(
People.__table__.insert().values(
inserts
)
)
I have this right now but it's still slower than I'd like because it's
using executemany, hoping to have it be one big query
updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"}, {"b_id": 2,
"b_name": "ashley", "b_age": 25"}]
stmt = People.__table__.update().\
where(People.id == bindparam('b_id')).\
values(name=bindparam('b_name'), age=bindparam('b_age'))
session.execute(stmt, updated_people)
Thanks!
--
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] Re: Speed issue with bulk inserts
And here's the objects
for key, unique_new_location in unique_locations_hash.iteritems():
trial_location_inserts.append(TrialLocations(trial_id =
current_trial.id, location_id = unique_new_location['location_id'], active
= True, status = unique_new_location['status']))
On Friday, December 23, 2016 at 12:25:40 AM UTC-8, Brian Clark wrote:
>
> So I'm having an issue with a very slow insert, I'm inserting 223 items
> and it takes 20+ seconds to execute. Any advice on what I'm doing wrong and
> why it would be so slow? Using Postgresql 9.4.8
>
> The line of code
>
> LOG_OUTPUT('==PRE BULK==', True)
> db_session.bulk_save_objects(trial_location_inserts)
> LOG_OUTPUT('==POST BULK==', True)
>
> And here's the log from sqlalchemy echo
>
> 2016-12-23 07:37:52.570: ==PRE BULK==
> 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine INSERT INTO
> trial_locations (status, trial_id, location_id, active) VALUES (%(status)s,
> %(trial_id)s, %(location_id)s, %(active)s)
> 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine ({'status':
> u'Completed', 'active': True, 'location_id': 733, 'trial_id': 126625},
> {'status': u'Completed', 'active': True, 'location_id': 716, 'trial_id':
> 126625}, {'status': u'Completed', 'active': True, 'location_id': 1033,
> 'trial_id': 126625}, {'status': u'Completed', 'active': True,
> 'location_id': 1548, 'trial_id': 126625}, {'status': u'Completed',
> 'active': True, 'location_id': 1283, 'trial_id': 126625}, {'status':
> u'Completed', 'active': True, 'location_id': 1556, 'trial_id': 126625},
> {'status': u'Completed', 'active': True, 'location_id': 4271, 'trial_id':
> 126625}, {'status': u'Completed', 'active': True, 'location_id': 1567,
> 'trial_id': 126625} ... displaying 10 of 223 total bound parameter sets
> ... {'status': u'Completed', 'active': True, 'location_id': 1528,
> 'trial_id': 126625}, {'status': u'Completed', 'active': True,
> 'location_id': 1529, 'trial_id': 126625})
> 2016-12-23 07:38:14.270: ==POST BULK==
>
>
> Also for comparison I rewrote it in Sqlalchemy core
>
>
> LOG_OUTPUT('==PRE BULK==', True)
> engine.execute(
> TrialLocations.__table__.insert().values(
> trial_location_core_inserts
> )
> )
> # db_session.bulk_save_objects(trial_location_inserts)
> LOG_OUTPUT('==POST BULK==', True)
>
> and it ran in 0.028 seconds
>
> 2016-12-23 08:11:26.097: ==PRE BULK==
> ...
> 2016-12-23 08:11:27.025: ==POST BULK==
>
> I'd like to keep it in session for the sake of this being the end part of
> a transaction and if the whole thing fails I want it all to rollback. Am I
> doing something wrong? I don't need the data afterwards it just needs to be
> saved
>
> Help appreciated!
>
>
--
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] Speed issue with bulk inserts
So I'm having an issue with a very slow insert, I'm inserting 223 items and
it takes 20+ seconds to execute. Any advice on what I'm doing wrong and why
it would be so slow? Using Postgresql 9.4.8
The line of code
LOG_OUTPUT('==PRE BULK==', True)
db_session.bulk_save_objects(trial_location_inserts)
LOG_OUTPUT('==POST BULK==', True)
And here's the log from sqlalchemy echo
2016-12-23 07:37:52.570: ==PRE BULK==
2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine INSERT INTO
trial_locations (status, trial_id, location_id, active) VALUES (%(status)s,
%(trial_id)s, %(location_id)s, %(active)s)
2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine ({'status':
u'Completed', 'active': True, 'location_id': 733, 'trial_id': 126625},
{'status': u'Completed', 'active': True, 'location_id': 716, 'trial_id':
126625}, {'status': u'Completed', 'active': True, 'location_id': 1033,
'trial_id': 126625}, {'status': u'Completed', 'active': True,
'location_id': 1548, 'trial_id': 126625}, {'status': u'Completed',
'active': True, 'location_id': 1283, 'trial_id': 126625}, {'status':
u'Completed', 'active': True, 'location_id': 1556, 'trial_id': 126625},
{'status': u'Completed', 'active': True, 'location_id': 4271, 'trial_id':
126625}, {'status': u'Completed', 'active': True, 'location_id': 1567,
'trial_id': 126625} ... displaying 10 of 223 total bound parameter sets
... {'status': u'Completed', 'active': True, 'location_id': 1528,
'trial_id': 126625}, {'status': u'Completed', 'active': True,
'location_id': 1529, 'trial_id': 126625})
2016-12-23 07:38:14.270: ==POST BULK==
Also for comparison I rewrote it in Sqlalchemy core
LOG_OUTPUT('==PRE BULK==', True)
engine.execute(
TrialLocations.__table__.insert().values(
trial_location_core_inserts
)
)
# db_session.bulk_save_objects(trial_location_inserts)
LOG_OUTPUT('==POST BULK==', True)
and it ran in 0.028 seconds
2016-12-23 08:11:26.097: ==PRE BULK==
...
2016-12-23 08:11:27.025: ==POST BULK==
I'd like to keep it in session for the sake of this being the end part of a
transaction and if the whole thing fails I want it all to rollback. Am I
doing something wrong? I don't need the data afterwards it just needs to be
saved
Help appreciated!
--
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.
