postgresql array column, fetch only_load() element by index

[Dorian Hoxha]

So,

I want to do "SELECT array[1] FROM table;". Meaning to select only 1 element. Is this possible (didn't find by searching docs,mailing-list,google).
Though I can do it by normal query.

Thank You

[Simon King]

I haven't used arrays with postgresql, so I've no idea if this works,
but for documentation you probably want to start here:

http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.ARRAY

According to those docs, if you've declared a table with a column
using the ARRAY type, you should be able to query elements of the
array using python indexing operators. For example:

table = sa.Table('mytable', metadata, sa.Column('array',
sa.ARRAY(sa.Integer)))
sa.select([table.c.array[1]])

Hope that helps,

Simon

[Dorian Hoxha]

Hi Simon,

It all works. All I need is how to do:

Model.query.options(load_only(Model.array[1])).all()   ?

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 a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/dkOnyUkgRX4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@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.

[Simon King]

If you have an SQLAlchemy session, you would write this:

session.query(Model.array[1]).all()

Assuming your "Model.query" is a shorthand for "session.query(Model)",
you might be able to use:

Model.query.with_entities(Model.array[1]).all()

Simon

>> 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 sqlalchemy+...@googlegroups.com.

[Jonathan Vanasco]

FYI, if you query via the ORM and load_only, the query should be something like :

    SELECT primary_key_column, array[1] FROM table;

The ORM adds the primary key behind-the-scenes so it can setup the objects.

as far as i know, If you need to only load array[1] and not the primary key column, you need to use the sqlalchemy core methods.