Re: [sqlalchemy] Documentation options
On Thu, Mar 21, 2019 at 7:01 PM Rich Shepard wrote: > > On Thu, 21 Mar 2019, Mike Bayer wrote: > > > sounds like automap: > > https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html > > Mike, > > While this does not look familiar, I'll carefully read the page again until > I really understand it. > > Now, model.py contains classes for each table in the postgres database, and > I thought there was a way to associate the classes with the tables yet > automap doesn't tickle my memory. So, more study is on the schedule. there's also just using `__table__ = Table(..., autoload_with=engine)` as well as DeferredReflection which is kind of a more primitive automap, maybe you saw that: https://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/table_config.html#using-reflection-with-declarative > > Thanks again, > > Rich > > > -- > 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.
Re: [sqlalchemy] Documentation options
On Thu, 21 Mar 2019, Mike Bayer wrote: sounds like automap: https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html Mike, While this does not look familiar, I'll carefully read the page again until I really understand it. Now, model.py contains classes for each table in the postgres database, and I thought there was a way to associate the classes with the tables yet automap doesn't tickle my memory. So, more study is on the schedule. Thanks again, Rich -- 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] Documentation options
sounds like automap: https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html On Thu, Mar 21, 2019 at 6:07 PM Rich Shepard wrote: > > On Thu, 21 Mar 2019, Mike Bayer wrote: > > > oh sure, I meant it uses up an enormous amount of memory / CPU to build > > the PDF. it looks terrible too because sphinx's templates aren't very > > good, plus SQLAlchemy's docs have a bunch of custom things going on that > > format even more badly. > > Mike, > > Okay. You convinced me to stick with the on-line versions. > > I have the database tables created and populated, and the tkinter GUI views > (at least, most of them) written, and now I need to learn how to apply SA > between the two. > > Long ago I saw the docs that explained how to work with an existing database > rather than using the classes to create the tables. I'm trying to find that > again in my e-mail archives. > > Regards, > > Rich > > -- > 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.
Re: [sqlalchemy] Documentation options
On Thu, 21 Mar 2019, Mike Bayer wrote: oh sure, I meant it uses up an enormous amount of memory / CPU to build the PDF. it looks terrible too because sphinx's templates aren't very good, plus SQLAlchemy's docs have a bunch of custom things going on that format even more badly. Mike, Okay. You convinced me to stick with the on-line versions. I have the database tables created and populated, and the tkinter GUI views (at least, most of them) written, and now I need to learn how to apply SA between the two. Long ago I saw the docs that explained how to work with an existing database rather than using the classes to create the tables. I'm trying to find that again in my e-mail archives. Regards, Rich -- 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] Documentation options
On Thu, Mar 21, 2019 at 5:11 PM Rich Shepard wrote: > > On Thu, 21 Mar 2019, Mike Bayer wrote: > > > not currently, however you can do a sphinx pdf build yourself if you > > feel like installing LaTeX, > > Mike, > > I write > 90% of my documents using LaTeX (with the LyX GUI front end). > Sphinx is a new one for me so I'll go look at it. > > > unfortunately SQLAlchemy's PDF is enormous, has a lot of formatting > > problems, and the readthedocs site wasn't able to build it, the > > current tools are not reliable enough for me to publish this myself. > > Postgres docs are also rather large and I find it easier to search for > specific information in a PDF. oh sure, I meant it uses up an enormous amount of memory / CPU to build the PDF. it looks terrible too because sphinx's templates aren't very good, plus SQLAlchemy's docs have a bunch of custom things going on that format even more badly. > > Thanks, > > Rich > > -- > 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.
Re: [sqlalchemy] Documentation options
On Thu, 21 Mar 2019, Mike Bayer wrote: not currently, however you can do a sphinx pdf build yourself if you feel like installing LaTeX, Mike, I write > 90% of my documents using LaTeX (with the LyX GUI front end). Sphinx is a new one for me so I'll go look at it. unfortunately SQLAlchemy's PDF is enormous, has a lot of formatting problems, and the readthedocs site wasn't able to build it, the current tools are not reliable enough for me to publish this myself. Postgres docs are also rather large and I find it easier to search for specific information in a PDF. Thanks, Rich -- 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] Documentation options
On Thu, Mar 21, 2019 at 4:20 PM Rich Shepard wrote: > > Are there PDF versions of the docs available for downloading and reading? I > don't find an answer on the web site. not currently, however you can do a sphinx pdf build yourself if you feel like installing LaTeX, there also seems to be some new project https://github.com/brechtm/rinohtype that wants to replace LaTeX, could be helpful. unfortunately SQLAlchemy's PDF is enormous, has a lot of formatting problems, and the readthedocs site wasn't able to build it, the current tools are not reliable enough for me to publish this myself. > > TIA, > > Rich > > -- > 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.
[sqlalchemy] Documentation options
Are there PDF versions of the docs available for downloading and reading? I don't find an answer on the web site. TIA, Rich -- 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] binding parameters in quotes
On Thu, Mar 21, 2019 at 3:33 PM Victor Olex
wrote:
>
> Thanks Mike, though the question is valid - why does regex in SQLAlchemy
> allow for discovering parameter token inside quotes? Have you seen a
> legitimate case for that?
the regex in SQLAlchemy is not a SQL parser, it's just putting bound
parameters where the user typed them. It does not detect invalid
SQL, that's the database's job.
>
> On Wednesday, March 20, 2019 at 9:58:58 AM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Mar 20, 2019 at 7:59 AM mdob wrote:
>> >
>> > Hi,
>> >
>> >
>> > Is it correct behavior that parameter placeholders in quotes e.g. SELECT *
>> > FROM Artist WHERE Name LIKE "%:pattern%" are recognized as valid
>> > parameters?
>> >
>> >
>> > from sqlalchemy.sql import text
>> > from sqlalchemy.dialects import sqlite
>> > from sqlalchemy import create_engine
>> > engine = create_engine('sqlite:home/mike/Chinook.sqlite', echo=True)
>> >
>> >
>> > s = 'SELECT * FROM Artist WHERE Name LIKE "%:pattern%"'
>> > q = text(s)
>> >
>> > c = q.compile()
>> > print c.params
>> >
>> > {'pattern': None}
>> >
>> >
>> > If parameter is provided
>> > engine.execute(q, {'pattern': 'foo'})
>> >
>> > engine echo:
>> >
>> > 2019-03-20 12:44:14,668 INFO sqlalchemy.engine.base.Engine SELECT * FROM
>> > Artist WHERE Name LIKE '%?'
>> > 2019-03-20 12:44:14,669 INFO sqlalchemy.engine.base.Engine ('foo',)
>> >
>> > and error is raised
>> >
>> >
>> > ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of bindings
>> > supplied. The current statement uses 0, and there are 1 supplied. [SQL:
>> > u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] [parameters: ('foo',)]
>> > (Background on this error at: http://sqlalche.me/e/f405)
>> >
>> >
>> > When executed without parameters
>> >
>> > engine.execute(q, {'pattern': 'foo'})
>> >
>> > different error is raised
>> >
>> >
>> > StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required
>> > for bind parameter 'pattern' [SQL: u'SELECT * FROM Artist WHERE Name LIKE
>> > "%?%"'] (Background on this error at: http://sqlalche.me/e/cd3x)
>> >
>> > It feels to me like finding parameters in
>> > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L895
>> > should exclude placeholders inside quotes.
>>
>> This is not how bound parameters work in relational databases. Bound
>> parameters are not Python substitution strings, they are more often
>> than not processed by the server, or at least have to behave as though
>> they were. This means you cannot assume a parameter is substituted
>> inside of a value as you are doing here. The correct syntax for what
>> you are trying to do is:
>>
>> s = 'SELECT * FROM Artist WHERE Name LIKE '%' || :pattern || '%'
>>
>> that is, the concatenation you are doing is explicit in SQL.
>>
>>
>> >
>> > Michal
>> >
>> > --
>> > 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 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+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.
Re: [sqlalchemy] binding parameters in quotes
Thanks Mike, though the question is valid - why does regex in SQLAlchemy
allow for discovering parameter token inside quotes? Have you seen a
legitimate case for that?
On Wednesday, March 20, 2019 at 9:58:58 AM UTC-4, Mike Bayer wrote:
>
> On Wed, Mar 20, 2019 at 7:59 AM mdob >
> wrote:
> >
> > Hi,
> >
> >
> > Is it correct behavior that parameter placeholders in quotes e.g. SELECT
> * FROM Artist WHERE Name LIKE "%:pattern%" are recognized as valid
> parameters?
> >
> >
> > from sqlalchemy.sql import text
> > from sqlalchemy.dialects import sqlite
> > from sqlalchemy import create_engine
> > engine = create_engine('sqlite:home/mike/Chinook.sqlite', echo=True)
> >
> >
> > s = 'SELECT * FROM Artist WHERE Name LIKE "%:pattern%"'
> > q = text(s)
> >
> > c = q.compile()
> > print c.params
> >
> > {'pattern': None}
> >
> >
> > If parameter is provided
> > engine.execute(q, {'pattern': 'foo'})
> >
> > engine echo:
> >
> > 2019-03-20 12:44:14,668 INFO sqlalchemy.engine.base.Engine SELECT * FROM
> Artist WHERE Name LIKE '%?'
> > 2019-03-20 12:44:14,669 INFO sqlalchemy.engine.base.Engine ('foo',)
> >
> > and error is raised
> >
> >
> > ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of
> bindings supplied. The current statement uses 0, and there are 1 supplied.
> [SQL: u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] [parameters: ('foo',)]
> (Background on this error at: http://sqlalche.me/e/f405)
> >
> >
> > When executed without parameters
> >
> > engine.execute(q, {'pattern': 'foo'})
> >
> > different error is raised
> >
> >
> > StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required
> for bind parameter 'pattern' [SQL: u'SELECT * FROM Artist WHERE Name LIKE
> "%?%"'] (Background on this error at: http://sqlalche.me/e/cd3x)
> >
> > It feels to me like finding parameters in
> https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L895
>
> should exclude placeholders inside quotes.
>
> This is not how bound parameters work in relational databases. Bound
> parameters are not Python substitution strings, they are more often
> than not processed by the server, or at least have to behave as though
> they were. This means you cannot assume a parameter is substituted
> inside of a value as you are doing here. The correct syntax for what
> you are trying to do is:
>
> s = 'SELECT * FROM Artist WHERE Name LIKE '%' || :pattern || '%'
>
> that is, the concatenation you are doing is explicit in SQL.
>
>
> >
> > Michal
> >
> > --
> > 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 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+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
just to confirm, run this inside of any execute() callable, eg. in alembic:
op.execute("CREATE INDEX img_createdby_uri_index ON image ((image_metadata
-> 'systemfields' -> 'created_by' ->> 'uri'))")
with an engine
with engine.connect() as conn:
conn.execute("CREATE INDEX img_createdby_uri_index ON image
((image_metadata -> 'systemfields' -> 'created_by' ->> 'uri'))")
On Thu, Mar 21, 2019 at 12:17 PM Scheck David wrote:
> 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.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
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
(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.
Re: [sqlalchemy] Index on nested JSONB field
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.
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] rendering "NOT EXISTS ()" in PostgreSQL ?
On Wed, Mar 20, 2019 at 9:48 PM Jonathan Vanasco wrote: > > thanks, mike. I may just make a 'not exists' op. > > there are a handful of places in the docs that show `NOT EXISTS` with no > parenthesis. Do you think this is because of a change in the codebase or a > difference in the backends? > > If it's because of legacy code, I can generate a PR to update the docs and > use the current syntax. sure I probably typed that stuff by hand years ago, or EXISTS wasn't an operator, who knows. everytime I look at the docs for anything, it's all not been updated since 2011 and is totally wrong. > > -- > 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.
Re: [sqlalchemy] Index on nested JSONB field
I hardly understand what that does but if it floats your boat, get on board
( I guess?)
On Thu, Mar 21, 2019 at 4:35 AM Scheck David wrote:
> 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.
>
--
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
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.
