Re: [sqlalchemy] Re: Query last relation created and attributes
Ok I just identified the issue. It seems that there is a conflict between 2 subqueries : in my model.I have this : statussen = relationship( "PersoonStatus", order_by="desc(PersoonStatus.status_datum)", backref='persoon', cascade='all, delete, delete-orphan', lazy='subquery' ) and if I do this query : last_statuses = aliased( statussen_table_name, self.session.query( getattr(statussen_table_name, issue_id_field), statussen_table_name.status_id)\ .join(Status).order_by(Status.datum.desc())\ .limit(1).subquery().lateral()) there is a conflict between this 2 subqueries who target the same table. Is there a workaround to prevent this conflict? any idea? -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/61df1e98-8475-4d3a-a21b-20aff412d4fa%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
I really don't know why this query returns me this .. totally mysterious Le jeudi 16 mai 2019 16:27:50 UTC+2, Scheck David a écrit : > > I finished by a query like that : > > > last_statuses = aliased(statussen_table_name, > self.session.query(getattr(statussen_table_name, object_name), > > statussen_table_name.status_id).join(Status).order_by(Status.datum.desc()).limit(1).subquery().lateral()) > return > self.session.query(self.cls).join(last_statuses).filter(Status.statustype_id > > 50).all() > > the problem is that there is a problem in the query : > > sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT > statussen.datum > > FROM (SELECT personen.id AS personen_id > > FROM statussen, personen JOIN LATERAL (SELECT > personen_statussen.persoon_id AS persoon_id, personen_statussen.status_id > AS status_id > > FROM personen_statussen JOIN statussen ON statussen.id = > personen_statussen.status_id ORDER BY statussen.datum DESC > > LIMIT :param_1) AS anon_2 ON personen.id = anon_2.persoon_id > > WHERE statussen.statustype_id > :statustype_id_1) AS anon_1, > personen_statussen > > WHERE statussen.id = personen_statussen.status_id' returned no FROM > clauses due to auto-correlation; specify correlate() to control > correlation manually. > -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/94817a4b-3579-44de-ab48-1251baf21ac1%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
I finished by a query like that : last_statuses = aliased(statussen_table_name, self.session.query(getattr(statussen_table_name, object_name), statussen_table_name.status_id).join(Status).order_by(Status.datum.desc()).limit(1).subquery().lateral()) return self.session.query(self.cls).join(last_statuses).filter(Status.statustype_id > 50).all() the problem is that there is a problem in the query : sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT statussen.datum FROM (SELECT personen.id AS personen_id FROM statussen, personen JOIN LATERAL (SELECT personen_statussen.persoon_id AS persoon_id, personen_statussen.status_id AS status_id FROM personen_statussen JOIN statussen ON statussen.id = personen_statussen.status_id ORDER BY statussen.datum DESC LIMIT :param_1) AS anon_2 ON personen.id = anon_2.persoon_id WHERE statussen.statustype_id > :statustype_id_1) AS anon_1, personen_statussen WHERE statussen.id = personen_statussen.status_id' returned no FROM clauses due to auto-correlation; specify correlate() to control correlation manually. -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/930ee74c-1892-4621-8006-51cbd94e020e%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
I finished by a query like that : last_statuses = aliased(statussen_table_name, self.session.query(getattr(statussen_table_name, object_name), statussen_table_name.status_id).join(Status).order_by(Status.datum.desc()).limit(1).subquery().lateral()) return self.session.query(self.cls).join(last_statuses).filter(Status.statustype_id > 50).all() the problem is that there is a problem in the query : sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT statussen.datum FROM (SELECT personen.id AS personen_id FROM statussen, personen JOIN LATERAL (SELECT personen_statussen.persoon_id AS persoon_id, personen_statussen.status_id AS status_id FROM personen_statussen JOIN statussen ON statussen.id = personen_statussen.status_id ORDER BY statussen.datum DESC LIMIT :param_1) AS anon_2 ON personen.id = anon_2.persoon_id WHERE statussen.statustype_id > :statustype_id_1) AS anon_1, personen_statussen WHERE statussen.id = personen_statussen.status_id' returned no FROM clauses due to auto-correlation; specify correlate() to control correlation manually. -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f7fef252-8fc6-4cb5-a8e4-1270cfacab21%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
For a full state of what I have https://dpaste.de/vV8k the goal is to convert the sql query to SQLAlchemy. Thanks in advance for any help -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d17f598c-11fc-4921-b165-7f92d95b385c%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
In fact this I will use as a method on a datamanager use for different objects. In this sql the object is erfgoedobjecten but it will change. Le mar. 14 mai 2019 à 18:06, Jonathan Vanasco a écrit : > > > On Tuesday, May 14, 2019 at 10:29:58 AM UTC-4, Scheck David wrote: >> >> I'm near the result of sql: >> >> https://dpaste.de/1XYa# >> >>> >>>> > Do you want this to be an attribute/relationship on the class or to run > this as a separate query? > > -- > 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/Cju-PF7mV58/unsubscribe. > To unsubscribe from this group and all its topics, 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. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/36b8df42-960c-4f75-adee-a4a59cc87636%40googlegroups.com > <https://groups.google.com/d/msgid/sqlalchemy/36b8df42-960c-4f75-adee-a4a59cc87636%40googlegroups.com?utm_medium=email_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- With kindest regards, *David SCHECK* PRESIDENT/DEVELOPER [image: Signature Logo Sphax Bleu-01.png] Phone: +32 4 87 86 70 12 Visit our website ! https://www.sphax.org -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOPG6sc%3DgxYFbRHF46kqnRRyhx0-%2BJiC4L06S5hDQNc28a19GA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
I'm near the result of sql:
https://dpaste.de/1XYa#
Le lundi 13 mai 2019 17:46:29 UTC+2, Mike Bayer a écrit :
>
>
>
> On Mon, May 13, 2019 at 10:37 AM Scheck David > wrote:
>
>> the problem is that I can't use SQL for this because this is a mixins
>> that I use for several objects (tables) because they all have this status
>> structure... yes it's quite difficult to do :
>>
>
> I don't know what it is you want to do so if you could illustrate the SQL
> that resembles what you want, I'd have some idea. If you are trying to
> build a Query object, then you are working with SQL and you'd need to
> understand the structure you're looking for.
>
>
>
>
>>
>> right now I'm on this stage, but still don't work
>> self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
>> .group_by(Object).with_entities(Object,
>> func.max(Status.datum).label("status_datum")).subquery()
>>
>> self.session.query(Object).join((sub, sub.c.id == Status.id))\
>> .filter(sub.c.statustype_id > 50)
>>
>> Le lun. 13 mai 2019 à 16:26, Mike Bayer > > a écrit :
>>
>>> Hi -
>>>
>>> I was hoping someone could jump in on this.
>>>
>>> do you know the SQL that you want to emit? E.g. plain SQL string. I
>>> can help you do that part.
>>>
>>>
>>> On Mon, May 13, 2019 at 9:11 AM Scheck David >> > wrote:
>>> >
>>> > I think I'm near but I can't finish :
>>> >
>>> > for all the structure : https://dpaste.de/fek5#L
>>> >
>>> > and here my query :
>>> >
>>> > self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
>>> > .group_by(Object).with_entities(Object,
>>> func.max(Status.datum).label("status_datum")).subquery()
>>> >
>>> > self.session.query(Object).join((sub, sub.c.id == Status.id))\
>>> > .filter(sub.c.statustype_id > 50)
>>> >
>>> > but status type not reachable.
>>> >
>>> >
>>> > --
>>> > 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 sqlal...@googlegroups.com .
>>> > To post to this group, send email to sqlal...@googlegroups.com
>>> .
>>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>>> > To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com
>>> .
>>> > 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 a topic in the
>>> Google Groups "sqlalchemy" group.
>>> To unsubscribe from this topic, visit
>>> https://groups.google.com/d/topic/sqlalchemy/Cju-PF7mV58/unsubscribe.
>>> To unsubscribe from this group and all its topics, send an email to
>>> sqlal...@googlegroups.com .
>>> To post to this group, send email to sqlal...@googlegroups.com
>>> .
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXF3d_pffLqFLYpSNw80E%3D_2ULhh0pTiqBG-F1dFjA9r1Q%40mail.gmail.com
>>> .
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>> --
>>
>> With kindest regards,
>>
>>
>> *David SCHECK*
>>
>> PRESIDENT/DEVELOPER
>>
>> [image: Signature Logo Sphax Bleu-01.png]
>>
>> Phone: +32 4 87 86 70 12
>> Visit our webs
Re: [sqlalchemy] Re: Query last relation created and attributes
Here is the last version of my sql query: https://dpaste.de/8UhP -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/23195ece-7551-483a-bd85-61a00a8b8748%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
>
>
> Here is the sql generated for this query : https://dpaste.de/bJsc
Le lundi 13 mai 2019 17:46:29 UTC+2, Mike Bayer a écrit :
>
>
>
> On Mon, May 13, 2019 at 10:37 AM Scheck David > wrote:
>
>> the problem is that I can't use SQL for this because this is a mixins
>> that I use for several objects (tables) because they all have this status
>> structure... yes it's quite difficult to do :
>>
>
> I don't know what it is you want to do so if you could illustrate the SQL
> that resembles what you want, I'd have some idea. If you are trying to
> build a Query object, then you are working with SQL and you'd need to
> understand the structure you're looking for.
>
>
>
>
>>
>> right now I'm on this stage, but still don't work
>> self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
>> .group_by(Object).with_entities(Object,
>> func.max(Status.datum).label("status_datum")).subquery()
>>
>> self.session.query(Object).join((sub, sub.c.id == Status.id))\
>> .filter(sub.c.statustype_id > 50)
>>
>> Le lun. 13 mai 2019 à 16:26, Mike Bayer > > a écrit :
>>
>>> Hi -
>>>
>>> I was hoping someone could jump in on this.
>>>
>>> do you know the SQL that you want to emit? E.g. plain SQL string. I
>>> can help you do that part.
>>>
>>>
>>> On Mon, May 13, 2019 at 9:11 AM Scheck David >> > wrote:
>>> >
>>> > I think I'm near but I can't finish :
>>> >
>>> > for all the structure : https://dpaste.de/fek5#L
>>> >
>>> > and here my query :
>>> >
>>> > self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
>>> > .group_by(Object).with_entities(Object,
>>> func.max(Status.datum).label("status_datum")).subquery()
>>> >
>>> > self.session.query(Object).join((sub, sub.c.id == Status.id))\
>>> > .filter(sub.c.statustype_id > 50)
>>> >
>>> > but status type not reachable.
>>> >
>>> >
>>> > --
>>> > 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 sqlal...@googlegroups.com .
>>> > To post to this group, send email to sqlal...@googlegroups.com
>>> .
>>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>>> > To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com
>>> .
>>> > 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 a topic in the
>>> Google Groups "sqlalchemy" group.
>>> To unsubscribe from this topic, visit
>>> https://groups.google.com/d/topic/sqlalchemy/Cju-PF7mV58/unsubscribe.
>>> To unsubscribe from this group and all its topics, send an email to
>>> sqlal...@googlegroups.com .
>>> To post to this group, send email to sqlal...@googlegroups.com
>>> .
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXF3d_pffLqFLYpSNw80E%3D_2ULhh0pTiqBG-F1dFjA9r1Q%40mail.gmail.com
>>> .
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>> --
>>
>> With kindest regards,
>>
>>
>> *David SCHECK*
>>
>> PRESIDENT/DEVELOPER
>>
>> [image: Signature Logo Sphax Bleu-01.png]
>>
>> Phone: +32 4
Re: [sqlalchemy] Re: Query last relation created and attributes
> > Here is my sql for this case > SELECT DISTINCT erfgoed.id as id, statussen.statustype_id as statype_id, statussen.datum as datum, erfgoed.naam as naam FROM erfgoedobjecten as erfgoed JOIN erfgoedobjecten_statussen as erfgoedobjectstatus ON erfgoed.id = erfgoedobjectstatus.erfgoedobject_id JOIN statussen ON statussen.id = ( SELECT id FROM statussen WHERE statussen.id = erfgoedobjectstatus.status_id ORDER BY datum DESC ) WHERE statussen.statustype_id > 50 -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4b07dd75-df93-4896-91ff-95edcdd74453%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Query last relation created and attributes
Ok, I'll try to build this query in sql. thanks :)
Le lun. 13 mai 2019 à 17:43, James Fennell a
écrit :
> I think Mike's suggestion was to construct the raw SQL string you want,
> then reverse engineer to get the correct SQL Alchemy code, which you can
> then use with your different models. For complicated SQL logic I think this
> is a good practice in general.
>
> You current question seems like a general SQL question rather than
> something specific to SQL Alchemy. After you've the SQL, we could discuss
> the reverse engineering.
>
> On Mon, May 13, 2019, 10:37 AM Scheck David
>> the problem is that I can't use SQL for this because this is a mixins
>> that I use for several objects (tables) because they all have this status
>> structure... yes it's quite difficult to do :
>>
>> right now I'm on this stage, but still don't work
>> self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
>> .group_by(Object).with_entities(Object,
>> func.max(Status.datum).label("status_datum")).subquery()
>>
>> self.session.query(Object).join((sub, sub.c.id == Status.id))\
>> .filter(sub.c.statustype_id > 50)
>>
>> Le lun. 13 mai 2019 à 16:26, Mike Bayer a
>> écrit :
>>
>>> Hi -
>>>
>>> I was hoping someone could jump in on this.
>>>
>>> do you know the SQL that you want to emit? E.g. plain SQL string. I
>>> can help you do that part.
>>>
>>>
>>> On Mon, May 13, 2019 at 9:11 AM Scheck David wrote:
>>> >
>>> > I think I'm near but I can't finish :
>>> >
>>> > for all the structure : https://dpaste.de/fek5#L
>>> >
>>> > and here my query :
>>> >
>>> > self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
>>> > .group_by(Object).with_entities(Object,
>>> func.max(Status.datum).label("status_datum")).subquery()
>>> >
>>> > self.session.query(Object).join((sub, sub.c.id == Status.id))\
>>> > .filter(sub.c.statustype_id > 50)
>>> >
>>> > but status type not reachable.
>>> >
>>> >
>>> > --
>>> > 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.
>>> > To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com
>>> .
>>> > 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 a topic in the
>>> Google Groups "sqlalchemy" group.
>>> To unsubscribe from this topic, visit
>>> https://groups.google.com/d/topic/sqlalchemy/Cju-PF7mV58/unsubscribe.
>>> To unsubscribe from this group and all its topics, 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.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXF3d_pffLqFLYpSNw80E%3D_2ULhh0pTiqBG-F1dFjA9r1Q%40mail.gmail.com
>>> .
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>> --
>>
>> With kindest regards,
>>
>>
>> *David SCHECK*
>>
>> PRESIDENT/DEVELOPER
>>
>> [image: Signature Logo Sphax Bleu-01.p
Re: [sqlalchemy] Re: Query last relation created and attributes
the problem is that I can't use SQL for this because this is a mixins that
I use for several objects (tables) because they all have this status
structure... yes it's quite difficult to do :
right now I'm on this stage, but still don't work
self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
.group_by(Object).with_entities(Object,
func.max(Status.datum).label("status_datum")).subquery()
self.session.query(Object).join((sub, sub.c.id == Status.id))\
.filter(sub.c.statustype_id > 50)
Le lun. 13 mai 2019 à 16:26, Mike Bayer a écrit :
> Hi -
>
> I was hoping someone could jump in on this.
>
> do you know the SQL that you want to emit? E.g. plain SQL string. I
> can help you do that part.
>
>
> On Mon, May 13, 2019 at 9:11 AM Scheck David wrote:
> >
> > I think I'm near but I can't finish :
> >
> > for all the structure : https://dpaste.de/fek5#L
> >
> > and here my query :
> >
> > self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
> > .group_by(Object).with_entities(Object,
> func.max(Status.datum).label("status_datum")).subquery()
> >
> > self.session.query(Object).join((sub, sub.c.id == Status.id))\
> > .filter(sub.c.statustype_id > 50)
> >
> > but status type not reachable.
> >
> >
> > --
> > 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.
> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com
> .
> > 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/Cju-PF7mV58/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXF3d_pffLqFLYpSNw80E%3D_2ULhh0pTiqBG-F1dFjA9r1Q%40mail.gmail.com
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
With kindest regards,
*David SCHECK*
PRESIDENT/DEVELOPER
[image: Signature Logo Sphax Bleu-01.png]
Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAOPG6scR1rWJTBN%3DEArX2buPqiVsGaPiQq4wYGU58Renqae0kA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Query last relation created and attributes
I think I'm near but I can't finish :
for all the structure : https://dpaste.de/fek5#L
and here my query :
self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
.group_by(Object).with_entities(Object,
func.max(Status.datum).label("status_datum")).subquery()
self.session.query(Object).join((sub, sub.c.id == Status.id))\
.filter(sub.c.statustype_id > 50)
but status type not reachable.
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Query last relation created and attributes
I did this : 1. self.session.query(Object)\ 2. .join(Object.statussen)\ 3. .filter(Status.id == self.session.query(Status).order_by(desc(Status. datum)).first().id)\ 4. .filter(Statustype.id > 50).all() but I still don"t get it -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/99179cb4-a6b0-409f-a4b1-06fc5abf7fb8%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Query last relation created and attributes
Hi,
I have to perform a query who filter in the last relations created an
attribute, but I don't know how to do. I checked the doc and I don't get it
class Statustype(Base):
__tablename__ = 'statustypes'
id = Column(Integer, nullable=False, primary_key=True)
naam = Column(String(50), nullable=False)
class Status(Base):
__tablename__ = 'statussen'
id = Column(Integer, nullable=False, primary_key=True)
statustype_id = Column(Integer, ForeignKey('statustypes.id'),
nullable=False)
datum = Column(DateTime(timezone=True), nullable=False,
default=func.now())
status = relationship('Statustype')
class Object(Base):
__tablename__ = 'aanduidingsobjecten'
naam = Column(String(255), nullable=False)
type_id = Column(Integer, ForeignKey('aanduidingsobjecttypes.id'),
nullable=False)
statussen = relationship(
"AanduidingsobjectStatus",
order_by='desc(AanduidingsobjectStatus.status_datum)',
backref='aanduidingsobject',
cascade='all, delete, delete-orphan',
lazy='subquery'
)
here is the query I got so far :
session.query(Object)\
.join(Object.statussen)\
.filter(Statustype.id > 50).all()
But I don't see how to perform the check on the last status and all in only
one query. Do you have an idea?
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/99bc3fc7-d2fb-4629-b81e-b1558e578669%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: can't update images
Ok solved it in postgresql 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.
Re: [sqlalchemy] can't update images
it's ok, it's saving but what is strange is that my query doesn't find this
records:
this is my sql :
sql = 'SELECT ss.id, ss.name, ss.image_metadata FROM (SELECT image.id,
image.image_metadata, image.name,
json_array_elements(image.uri_reference)::text as uri_ref FROM image) ss
WHERE ss.uri_ref = :uri ORDER BY ss.id LIMIT 5'
result = self.session.execute(text(sql).params(uri=uri))
return result
Le mer. 27 mars 2019 à 16:04, Scheck David a écrit :
> Hi everyone,
>
> I try to update an attribute but it appears that it doesn't save. Would
> you know why ? it's very strange because the print is good. but when I
> query again this uri_reference it finds Nonetype.
>
> here is the command :
>
> ```
>
> @contextlib.contextmanager
> def db_session(settings):
> engine = engine_from_config(settings, 'sqlalchemy.')
> session_maker = sessionmaker(bind=engine)
> session = session_maker()
> try:
> yield session
> session.commit()
> except Exception:
> session.rollback()
> finally:
> session.close()
>
>
> def process(settings):
> """
> Command to migrate urls to a single JSON field for performance issues
> """
> with db_session(settings) as session:
> print ("")
> print ("SELECTING ALL IMAGES")
> print ("")
> for image in session.query(Image).limit(40).all():
> urls = _list_uri_references_for_data(image.image_metadata)
> urls.append(image.state.actor_uri)
> image.uri_reference = urls
> print ("{}: SAVING -> {}".format(image.name,
> image.uri_reference))
> print ("")
> print ("CLOSING SCRIPT")
> print ("")
> ```
>
> Thanks in advance.
>
> --
> 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/F0WBuRrxa4Q/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>
--
With kindest regards,
*David SCHECK*
PRESIDENT/DEVELOPER
[image: Signature Logo Sphax Bleu-01.png]
Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org
--
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] can't update images
Hi everyone,
I try to update an attribute but it appears that it doesn't save. Would you
know why ? it's very strange because the print is good. but when I query
again this uri_reference it finds Nonetype.
here is the command :
```
@contextlib.contextmanager
def db_session(settings):
engine = engine_from_config(settings, 'sqlalchemy.')
session_maker = sessionmaker(bind=engine)
session = session_maker()
try:
yield session
session.commit()
except Exception:
session.rollback()
finally:
session.close()
def process(settings):
"""
Command to migrate urls to a single JSON field for performance issues
"""
with db_session(settings) as session:
print ("")
print ("SELECTING ALL IMAGES")
print ("")
for image in session.query(Image).limit(40).all():
urls = _list_uri_references_for_data(image.image_metadata)
urls.append(image.state.actor_uri)
image.uri_reference = urls
print ("{}: SAVING -> {}".format(image.name,
image.uri_reference))
print ("")
print ("CLOSING SCRIPT")
print ("")
```
Thanks in advance.
--
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.
Re: [sqlalchemy] RAW SQL working on Postgres but not in SQLAlchemy
Thanks for the answer Mike, I'll use your advice. It was indeed a problem of wrong database config. Thanks for the answer. Le mar. 26 mars 2019 à 19:29, Piyush Nalawade a écrit : > Big thanks. Learned something new. > > On Tue, Mar 26, 2019, 11:24 PM Jonathan Vanasco > wrote: > >> >> >> On Tuesday, March 26, 2019 at 1:46:37 PM UTC-4, Piyush Nalawade wrote: >>> >>> Hi Mike, >>> >>> In the above example does the text and params help to protect from SQL >>> injection attacks? >>> >> >> yes. >> >> see >> https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?#sqlalchemy.sql.expression.text >> on >> how the raw text is handled >> >> in terms of params, >> https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?#sqlalchemy.sql.expression.ClauseElement.params >> >> passing the values in via `params` invokes the database support for bind >> parameters, which is what protects you from sql injection in values passed >> in. >> >> -- >> 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/pDuIdeSzR8Q/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- With kindest regards, *David SCHECK* PRESIDENT/DEVELOPER [image: Signature Logo Sphax Bleu-01.png] Phone: +32 4 87 86 70 12 Visit our website ! https://www.sphax.org -- 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] RAW SQL working on Postgres but not in SQLAlchemy
Hi,
I've a simple query as this :
def count_references(self, uri):
sql = 'SELECT count(*) FROM (SELECT image.id,
json_array_elements(image.uri_reference)::text as uri_ref FROM image) ss
WHERE ss.uri_ref = \'\"{0}\"\''.format(uri)
result = self.session.execute(text(sql))
I tested It on pgadmin and all works very good. and SQLAlchemy is throwing
an error as :
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation
"image" does not exist
LINE 1: ...ements(image.uri_reference)::text as uri_ref FROM image) ss ...
--
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.
Re: [sqlalchemy] Index on nested JSONB field
@mike, a real thank you for your patience. I'm sorry I was lost for a
while, I understood what you said and it's what I'm doing now. Thanks for
the tips and sorry for your time
Le jeu. 21 mars 2019 à 17:17, Scheck David a écrit :
> CREATE INDEX img_createdby_uri_index ON image ((image_metadata ->
> 'systemfields' -> 'created_by' ->> 'uri'));
>
> that's what I want. thank you
>
> Le jeu. 21 mars 2019 à 14:25, Mike Bayer a
> écrit :
>
>> (noting again, you need the CREATE INDEX statement that PG wants.
>> SQLAlchemy part is only a bonus which isn't strictly needed here).
>>
>> On Thu, Mar 21, 2019 at 9:24 AM Mike Bayer
>> wrote:
>>
>>> can you go onto some Postgresql forums and ask there? this is a
>>> Postgresql-specific issue.
>>>
>>> On Thu, Mar 21, 2019 at 8:44 AM Scheck David wrote:
>>>
>>>> In fact it's a function in postgresql which catch all urls and store it
>>>> in a field. but it's not what I'm searching for. I just have to create an
>>>> index gin and I still don't know how to. I'm just stuck because I don't
>>>> understand how to create index on a nested field.
>>>>
>>>> op.create_index('ix_law_search_vector', 'law', ['search_vector'],
>>>> unique=False, postgresql_using='gin')
>>>>
>>>> my model is like
>>>> Image
>>>> metadata = JSONB
>>>>
>>>> JSONB field :
>>>> { 'updated_by': {'uri': 'https://alink', 'description': 'Beeldbank'}},
>>>> 'akey': [{'uri': 'alink'}], 'aanduidingsobjecten': []
>>>> }
>>>>
>>>> And I need to optimise the search through all the keys "uri".
>>>>
>>>> because right now we do something like that :
>>>>
>>>> filter(
>>>> or_(
>>>>
>>>> Image.image_metadata['systemfields']['updated_by']['uri'].astext == uri,
>>>> Image.image_metadata.contains({'akey': [{'uri': uri}]}),
>>>> ImageState.actor_uri == uri
>>>> ))
>>>>
>>>> and it takes 70s on a database with 27 records. and that's just not
>>>> possible.
>>>>
>>>> I've another query where it's 2 sec (I don't understand that) :
>>>> filter(Image.image_metadata.contains({'akey': [{'uri': uri}]}))
>>>>
>>>>
>>>> --
>>>>
>>>> With kindest regards,
>>>>
>>>>
>>>> *David SCHECK*
>>>>
>>>> PRESIDENT/DEVELOPER
>>>>
>>>> [image: Signature Logo Sphax Bleu-01.png]
>>>>
>>>> Phone: +32 4 87 86 70 12
>>>> Visit our website ! https://www.sphax.org
>>>>
>>>> --
>>>> 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.
>>
>
>
> --
>
> With kindest regards,
>
>
> *David SCHECK*
>
> PRESIDENT/DEVELOPER
>
> [image: Signature Logo Sphax Bleu-01.png]
>
> Phone: +32 4 87 86 70 12
> Visit our website ! https://www.sphax.org
>
--
With kindest regards,
*David SCHECK*
PRESIDENT/DEVELOPER
[image: Signature Logo Sphax Bleu-01.png]
Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org
--
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.
Re: [sqlalchemy] Index on nested JSONB field
CREATE INDEX img_createdby_uri_index ON image ((image_metadata ->
'systemfields' -> 'created_by' ->> 'uri'));
that's what I want. thank you
Le jeu. 21 mars 2019 à 14:25, Mike Bayer a
écrit :
> (noting again, you need the CREATE INDEX statement that PG wants.
> SQLAlchemy part is only a bonus which isn't strictly needed here).
>
> On Thu, Mar 21, 2019 at 9:24 AM Mike Bayer
> wrote:
>
>> can you go onto some Postgresql forums and ask there? this is a
>> Postgresql-specific issue.
>>
>> On Thu, Mar 21, 2019 at 8:44 AM Scheck David wrote:
>>
>>> In fact it's a function in postgresql which catch all urls and store it
>>> in a field. but it's not what I'm searching for. I just have to create an
>>> index gin and I still don't know how to. I'm just stuck because I don't
>>> understand how to create index on a nested field.
>>>
>>> op.create_index('ix_law_search_vector', 'law', ['search_vector'],
>>> unique=False, postgresql_using='gin')
>>>
>>> my model is like
>>> Image
>>> metadata = JSONB
>>>
>>> JSONB field :
>>> { 'updated_by': {'uri': 'https://alink', 'description': 'Beeldbank'}},
>>> 'akey': [{'uri': 'alink'}], 'aanduidingsobjecten': []
>>> }
>>>
>>> And I need to optimise the search through all the keys "uri".
>>>
>>> because right now we do something like that :
>>>
>>> filter(
>>> or_(
>>>
>>> Image.image_metadata['systemfields']['updated_by']['uri'].astext == uri,
>>> Image.image_metadata.contains({'akey': [{'uri': uri}]}),
>>> ImageState.actor_uri == uri
>>> ))
>>>
>>> and it takes 70s on a database with 27 records. and that's just not
>>> possible.
>>>
>>> I've another query where it's 2 sec (I don't understand that) :
>>> filter(Image.image_metadata.contains({'akey': [{'uri': uri}]}))
>>>
>>>
>>> --
>>>
>>> With kindest regards,
>>>
>>>
>>> *David SCHECK*
>>>
>>> PRESIDENT/DEVELOPER
>>>
>>> [image: Signature Logo Sphax Bleu-01.png]
>>>
>>> Phone: +32 4 87 86 70 12
>>> Visit our website ! https://www.sphax.org
>>>
>>> --
>>> 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.
>
--
With kindest regards,
*David SCHECK*
PRESIDENT/DEVELOPER
[image: Signature Logo Sphax Bleu-01.png]
Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org
--
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.
Re: [sqlalchemy] Index on nested JSONB field
In fact it's a function in postgresql which catch all urls and store it in
a field. but it's not what I'm searching for. I just have to create an
index gin and I still don't know how to. I'm just stuck because I don't
understand how to create index on a nested field.
op.create_index('ix_law_search_vector', 'law', ['search_vector'],
unique=False, postgresql_using='gin')
my model is like
Image
metadata = JSONB
JSONB field :
{ 'updated_by': {'uri': 'https://alink', 'description': 'Beeldbank'}},
'akey': [{'uri': 'alink'}], 'aanduidingsobjecten': []
}
And I need to optimise the search through all the keys "uri".
because right now we do something like that :
filter(
or_(
Image.image_metadata['systemfields']['updated_by']['uri'].astext == uri,
Image.image_metadata.contains({'akey': [{'uri': uri}]}),
ImageState.actor_uri == uri
))
and it takes 70s on a database with 27 records. and that's just not
possible.
I've another query where it's 2 sec (I don't understand that) :
filter(Image.image_metadata.contains({'akey': [{'uri': uri}]}))
--
With kindest regards,
*David SCHECK*
PRESIDENT/DEVELOPER
[image: Signature Logo Sphax Bleu-01.png]
Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org
--
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.
Re: [sqlalchemy] Index on nested JSONB field
the only problems is when I'll query with SQLAlchemy with a field like :
"uri,uri,uri" is there a simple query to extract this uri ? like a contains
?
Le jeu. 21 mars 2019 à 09:35, Scheck David a écrit :
> I found this which could make the work. because it seems that it index all
> in one field and this will improve my performances. what do you think?
>
> https://stackoverflow.com/questions/40106609/indexing-nested-json-with-postgres
>
> I think this could do the trick
>
> Le mer. 20 mars 2019 à 17:03, Mike Bayer a
> écrit :
>
>> On Wed, Mar 20, 2019 at 11:04 AM david scheck
>> wrote:
>> >
>> > Hi everyone,
>> >
>> > I'm trying to implement a nested JSONB index on a nested field in the
>> json file. I searched through internet and came at the conclusion that I
>> had to create it manually.
>> >
>> > so that's where I am.
>> >
>> > op.create_index('ix_law_search_vector', 'law', ['search_vector'],
>> unique=False, postgresql_using='gin')
>> >
>> >
>> > first of all, I don't understand the 3rd argument. what does this
>> represent? the name of the index?
>>
>> the first argument is the name of the index.All the remaining
>> positional arguments are SQL expressions. So the ['search_vector'] is
>> probably wrong.
>>
>> >
>> > this is what should be. but my problem is that my desire index is very
>> deep in the JSONB.
>>
>> >
>> >
>> > that's what I've to target :
>> Image.image_metadata['afield']['anotherfield']['fieldindex']
>> >
>> >
>> > I want to create an index on fieldindex.
>> >
>> >
>> > but if I do :
>> >
>> > op.create_index('uri', ['search_vector'], unique=False,
>> postgresql_using='gin')
>> >
>> >
>> > as there is a lot of field 'uri' in the json, will it index all this
>> fields in 1 ?
>> >
>> >
>> > (I'm new to sqlalchemy, I usually use the django orm, so sorry in
>> advance).
>>
>> Right so in SQLAlchemy, you don't have to figure any of this out.
>> Just get the Postgresql syntax you want exactly and run it,
>> op.execute("CREATE INDEX whatever").if you show me the syntax I
>> can see how to make Index() do it too but to just emit a very specific
>> CREATE INDEX, you can just type it out.
>>
>>
>>
>> >
>> >
>> > Thanks for answers
>> >
>> >
>> > --
>> > 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.
>>
>
>
> --
>
> With kindest regards,
>
>
> *David SCHECK*
>
> PRESIDENT/DEVELOPER
>
> [image: Signature Logo Sphax Bleu-01.png]
>
> Phone: +32 4 87 86 70 12
> Visit our website ! https://www.sphax.org
>
--
With kindest regards,
*David SCHECK*
PRESIDENT/DEVELOPER
[image: Signature Logo Sphax Bleu-01.png]
Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org
--
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.
Re: [sqlalchemy] Index on nested JSONB field
I found this which could make the work. because it seems that it index all
in one field and this will improve my performances. what do you think?
https://stackoverflow.com/questions/40106609/indexing-nested-json-with-postgres
I think this could do the trick
Le mer. 20 mars 2019 à 17:03, Mike Bayer a
écrit :
> On Wed, Mar 20, 2019 at 11:04 AM david scheck
> wrote:
> >
> > Hi everyone,
> >
> > I'm trying to implement a nested JSONB index on a nested field in the
> json file. I searched through internet and came at the conclusion that I
> had to create it manually.
> >
> > so that's where I am.
> >
> > op.create_index('ix_law_search_vector', 'law', ['search_vector'],
> unique=False, postgresql_using='gin')
> >
> >
> > first of all, I don't understand the 3rd argument. what does this
> represent? the name of the index?
>
> the first argument is the name of the index.All the remaining
> positional arguments are SQL expressions. So the ['search_vector'] is
> probably wrong.
>
> >
> > this is what should be. but my problem is that my desire index is very
> deep in the JSONB.
>
> >
> >
> > that's what I've to target :
> Image.image_metadata['afield']['anotherfield']['fieldindex']
> >
> >
> > I want to create an index on fieldindex.
> >
> >
> > but if I do :
> >
> > op.create_index('uri', ['search_vector'], unique=False,
> postgresql_using='gin')
> >
> >
> > as there is a lot of field 'uri' in the json, will it index all this
> fields in 1 ?
> >
> >
> > (I'm new to sqlalchemy, I usually use the django orm, so sorry in
> advance).
>
> Right so in SQLAlchemy, you don't have to figure any of this out.
> Just get the Postgresql syntax you want exactly and run it,
> op.execute("CREATE INDEX whatever").if you show me the syntax I
> can see how to make Index() do it too but to just emit a very specific
> CREATE INDEX, you can just type it out.
>
>
>
> >
> >
> > Thanks for answers
> >
> >
> > --
> > 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.
>
--
With kindest regards,
*David SCHECK*
PRESIDENT/DEVELOPER
[image: Signature Logo Sphax Bleu-01.png]
Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org
--
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.
