Re: [sqlalchemy] Returning Postgres JSON view as JSON string without serialization
Thanks Mike, wrapping the view declaration with ()::TEXT worked perfectly,
but good to know I can do it from SQLA at load time if need be.
On Saturday 30 October 2021 at 02:17:49 UTC+1 Mike Bayer wrote:
> oh also, if this is a view, much easier, just put the CAST to TEXT in your
> CREATE VIEW statement. that way you will definitely get strings back and
> nothing json related will kick in client side.
>
>
>
> On Fri, Oct 29, 2021, at 9:16 PM, Mike Bayer wrote:
>
> psycopg2 driver (if that's what you're using) jumps in to do the JSON so
> if you dont want json you need to cast as a string, like
> cast(table.c.json_col, String).
>
> if you are using a Table with autoload you'd want to override this type
> using the technique detailed at
> https://docs.sqlalchemy.org/en/14/core/reflection.html#overriding-reflected-columns
>
> .
>
> On Fri, Oct 29, 2021, at 7:52 PM, Stephan Hügel wrote:
>
> I’m querying a Postgres view which returns JSON (SELECT
> json_build_object(…)) which I’ve declared as a view in my db:
>
> allinfra = Table("allinfra", db.metadata, autoload_with=db.engine)
> res = db.session.query(allinfra).scalar()
>
> But this gives me a Python dict, when what I want is the JSON string
> returned by the view - serializing it with json.dumps() on the Python side
> defeats the point (getting Postgres to do the heavy lifting). Apologies if
> this question has an obvious answer, but I’ve found it impossible to search
> for.
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/5f84f3a9-aace-43ab-b7d7-1f1a143f1a53n%40googlegroups.com
>
>
> .
>
>
>
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/97d62485-ba5b-4b7b-9153-edb7e8e7bf2cn%40googlegroups.com.
Re: [sqlalchemy] Returning Postgres JSON view as JSON string without serialization
oh also, if this is a view, much easier, just put the CAST to TEXT in your
CREATE VIEW statement. that way you will definitely get strings back and
nothing json related will kick in client side.
On Fri, Oct 29, 2021, at 9:16 PM, Mike Bayer wrote:
> psycopg2 driver (if that's what you're using) jumps in to do the JSON so if
> you dont want json you need to cast as a string, like cast(table.c.json_col,
> String).
>
> if you are using a Table with autoload you'd want to override this type using
> the technique detailed at
> https://docs.sqlalchemy.org/en/14/core/reflection.html#overriding-reflected-columns
> .
>
> On Fri, Oct 29, 2021, at 7:52 PM, Stephan Hügel wrote:
>> I’m querying a Postgres view which returns JSON (SELECT
>> json_build_object(…)) which I’ve declared as a view in my db:
>>
>> allinfra = Table("allinfra", db.metadata, autoload_with=db.engine)
>> res = db.session.query(allinfra).scalar()
>>
>> But this gives me a Python dict, when what I want is the JSON string
>> returned by the view - serializing it with json.dumps() on the Python side
>> defeats the point (getting Postgres to do the heavy lifting). Apologies if
>> this question has an obvious answer, but I’ve found it impossible to search
>> for.
>>
>>
>> --
>> 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 view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/5f84f3a9-aace-43ab-b7d7-1f1a143f1a53n%40googlegroups.com
>>
>> .
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/dcea0cd3-15bb-4e2e-86c4-17ece9bdb90f%40www.fastmail.com.
Re: [sqlalchemy] Returning Postgres JSON view as JSON string without serialization
psycopg2 driver (if that's what you're using) jumps in to do the JSON so if you
dont want json you need to cast as a string, like cast(table.c.json_col,
String).
if you are using a Table with autoload you'd want to override this type using
the technique detailed at
https://docs.sqlalchemy.org/en/14/core/reflection.html#overriding-reflected-columns
.
On Fri, Oct 29, 2021, at 7:52 PM, Stephan Hügel wrote:
> I’m querying a Postgres view which returns JSON (SELECT json_build_object(…))
> which I’ve declared as a view in my db:
>
> allinfra = Table("allinfra", db.metadata, autoload_with=db.engine)
> res = db.session.query(allinfra).scalar()
>
> But this gives me a Python dict, when what I want is the JSON string returned
> by the view - serializing it with json.dumps() on the Python side defeats the
> point (getting Postgres to do the heavy lifting). Apologies if this question
> has an obvious answer, but I’ve found it impossible to search for.
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/5f84f3a9-aace-43ab-b7d7-1f1a143f1a53n%40googlegroups.com
>
> .
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/7110eb54-b954-4978-ad53-dff5d4bcf1e8%40www.fastmail.com.
