how to handle the ARRAY(JSON) -JSON[] column type

[Wenlong LU]

hi,

I have the following model:

class Product(Base):

     skus = Column(ARRAY(JSON))

p = Product(skus=[{"skuPrice": "44.88", "skuStock": true, "aeopSKUProperty": [], "skuCode": "060314"}])

session.add(p)

t.commit()

Error comes out:

sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occuring prematurely) (ProgrammingError) column "skus" is of type json[] but expression is of type text[] ..

'INSERT INTO product (created_at, updated, skus) VALUES (%(created_at)s, %(updated_at)s, %(skus)s) RETURNING product.id' {'skus': ['{"skuPrice": "44.88", "skuStock": true, "aeopSKUProperty": [], "skuCode": "060314"}'], 'created_at':  .... } 

it seems sqlalchemy only support array(Unicode), it convert Json into str...

any suggestion?

wenlong

[Michael Bayer]

> it seems sqlalchemy only support array(Unicode), it convert Json into str…

typically the handling of ARRAY and JSON falls to psycopg2 so it may not be supported on that side. It’s a pretty odd case to have JSON inside of ARRAY.

to try fixing this as is would involve experimenting with psycopg2’s array/json extensions to see if psycopg2 supports it directly (that is, get a script to do this INSERT using only psycopg2, but make sure you’re using bound parameters), then to make sure SQLAlchemy’s PG dialect isn’t doing anything to get in the way of that.

unfortunately I don’t have a simple fix here. this is a bug in either SQLAlchemy, psycopg2 or both.

[Lu Wenlong]

thx.

I see. I am stupid, and it seems ok,  just using JSON directly instead of array(JSON).

wenlong

On Fri, Jun 6, 2014 at 9:01 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

On Jun 5, 2014, at 11:25 PM, Wenlong LU <a88...@gmail.com> wrote:

> hi,
>
> I have the following model:
>
> class Product(Base):
>      skus = Column(ARRAY(JSON))
>
>
> p = Product(skus=[{"skuPrice": "44.88", "skuStock": true, "aeopSKUProperty": [], "skuCode": "060314"}])
>
> session.add(p)
> t.commit()
>
> Error comes out:
> sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occuring prematurely) (ProgrammingError) column "skus" is of type json[] but expression is of type text[] ..
>
> 'INSERT INTO product (created_at, updated, skus) VALUES (%(created_at)s, %(updated_at)s, %(skus)s) RETURNING product.id' {'skus': ['{"skuPrice": "44.88", "skuStock": true, "aeopSKUProperty": [], "skuCode": "060314"}'], 'created_at':  .... }
>
>

> it seems sqlalchemy only support array(Unicode), it convert Json into str...

typically the handling of ARRAY and JSON falls to psycopg2 so it may not be supported on that side.      It's a pretty odd case to have JSON inside of ARRAY.

to try fixing this as is would involve experimenting with psycopg2's array/json extensions to see if psycopg2 supports it directly (that is, get a script to do this INSERT using only psycopg2, but make sure you're using bound parameters), then to make sure SQLAlchemy's PG dialect isn't doing anything to get in the way of that.

unfortunately I don't have a simple fix here.   this is a bug in either SQLAlchemy, psycopg2 or both.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/19lTeetxdHQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

沒有理想走不遠，不面對現實連今天都走不過.